Home ยป Functions

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.

Sample Data with Blank Cells 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.

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.

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:
Right click Pivot table options
  • 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.
Pivot Table Options Dialog Box

That’s it. Resultantly, you would notice that excel inserts 0 in the blank cells in the pivot table. See the image below.

Pivot Table Result - 0 instead of blank cells
Pivot Table - Replace Blank Cells with Zeros

Similarly, you can show ‘NA’ or ‘N/A’ or ‘Not Applicable’ in the Blank cells in the pivot table.

NA instead of Zeros in 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.

Leave a Comment