The default setting in Excel Pivot Tables is to show blank cells when there is no applicable data for a row or column label. You will find below the steps to Replace Blank Cells with Zeros in Excel Pivot Table.
Replace Blank Cells with Zeros in Excel Pivot Table
As mentioned above, an Excel Pivot Table can end up having blank cells if there is no data applicable for a particular row or column label.
However, having blank cells in Excel Pivot Table can often lead to the possibility of those blank values being attributed to Missing Data, Calculations Error or mistakes in creating Pivot Table.
Hence, it is a good practice to clearly show ‘0’ (zero), whenever there is no applicable value or data in any cell of an Excel Pivot Table.
So, let us go ahead and take a look at the steps to Replace Blank Cells with Zeroes in Excel Pivot Tables.
Replace Blank Cells with Zeros in Excel Pivot Table
In the example below, we have Sales Data for Windows Laptops, MacBooks and ChromeBooks coming from two different branches of a store.
If above data is used to create a pivot table with ‘Computers’ in Row Area and ‘Store#1’ in Column Area, we will end up with a Pivot Table having blanks Cells.
As mentioned above, blank cells in above Pivot Table can be seen as missing data, data entry error or calculation mistake.
Hence, you can follow the steps below to replace blank cells with 0 in Excel Pivot Table.
1. Right-click on any Cell within the Pivot Table and select PivotTable Options in the contextual menu.
2. In PivotTable Options Box, select For Empty cells show option and type 0 in the box next to this field.
3. Click on OK to save this setting.
4. Once you click on OK, you will immediately see that all the blank cells in the Pivot Table have been replaced with 0.
Note: You can also replace blank cells with any text field (such as “NA” or “No Sales”) by typing NA or No Sales in ‘For Empty Cells Show’ field on Pivot Table Options screen.
