Replace Blank Cells with Zeros in Excel Pivot Table

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.

Table of Contents

Background and Sample File
Replace Blank Cells with Zeros in Excel Pivot Table

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.

Blank Cell Instead of Zero Value - Pivot Table

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.

Also Read: Excel Pivot Table Grand Total and Subtotal

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.