Blue Flower

Display Items Without Data and Return Zero Value Instead Empty Cells in Pivot Table

About work with Pivot Table I have already written on this blog. If you are interested, you can see Excel tutorials. URLs are placed at the end of this Excel example tutorial.

How to display a Row in Pivot Table there is No Value in the Adjacent Cell

In this Excel tutorial, I'll show how to display in PivotTable, items that do not contain values of the same Row. In the picture below you see the range of data in columns 'A:B'. Our goal is to create a Pivot Table, but so that Excel displays all the data in column 'B' (Status). So we want to count how many times appears the status data from the 'B' column, which contains data in column 'A'. You note that not all items in column 'B' corresponding data in column 'A'.

In this particular case in the picture below D and E do not contain the corresponding data in column 'A' and we want to display Items D and E in Pivot Table.

 The range contains data that have no value in the adjacent cell

 The next step is to create a Pivot Table. Ultimately, the result is as shown below.

 Created pivot table in Excel

You notice in the picture above it in Excel Pivot Table are not shown the letters D and E of the 'B' column. We want in Pivot Table display these two items in column 'B'.

The next step is to start a dialogue window "PivotTable Options". So right mouse click the Pivot Table, and the pop-up menu, start the "PivotTable Options ..."

 PivotTable Options Window dialog box

 It opens a dialogue window "PivotTable Options" that looks like the image below. Click on "Display" tab and turn on "Show items with no data on rows"

 Displaying the items in pivot table without data in Excel row

 Now, our Pivot Table looks like the image below. You notice in the picture below that now in Pivot Table we have displayed the items D and E in column 'B'.

 The items displayed in Pivot Table that have no value in the same row

In the picture above you see that we are missing the ZERO Value in Pivot Table, which we want to visually display. Let's go to the next part of this Excel tutorial and that is:

How to Display Zero Value in Pivot Table if there is No Value in the Cell

To in Pivot Table view the Zero Value (0) if the cell is empty for a particular item you need to click on the tab "Layout & Format" and turn on "For empty cells show" as shown below.

 Display Zero Value in Pivot Table

 And finally our Pivot Table with the items displayed without data and display Zero Value instead of empty cells look like in the picture below.

 Excel Pivot Table displayed Zero Value insteda empty cell

Tutorials on this blog associated to Pivot Table.