Suppress Pivot Table Errors

Learn Excel from MrExcel Podcast, Episode 2075: Suppress Pivot Table Errors

Hey, welcome back to MrExcel netcast, I'm Bill Jelen.  Today's question from Mike in Columbus, Indiana, 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 that is originally - That's weird.  I want to run and start happening.  Alright, but Mike has a Calculated Field here.  Calculated Field and it's going to be called Growth.  It’s Gross Profit Percent, profit divided by revenue.  I'm going to do this using a name that I own, like up there because I know I'm going to have to change it.  So =Profit/Revenue, like that, Profit/Revenue, click OK.  Alright, and it's great that we're getting answers.  Horrible heading but I'm allowed to change that: GP%, see I 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.  Alright 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 Cell errors as Blank, click OK.  And then when we do Ctrl+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 & Sets, go to Calculated Field where we can get back to the one that we already created, the GPPercent.  And it always amazes me here that you can add new functions in here.  So I'm going to say =IFERROR, if that’s an error, then just put a 0 in or something like that.  Click OK and it works now.  That IFERROR 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 going to do here is double click to get to the Value Field settings.  Go to Number Format and let’s say, if this is a positive number 0.0%, if it’s a negative number -0.0%, but if it happens to be 0 then I'm going to put nothing, alright.  So, that will get those zeros that are appearing and just replace them with nothing. Kind of a hassle that we have to do- go through that.  Although I'm happy, happy that the IFERROR function works in a calculated field.

Alright, so my book, Power Excel 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%.  Then you're getting some errors such as Divide 0 errors.  You can suppress the errors while printing: Go to Page Layout tab, Dialog launcher, fourth tab and say Errors as Blanks or wrap the whole calculation in IFERROR.  And then finally, because that IF there has to return a number such as 0, I used a custom number format to mask those zeros as nothing.

Okay, I want to thank Mike for showing it from my seminar in Columbus, Indiana and I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.