Whenever you do a calculation in an Excel Pivot Table you may get an error value like a #DIV/0!

This looks ugly when you are presenting important information.  Luckily you can override this with a custom value or text.

To activate this you need to Right Click in any Value in your Excel Pivot Table and choose PivotTable Options and “Check” the box that says: For error values show

This activates the box and you can now enter any value or text that you want to show whenever your calculation has an error.

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We have an error in the pivot table calculation.

 

STEP 2: Right click on any value and Go to Pivot Table Options.

 

STEP 3: Check the Box: For Error Values Show

 

STEP 4: Enter any text or value

 

Now your error values are properly formatted!

Format Error Values

HELPFUL RESOURCE:

728x90

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Color Scales in a Pivot Table Color Scales are new in Excel 2010 and they highlight the smallest and largest data points within your Pivot Table.It works like a heat map, in that it gives the highest number a shade of color and the lowest number a different shade of color, so your numbers can stand out at...
Insert Blank Rows In a Pivot Table Pivot Table reports are shown in a Compact Layout format as a default and if you have two or more Items in the Row Labels (e.g.Month & Customer), then the Pivot Table report can look very clunky...There is a cool little trick that most Excel users do not know about th...
Pivot Table Calculated Item Pivot Table Calculated Items allow you to do mathematical calculations with your Item List.  You can use any of the Excel mathematical equations, like /*+-%.The only limitation is that you cannot reference any cells.  Pivot Table Calculated Items can be used to calculate chan...
Group By Month With Excel Pivot Tables Before I was a Pivot Table guru, I had to get individual rows of daily sales and group them into a report showing the monthly sales during the year.Grouping these would take a ton of effort using Formulas:Extracting the month and year from each transactional date; Th...