How to Remove Errors in Your Pivot Tables + Video - Excel Campus
10

How to Remove Errors in Your Pivot Tables + Video

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Mohsin - September 16, 2017

I correct this issue. Thank you sir for your help

Reply
Annie - February 10, 2017

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.

Reply
Dana Bunting - August 1, 2016

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.

Reply
cameron - May 26, 2016

suppose i don’t want the #DIV/0! row to show at all. how do i do that?

Reply
    Jon Acampora - May 30, 2016

    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

    Reply
Chuck - November 25, 2015

I really like your tips, because they are very helpfull.
Thank you very much.

Reply
Allen Mathurin - August 19, 2015

I enjoyed the quick video about the deleting error #div/0 and formatting. Great job.

Reply

Leave a Reply: