Bottom line: Learn how to remove or replace the error values in your pivot tables.  You will also learn how the Pivot Layouts feature of the PivotPal Add-in can save a lot of time with pivot table settings.

Skill level: Beginner

Handling Errors in the Pivot Table Values Area

How To Handle Remove Pivot Table Errors

The most common type of error I see in the values of a pivot table is Divide by Zero (#Div/0).  This error is typically caused by a calculated field or calculation on a field (show values as option).

Displaying the error can make our pivot tables look ugly.  Fortunately, there is a way to remove or replace the error.

Step 1: Open the Pivot Table Options Menu

The PivotTable Options menu can be found on the left side of the Analyze/Options tab in the ribbon when any cell is selected in the pivot table.

Pivot Table Options Button on Analyze Options Tab of Excel Ribbon

You can also Right-Click on the pivot table and choose PivotTable Options.

Step 1 Right Click Pivot Table Options Menu

Step 2: For Error Values Show Checkbox

The “For error values show” option on the Layout & Format tab of the PivotTable Options will allow you to handle the errors.

Once the box is checked, the value in the text box to the right will be displayed in the pivot table.  You can put a number or text in this box.  The error values in the pivot table will be replaced with whatever you put in the text box.

You can also leave the box blank to display a blank cell in the pivot table.

Step 2 For Error Values Show Check Box - Pivot Table Options

Press OK after you have made your changes and the pivot table will display the changes.

Unchecking the option will display the errors again.

Here is a before and after of what the pivot table looks like when displaying zero for errors.  As you can see, the number formatting is also displayed for the zero values.  The field is formatted as a percent, and the zeros are formatted as a percent as well.

Pivot Table Divide by Zerro Error Before and After

Save Time with PivotPal & My Pivot Layouts

By default, the “For error values show option” is unchecked.  This can be good because it lets you know that there are errors in your calculations.  But it can also be annoying to have to go change this setting, or any of the other 30+ pivot table options, every time you create a pivot table.

In the video above I demonstrate how the My Pivot Layouts feature of PivotPal will save you a lot of time when modifying the settings of your pivot tables.  You can create custom profiles that save all of your pivot table settings, and then quickly apply all the settings to your pivot tables at one time.

This feature will save you a ton of time!

Try PivotPal Today

This week you can save 20% on PivotPal when you use the code layout20 at checkout.  The discount expires on Sunday August 23rd at midnight PST.

PivotPal Box 20 Off

Click Here to Get PivotPal Today

Please leave a comment below with any questions.  Thanks!


Your email address will not be published. Required fields are marked *

  • Hi Jon
    I have a massive external database and we used to calculate the rate.
    One field is total price and the other field is total weight for many zones. So the Pivot formula is Total Price/Total weight. This formula worked and we could get a rate per zone as a automated Pivot and a graph was done linking to the Pivot. Over time this formula brings “0” now. I checked the total price listing from double clicking the total line and found some rows have no information. I am unable to correct the data source as it’s an external file. Is there a way around correcting this issue in the total Price so the formula works again.

    Thanks for your help.

  • Thanks Jon! I have been working with tables and VBA just a short time and this issue has cropped up multiple times. I’ve been able to avoid it but not today. Glad I found this solution. Thanks again.

    • Hi Cameron,
      Great question! I don’t believe there is a way to do that within the pivot table settings. There is an option to not “Show items with data” in the Field Settings under Layout & Print, but that would not hide the columns in this case because the field still has data for some of the rows.

      You could apply filters to the pivot table and filter out the rows that contain zero for the Total Forecast. For the filters to work on a pivot table you have to select the cell to the right of the pivot table with the headers and then turn the filters on. Here is a quick screencast that shows an example.

      Pivot Table Data Filter on Column Headers - Filter Out Zeros

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...