Fix Error Values in Pivot Table
When you get Error Value #DIV/0! in an Excel Pivot Table, it usually means that you are trying to divide a number by zero. Such Error Vales do not look good in a Pivot Table and they can raise unnecessary questions whenever you are trying to present important information to others. Luckily, it is possible to fix such error values in an Excel Pivot Table by making Excel replace such Error Values by a Custom defined value or Text.
1. Fix #DIV/0! Error in Pivot Table
From our experience, #DIV/0! errors in Pivot Table occur due to an incorrect formula or calculation errors in the Source Data File being used by the Pivot Table. Hence, it is really important to make sure that there is no incorrect formula or calculation error in the Source Data File.
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 it is still there). Right-click on the Pivot Table and click on PivotTable Options in the drop-down menu.
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.
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. Right-click on your Pivot Table and click on PivotTable Options in the drop-down menu.
On PivotTable options screen, check the little box next to For empty cells show: and enter “O” or “NA” in the box.
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 your Source Data File. 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.
How to Hide and Unhide Values in Pivot Table How to Change Pivot Table Data Source and Range
