Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education


Display items without data and show zero 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 tutorials. URLs are located at the end of this tutorial.

How to in Pivot Table display text or a row for which there is no value in the adjacent cell

In this 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 D and E in Pivot Table.

The table 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
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 value of zero in Pivot Table, which we want to visually display. Let's go to the next topic tutorials and that is:

How to in Pivot Table display value zero if there is no value in cell

To in Pivot Table view the value zero (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.

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

Excel pivot table without blank cells