In this tutorial of the Free ExcelUnlocked Pivot Table Course, we would unlock the technique to replace the blank cells with zeros (0) in an excel pivot table.
Background and Sample File
In the below image you can see that there are few blank cells in the source data.
Now when you create a pivot table in excel using this data, you would see that the excel would keep the corresponding values as blank and not zero. This is the default excel setting where excel does not convert the blank excel data into zero (0) in a pivot table. The image below is self-explanatory.
The blank cells in the pivot table mean that there are no records for the same in the source table. For example, here – Salesman John does not have any sales in North Zone. Similarly, Mark did not do sales in any of the four zones. Likewise, is the case with Rock for East and West.
Fortunately, you can change this setting to show 0 instead of blank cells in a pivot table. Also, the pivot table report shows more professional when there are values or numbers in it instead of blank.
Replace Blank Cells with Zeros in Excel Pivot Table
You can quickly and instantly show 0 instead of blank cells in pivot table using the below steps:
- Right-click anywhere in the pivot table and select the option – ‘Pivot table Options‘ as shown in the image below:
- The ‘Pivot Table Options‘ dialog box would appear on your screen. Over here, in the ‘Layout & Format‘ tab, under the ‘Format’ section, type 0 in the input box – ‘For empty cells show‘, and click OK. See the image below for more clarity.
That’s it. Resultantly, you would notice that excel inserts 0 in the blank cells in the pivot table. See the image below.
Similarly, you can show ‘NA’ or ‘N/A’ or ‘Not Applicable’ in the Blank cells in the pivot table.
Now, this setting is permanent. Whenever you create any pivot table, it would show zero (0) for blank records in source table.
Check out other Excel Pivot table related blogs on Free Pivot Table Course by Excelunlocked.