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 next step is to create a Pivot Table. Ultimately, the result is as shown below.
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 ..."
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"
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'.
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.
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.
Tutorials on this blog associated to Pivot Table.
- How to create Pivot Table in Excel
- How to create Pivot Table from multiple sheets in Excel
- How to count distinct unique data by Pivot Table