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 belowExcel Pivot Tables Error Handling 1
  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).Excel Pivot Tables Error Handling 2
  5. In such cases, Right Click on the Pivot Table and select PivotTable Options (as highlighted in the below screenshot)Excel Pivot Tables Error Handling 3
  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)Excel Pivot Tables Error Handling 4
  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 sectionExcel Pivot Tables Error Handling 4

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.

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

It's only fair to share...Share on Facebook
0Pin on Pinterest
0Tweet about this on Twitter
Share on LinkedIn