When you make use of calculations in an Excel Pivot Table, your Pivot Table may come out with some Empty cells and cells with #DIV/0! error values.
Such Error Vales do not look good in a Pivot Table and often raise questions, both when you submit the pivot table as a part of your report and also while you are trying to make a live or online presentation.
Fix Error Values in Pivot Table
If the reason for #DIV/0! error values in a Pivot Table is due to a number being divided by zero, this can be easily fixed by making Microsoft Excel replace such Error Values by a Custom defined value or Text.
However, #DIV/0! and other types of error values in a Pivot Table can also be caused due to calculation errors and Incorrect formulas in the Data Source File being used by the Pivot Table.
Hence, it is really important that you thoroughly check and make sure that there are no incorrect formulas or calculation errors in the Source Data File being used by the Pivot Table.
If you are completely satisfied with the formulas and calculations in Source Data File, you can follow the steps below to fix #DIV/0! error in Pivot Table (if they are still there).
1. Fix #DIV/0! Error in Pivot Table
1. Right-click on the Pivot Table and click on PivotTable Options in the drop-down menu.
2. On PivotTable options screen, check the little box next to For error value show: and enter NA (Not Applicable) or any other text that you want to show up in the Pivot Table in place of the Error Value.
3. Click on the OK button to save this setting in the workbook.
Now, whenever the Pivot Table comes across #DIV/0! error in the source data file, you will see NA (Not Applicable) in the Pivot Table, instead of seeing an Error Value.
Important: A drawback of this solution is that it can mask future errors in source data file. Hence, always make sure that formulas and calculations in source data file are correct and free from all errors.
2. Fix Empty Cells in Pivot Table
Whenever the Source Data File for a Pivot Table contains blanks (which usually happens), you may see empty or no values in certain cells of your Pivot Table.
Just like other errors, empty values in a Pivot Table do not look good and they can also lead to waste of time due to questions about them during your presentation.
1. Right-click on your Pivot Table and click on PivotTable Options in the drop-down menu.
2. On PivotTable options screen, check the little box next to For empty cells show: and enter “O” or “NA” in the box.
3. Click on OK to save this setting.
Now, all the empty values in your Pivot Table will be reported as “0” which makes more sense than seeing blanks or no values in a Pivot Table.
3. Fix “Blank” Value in Pivot Table
Instead of seeing empty cells, you may see the words “blank” being reported in a Pivot Table. As mentioned above, this error is occurring due to presence of empty cells in the Source or Data File being used by the Pivot Table.
You can either fix this error by using Custom Values (methods as discussed above) or hide “blank” in Pivot Table by following the steps below.
1. Identify the location of “blank” values in your Pivot Table. In our case, the word “blank” is appearing in Row 8 and also in Column C of the Pivot Table.
2. To hide “blank” values in Pivot Table, click on the Down-arrow located next to “Row Labels”. In the drop-down, uncheck the little box located next to blank and click on the OK button.
This will hide all the “blank” values in your Pivot Table.