Replace Blank Cells with Zeros in Excel Pivot Tables

Subject Line

Make your Pivot look better without blanks

On a lighter note, you may not be able to grow hair on your head but you can surely grow hair on your Pivot (Zeros) to cover the blanks (baldness)

Overview

Sometimes you will have to prepare summary of the raw data which can have missing or blank values. Once you generate the Pivot table the values will be shown as blanks in the Pivot table also. To make the Pivot table presentable and uniform especially in case of numeric values you can replace the blank values with zeros. Also once you generate the Pivot Table you can identify the number of blanks in the data and you may want to replace the blank value with some value of your own. So your Pivot looks better

Working

Let’s understand with an example of Sales table.

We have a report (Figure1: Sales by Sales Person for the year 2017 and 2018)

As you can see some of the values are blanks in Column G which is the Sales amount.

You have to create a summary of quarterly Sales made by Sales Peron for the year 2017 and 2018.


Figure1: Sales by Sales Person for the year 2017 and 2018

 

Step 1: Let’s begin with by creating Pivot table.

Select the table range.

Step 2: Click on INSERT Tab

Step 3: Click on PivotTable Option in the Tables group


Step 4: Create PivotTable dialog box will appear

Step 5: Click on OK


Step 6: PivotTable Fields will be appear in the new sheet


 

Step 7: Drag the Sales Amount field in VALUES area.

Step 8: Drag the Product to COLUMNS area.

Step 9: Drag the Sales Person & Quarter to ROWS area


Step 10: You can see the below summary after dragging the field to their respective area.

Step 11: You can see that there is blank in some fields in the below figure.

Step 12: Click on any cell inside the Pivot Table.

Step 13: Click on PivotTable Options…


Step 14: PivotTable Options dialog box will appear


Step 15: Type 0 in the “For empty cells show” in the Format section in the Layout & Format tab.


Step 16: You can see the value of 0 in place of the missing fields in the PivotTable.


Step 17: Click on any cell inside the Pivot Table

Step 18: Click on Number Format

Step 19: Select $ English (United States) from the Symbol as shown in the below figure


Step 20: Final result


Scope of usage

ü  Can be used to provide your own value to fill missing values in the Pivot Table

ü  Can be used to prepare uniform and meaningful summaries

Can be used to replace blank cells in the Pivot Table

Excel Tutorials