How to remove blanks from pivot table

Microsoft Excel offers a very interesting way to remove blanks from pivot tables. If there are some blanks appearing in the pivot table, we can impute them with something else through a powerful option in the pivot table options. We can perform the below mentioned way to remove blanks from pivot table:

We’ll learn about this methodology step by step.

Removing blanks from pivot table:

Step-1: Pivot table with some data and some blanks

To do this yourself, please follow the steps described below;

– Open the desired Excel workbook with a pivot table having some blank values. 
– Now right click on any cell in the pivot table. A drop down option would appear. Select the “PivotTable Options” from the drop down menu as shown in the image below.

Step-2: Selecting PivotTable Options from drop down menu

– Now a dialogue box would appear. Under the format option, we can see that we have an option “For empty cells show:”. We can use this option to impute the blanks in our pivot table.

Step-3: PivotTable options dialogue box

– Now enter a value in the box against the “For empty cells show:” option, which would be displayed in place of the blanks. In the image below we have typed “N/A”. Now click on “OK”.

Step-4: Filling the value which would impute the blanks

– Now we can see that in our pivot table all the blanks have been imputed with “N/A”.

Final Image: Blanks imputed with “N/A”

– Blanks imputed with “N/A”