You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hiding zeros and zero sum results (0) in a pivot table numbers, is it possible?
The problem is that the zeros reappear when the table is refreshed with new data.
As a result I have to reapply the format even 20 times a day in many Pivot tables (it's crazy).
Is there a solution?
MacBook Pro 16″
Maybe I'm missing something - can't you just add a custom format to the cell that blank out 0-value cells?
Assuming the cells are currently formatted as Number, create a custom format:
Here you can see the rule "If equal to 0" which has no formatting, essentially blanking the cell.
I must be misunderstanding your problem.
Here is my example table of data:
Cells in columns B and C have a "don't show zeros" custom format. All cells that look blank have zeros in them
Here is my pivot table:
No formatting was applied, this is how it came out.
If I add new rows to my data table and if they also have the "no zeros" format, they'll show up on the pivot table using that format when the pivot table is refreshed.
But if you want to see the zeros in the data table, just not in the pivot table, this won't do it for you.
In fact this is the problem, setting the custom format (if equal to 0 -> empty), with the addition of new data, in the new data the custom format is not applied.
In practice every day I do more than 20 times this:
- I add data
- I apply the custom format to the whole column
- I add data
- I reapply the custom format to the whole column
- and again... and again...
I found the solution... my problem was not the "0", The problem is that the cell is empty (I stupidly thought: empty cell = 0, but it is not so, empty cell is empty).
Thanks for the help.
(it is easy to cry bug, when you are stupid... I mean me)
I mean this happens in a Pivot table, the custom format, with the addition of new data keeps (in the new data) all the custom format, but does not read the rule if equal to 0.
Maybe it's a Bug...
It appears that if the source data is using the "don't show zero" format then the pivot table will also.
No it's not like that, there is no solution... I have done all the possible and unimaginable tests... the new data does not read the rule.
It is a bug, only an update will fix it.
Pivot Table hide zero (0)
