Excel Pivot Tables Error Handling

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:

1. #DIV/0! Errors
2. #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.

1. Consider yearly sales data as shown in the snapshot below
2. Let’s say we create a Pivot table representing the same yearly sales info.
3. Along with that, we can add Year over Year sales increase percentage.
4. 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. Use this link below to download the excel file.

Download the Excel file here

Please share

Did you find this useful?  Why not share the love and tell your friends on the different social media platforms.

• About
• Latest Posts

Shreyas M

I'm a Senior Business Analyst by profession. Working with Excel is my passion. I've been working on Excel since 2011, it has been a great learning experience for me so far. I would like to share my knowledge with fellow Excel users!

Latest posts by Shreyas M (see all)

• Lock and Unlock specific cells in Excel - March 22, 2018
• EDATE Function for adding and subtracting months! - March 8, 2018
• Excel Pivot Tables Error Handling - February 26, 2018