MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Suppress Pivot Table Errors

April 13, 2017 - by Bill Jelen

Suppress Pivot Table Errors

How to prevent errors in your Excel pivot table calculated fields. If you are doing some division in a pivot table, you might get some Division by Zero errors.

Watch Video

  • Create a calculated field within a pivot table
  • Give the calculated field a name that you don't like so you can change it later.
  • That field might be returning errors such as Division by Zero
  • Choice 1: Suppress Errors when Printing
  • Choice 2: Wrap the calculation in IFERROR
  • In a pivot table, the second argument of IFERROR has to be numeric
  • Use a Number Format of 0.0%;-0.0%;; to hide the zeroes

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2075 suppress pivot table errors a
  • welcome back to the mr excel neck cast
  • i'm Bill Jelen today's question from
  • mike in columbus india mike has a data
  • set and we're going to build a pivot
  • table from that data set a little bit of
  • information here cost revenue and profit
  • check this out the pivot table field
  • list is appearing alphabetically now
  • instead of in the sequence is originally
  • that's weird i want to run app started
  • happening all right but mike has a
  • calculated field here calculated field
  • and it's going to be called growth this
  • gross profit percent profit divided by
  • revenue i'm going to do this using a
  • name that I out like up there because
  • I'm gonna have to change it so equal
  • profits divided by revenue like that
  • profited by my right and click ok all
  • right and it's great that we're getting
  • answers horrible heading but I'm allowed
  • to change that GP percent so you put the
  • heading I didn't want so I could put the
  • right heading up there things are great
  • except for sometimes we're getting a
  • division by zero error and how to
  • suppress those all right well if it's
  • just an issue when you're printing head
  • to page layout click the dialog launcher
  • go to the sheet tab and sell errors as
  • blank click OK and then when we do
  • control p to print you'll see that the
  • errors are not printing but if it's that
  • we want to hide the things here well you
  • know there's no good number format to
  • hide errors you can't use semicolon
  • semicolon semicolon even in a
  • conditional formatting so what we're
  • going to do instead is go back here to
  • the analyze tab go back to fields items
  • and sets go to calculated field where we
  • can get back to the one we already
  • created the GP percent and it always
  • amazes me or that you can add new
  • functions in here so I'm going to equal
  • if error if that's an error then just
  • put a zero in or something like that
  • click OK and it works now that if error
  • part has to be has to be a number it
  • can't be quote quote I would love to put
  • quote quote in there but they give me a
  • value error so I'm gonna do here is
  • double click to get
  • the value field settings go to number
  • format and say if this is a positive
  • number zero point zero percent if it's a
  • negative number of minus zero point zero
  • percent but if it happens to be 0 then
  • i'm going to put nothing all right so
  • that will get those zeros that are
  • appearing and just replace them with
  • nothing kind of a hassle we have to do
  • go through that although I'm happy happy
  • that the if error function works in a
  • calculated field all right so my book
  • power except with MrExcel goes over a
  • lot of different pivot table examples
  • including creating calculated fields
  • check it out click that I up there in
  • the top right hand corner episode recap
  • we created a calculated field within a
  • pivot table I used a cell or a field
  • name that I did not like because I knew
  • that they were going to give me that
  • weird some have GP percent I could just
  • change it to GP percent then you're
  • getting some errors such as div ID 0
  • errors you can suppress the errors while
  • printing go to the page Layout tab
  • dialog launcher fourth tab and say
  • errors as blanks or wrap the whole
  • calculation in if error and then finally
  • because that if there has to return a
  • number such as 0 I use a custom number
  • format to mask those zeros as nothing ok
  • i want to thank Mike for showing it from
  • my seminar in columbus indiana and i
  • thank you for stopping by we'll see you
  • next time for another NetCast from mr.
  • excel

Download File

Download the sample file here: Podcast2075.xlsm

Title Photo: stevepb / Pixabay

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.