How to get rid of Errors in Pivot Table
When you create pivot tables in Excel, there may be some error values in your pivot table. But now, you want to hide these errors or replace them with some readable information. Like summarization formulas in Excel, the Pivot Tables also pop up errors like #VALUE, #DIV/0! , #NULL.. Etc. Yes, It’s very infuriating when this happens during work.
Lets see how to handle such errors when they occur in Pivot Tables.
Let’s take an example and understand how these errors might occur and how to handle them when they occur. Mainly there are two types of errors that occur in Pivot Tables:
- #DIV/0! Errors
- #NULL! Errors
The first type of error happens when you have division by zero happening. While the second type happens when you have blank spaces in the data.
Now, let’s take an example for some yearly sales data and represent that in a Pivot Table and will see how we can handle the errors which might occur when we create some report.
- Consider yearly sales data as shown in the snapshot below
- Let’s say we create a Pivot table representing the same yearly sales info.
- Along with that, we can add Year over Year sales increase percentage.
- The new calculated column i.e. % increase in sales column might throw an error if the data is 0 for any given year(Refer the highlighted section below).
5. In such cases, Right Click on the Pivot Table and select PivotTable Options (as highlighted in the below screenshot)
6. A pop-up window for the Pivot Table Options appears. In the Format section under the Layout & Format tab, you can modify how errors are handled (refer to the highlighted section in the screenshot below)
7. You can check the For error values show and enter the value to show instead of errors. If you don’t enter anything, blank is taken instead.
8. You’ll notice in the below screenshot that the error is replaced with a blank in the highlighted section
This is how you can handle errors that occur in Pivot Tables :)
Note: This setting only affects cells in the Values area of the pivot table. If error values appear in the Row Labels, Column Labels, or Report Filter area, they won’t be replaced.