Like summarization formulas in Excel, the Pivot Tables also throw errors like #DIV/0! and #NULL. It’s very annoying when this happens.
You can refer to this article to know how to handle errors which result from a formula. In this article, we’ll learn 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.
Example: Handling Errors in Pivot Table
Now, let’s take an example for some yearly sales data and represent that in a Pivot Table. We’ll see how we can handle the errors which might occur when we create the Pivot Table.
- 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).
- In such cases, Right Click on the Pivot Table and select PivotTable Options (as highlighted in the below screenshot)
- 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)
- 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
- 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. Use this link below to download the excel file.
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - 2019-09-27
- FOR EACH Loop in Excel VBA - 2018-12-15