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

April 30, 2020 - 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

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.