MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Fill in the Blanks in the Annoying Outline View


April 29, 2019 - by Bill Jelen

Excel Fill in the Blanks in the Annoying Outline View. Photo Credit: Devin Avery at Unsplash.com

If your pivot table is in Tabular or Outline Form and you have more than one row field, the pivot table defaults to leaving a lot of blank cells in the outer row fields:

The outer row field is Region. Midwest appears once in column A followed by rows for Chicago, Cincinnati, Cleveland. Midwest does not appear next to Cincinnati or Cleveland.

Starting in Excel 2010, use Design, Report Layout, Repeat all Item Labels to fill in the blanks in column A:

After applying Repeat All Item Labels, all of the cells in the Region column are filled in.

There is another way to have blanks in the Values area of a pivot table.


Say that you have a product which is only sold in a few regions. If there are no Doodad sales in Atlanta, Excel will leave that cell empty instead of putting a zero there. Right-click the pivot table and choose Pivot Table Options. On the Layout & Format tab, find the box For Empty Cells, Show: and type a zero.

In the PivotTable Options dialog, choose the Layout & Format tab. There is a setting called For Empty Cells, Show:. Type a zero in that box.

Title Photo: Devin Avery at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.