If you build an Excel pivot table, and no data is entered for some items, there will be blank cells in the pivot table values area. See how to change those blanks to zero, or a text string, such as “N/A”
Blank Value Cells
In the pivot table screenshot below, there are two empty cells in the Values area, which I’ve circled in blue.
In the pivot table source data, there are no prices for Banana Bars at two of the stores, 3062 and 3659.
As a result, there aren’t any amounts to summarize in the pivot table, for those items, and the cells are left empty.
Video: Zero in Empty Cells
In this short video, you’ll see the steps for changing those empty cells, so they don’t look empty.
First, I showed a zero in each empty cell. Next, in the video, I changed that setting so an “N/A” shows in each empty cell.
Video Timeline
- 0:00 Introduction
- 0:27 Show Zero in Empty Value Cells
- 0:53 Show N/A in Empty Value Cells
Values Area Only
It’s important to note that when you change the Pivot Table option setting for empty cells, this only affects cells in the Values area of the Excel pivot table.
If empty cells appear in the pivot table’s Row Labels, Column Labels, or Report Filter area, those empty cells WILL NOT be replaced.
Keep It Simple
If you change the pivot table option setting for empty cells, try to make it clear that there is no data for those cells. That’s why I usually use a zero or the text string “N/A”
Don’t get too fancy, and enter something that will confuse people, instead of helping them!
And remember, if you use text, it will align to the left side of the cell by default, instead of right-aligning like the numbers.
Get the Sample File
To get a sample file for testing empty cells, and to see the written steps, go to the Excel Pivot Table Options page on my Contextures site.
If you’d like to get the food sales data that I used in the video, go to the Food Sales Data page, and download the food sales data file.
Note: Both of the zipped file are in xlsx format, and they do not contain any macros.
____________________
Show Zero in Empty Pivot Table Value Cells
____________________