#1
|
|||
|
|||
Displaying 0% as blank in pivot table?
I'm displaying a column as % of row total, and some are showing up as 0%. Can I just make Excel show them as blank? I do NOT want to hide them with filter. I want these data to take space in the pivot table, but with blank rather than with "0%". I couldn't find any solution on Google. |
#2
|
|||
|
|||
I'm not an expert, but could you use Conditional Formating where if the value is equal to 0%, you could make the color the same as the background and then it would appear to be blank? Just a suggestion.
|
#3
|
||||
|
||||
I didn't read this at first because the subject line specified "pivot table". My son tells me I should learn pivot tables, but I don't know much about them yet.
But I just took a look at the question, and it sounds like pivot tables may not be relevant. In normal Excel—and I don't see why pivot tables would necessarily be any different—there are two usual ways to do what you're asking: 1) If you want to do it all the time, you can set one of the Options. I forget what it looks like in earlier versions of Excel, but in 2010 it's under Advanced options and it's labeled "Show a zero in cells that have a zero value". You can set that by worksheet, so that one worksheet displays zeroes and another displays blanks. 2) If you want to control it by particular columns or cells, you can do it with a custom format. Up 'til now you've set the format as a Percentage; now go to the same Format-Cells dialog and select the Custom category. It probably shows a basic Percentage string, perhaps "0.0%". That's the string that applies if the value is positive. If it's negative, zero or a character string, this cell will be formatted using the default rules. In order to override the choice made for a zero value, you have to change the string to something like "0.0%;-0.0%;" The first value (before the first semicolon) is what Excel will use for positive values. The second is how it will display negative percentages (if you have any). The third—but look, there isn't a third; there's just a semicolon, with nothing after it. And that's how Excel will handle it; it'll display nothing. Custom formatting strings give you a lot of power over how numbers are displayed; I think they're well worth reading about and experimenting with. |
#4
|
|||
|
|||
Quote:
There is so much to learn about Excel. |
#5
|
||||
|
||||
Ain't that the truth? There are big chunks of it I've never fooled with, and not only pivot tables.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word is displaying my "compatible" docs as blank! | RobDuBois | Word | 3 | 01-20-2013 04:40 PM |
Pivot Table | uhlersa | Excel | 0 | 10-15-2012 12:01 PM |
pivot table | aliasadi_07 | Excel | 1 | 03-11-2012 12:49 AM |
Table of Contents is not displaying | mhb0825 | Word | 1 | 01-04-2012 03:21 PM |
Pivot Table | Karen615 | Excel | 5 | 08-03-2011 10:46 AM |