Obviously, we don’t want our PivotTables littered with #DIV/0! and #NULL! errors if we’re presenting them in a report (like the one below), that would just create unnecessary questions and we’re busy enough.
Thankfully Excel PivotTable error handling is easy to control via the PivotTable Options; right-click the PivotTable > PivotTable Options >
On the Layout & Format tab check the ‘For error values show’:
Instead of the error you can show text, like ‘N/A’, a number or leave it blank. I usually leave it blank, as you can see in the image below, because this is most likely to be suitable for all errors. Whereas if you put a value like 0 or 1, then that may not be correct in every scenario.
Types of PivotTable Errors
In the PivotTable below, column F; ‘% Change’ is a ‘Show Values As’ calculation for ‘% Difference From…’, which is uses the following formula:
=(current month – previous month) / previous month
When the ‘previous month’ is zero we get the #DIV/0! error, and when the current month has no data we get a #NULL! error, as you can see below:
#DIV/0! errors are triggered by dividing by zero. It’s the most common PivotTable error and is typically found in calculated fields or calculated items, or a calculation from the Show Values As options.
#NULL!, errors are triggered when a formula references an item that is blank. In the example above, you can see the source data for October is an empty cell (B15). You can fix this by replacing blank cells with zeros...but then you might end up with #DIV/! errors 🙂
Blanks – Notice Jan and Nov have blank results in the % Change column?
A blank is returned when the previous month is blank. And while this isn’t an error as such, some of you may prefer to see 100% or ∞, or some other value in there. After all, if you’ve gone from nothing to something (positive) then isn’t that an improvement?
Well yes, but that doesn’t mean you can represent the improvement in percentage terms.
Handling Percentage Change Where Prior Period is Zero
From time to time someone will ask me how they can override these blanks and display 100% change when the prior period was zero.
The answer to this question is always, “I’m not telling”! Not because I’m being mean, but because it would be wrong to show the percentage change from zero as 100%, or even 0%.
I don’t just leave them hanging with “I’m not telling”, I told you I’m not mean. 😉 I go on to explain why, like so:
Let’s say yesterday you had $0 and today you have $10. If you were to say that today you have 100% more $ than yesterday, then you’d be saying that you still have $0 because $0 + (100% x $0) = $0, when in reality you now have $10.
Whereas if yesterday you had $5 and today you have $10, then you can say you have 100% more $ today because $5 + (100% x $5) = $10.
In other words, you can only calculate the percentage change from ‘something’, and zero is nothing. i.e. something is a number other than zero.
So, the accepted answer when calculating the change from zero is ‘not meaningful’ or leave it blank.
And before you think you can sneak 100% in the ‘For empty cells show’ field in the PivotTable Options, I’m happy to say that won’t work for the % Difference From empty fields.
Forward this tutorial to those you know who insist the percentage change from nothing to something is 100%?
Download the Workbook
Enter your email address below to download the sample workbook.
If you liked this please click the buttons below to share.