Improving the Default Percent Formatting

August 1st, 2012 by datapig Leave a reply »

Every time I need to create a dashboard in Excel, I’m reminded at how sparse the default formatting options for percentages are. I mean take a look at the default percentage formatting.

.

This is it….this is all you get.

.

Go to the format cells dialog box, and the options for Percentage basically consist of adding decimals.

No way to show a plus sign (+) for positive percentages – no way to wrap negative percentages in parentheses – no way to make negative percentages red – etc.

.

.

But it doesn’t have to be this way for reporting geniuses like us.

.

All you have to do is put in your own custom formatting in the Format Cells dialog box.

Simply click the Custom category, and then enter the formatting in the Type field.

.

.

Let’s say you want to format your percentages so that the positive percentages are preceded with a plus sign (+), while negative numbers are wrapped in parentheses.

You can simply enter this format syntax in the Custom Type field: +0%;(0%)

.

Here is the resulting format. Nifty!

.

So how does this work? Well, every number format has essentially 4 parts (separated by semicolon).

format for positive numbers ; format for negative numbers ; format for zero ; format for text

.

So to format positive percentages with a + sign, you would enter

+0%

Add to that – a format for negative percentages wrapped in parentheses. Note the separation by semicolon.

+0%;(0%)

.

.

Want to get fancy with colors? Simply add a color tag to each part of the Custom format syntax.

[BLUE]+0%;[RED](0%)

.

Here’s what you get:

.

.

Ok…let’s get crazy. How about adding a nice visual touch?

Put your cursor in any empty cell, and then select Insert -> Symbol on the Ribbon.

This will call up the Symbol dialog box.

.

While here, do this:

Select the up arrow then click insert.

Select the down arrow then click insert.

.

.

Now go to the formula bar and copy the two symbols you just inserted.

This will get them onto your clipboard.

.

.

All you have to do now is paste the appropriate symbol into the Custom format syntax (i.e. up arrow goes with the positive number – down arrow goes with the negative number).

.

Snazzy!

.

As a useless but interesting side note, check out the status bar when you highlight your newly formatted numbers. Arrows in the status bar? Outrageous!

.

.

Like this tip? Well there’s plenty more where this came from. How much more?

Well, come find out.

.

Jon “Excel Charts” Peltier and I are putting on our annual

Excel Dashboards and Visualizations Boot Camp
September 13th – September 14th

Dallas, Texas

.

Click the link to check out the details.

Hope to see you all there!

Advertisement

22 comments

  1. That’s a pretty clever idea, using non-standard characters in a custom format string. I might borrow steal that for the Excel 2013 Bible.

  2. datapig says:

    John: Go ahead. The only thing I would’ve wanted in exchange for the idea was your awesome Thomas Kinkade Faith Mountain sculpture. But you squandered it long ago.

  3. Ken Puls says:

    Cool, Mike. I wasn’t aware that you could do that!

  4. Might I suggest, when using parentheses, to include space in the positive to line up the percent signs

    +0%_);(0%)

    The underscore tells Excel to leave enough space for the character that follows next.

  5. deadeye says:

    Is it possible to do more than 2 conditions? >0%, <0%, and =0%?

  6. Jeff Weir says:

    I can’t believe you passed up on mentioning these Unicode “Greatest Hits”
    -A very happy snowman at 9731 (or Arial Unicode MS no. 2603)
    - The skull and crossbones at 9760 (or Arial Unicode MS no. 2620)
    - The old soviet hammer and sickle at 9773 (Arial Unicode MS no. 262D, ironically placed right next to the Peace symbol at 262E)
    - Great cleavage at 5729
    A man in what appears to be a French Foreign Legion hat at 12320 (or Arial Unicode MS no. 3020, with the name ‘postal mark face’??)
    - a funny little beatle at 3424

    There was a good post over at http://www.powerpivotpro.com/2012/07/wingdings-and-other-symbolic-fonts-in-slicers/ the other day about using these for slicers.

    And Chandoo did a post back in 2008 on using characters in chart axis. http://chandoo.org/wp/2008/08/21/display-symbols-excel-chart/

    Jon Peltier somehow managed to sneak his company logo into Webdings (character 143).

  7. datapig says:

    deadeye: All you need to do is add a third formatting syntax to the zero part.
    For example:

    +0%;(0%);”N/A”

    This syntax will format percentages so that:

    positive percentages are preceded with a plus sign
    negative percentages are wrapped in parenthasese
    zeros automatically show as “N/A”

  8. pedjvak says:

    tanx for your sweet tips

  9. guy says:

    Thanks for the GREAT tips. I can hardly wait to use the up-and-down arrow symbols in my charts.

  10. IRAN says:

    thanks.

  11. Krishnan says:

    lovely tip.

    Great.

  12. Bilal says:

    John, very nice and clever trick.

  13. Asar says:

    This is awesome. i just used it on my Pivot Tables for a comparative analysis, and the people are just gung-ho about it.

    Thanks. :-)

  14. Asar says:

    I got a question here, if you can kindly help:

    When we want to change the color of the format, we simply write [Blue], [Green] etc.
    What if I want the symbol to be Blue, but the %age to be Black?
    Also, any advice about where we can choose colors from? The Help section in Excel only gives us 8 colors to choose from.

    Thanks,
    Asar

  15. Dave says:

    My symbol insert dialog does not have the subset drop down box, is there a setting or library that I need to display it?

  16. Marcelo says:

    Very practical tip from Datapig. Thanks
    Also, good tip from Dick about including a space _). I suggest also to use – sign for negative %. It looks very professional.

  17. Jared says:

    You can use ALT+30 and ALT+31 to insert the symbols instead of inserting from the symbols list and then doing a copy and paste. So you can type [Blue] ALT+30 0.0%;[Red] ALT+31 0.0% and it will give you [Blue]?0.0%;[Red]?0.0%

Leave a Reply