Aligning Custom Formatting Icons

June 3rd, 2014 by datapig Leave a reply »

Hello there Excel nerds.

Well it's been a few weeks since my last post. I've been wrapping up my last book of the year and doing some other projects. I'm ready to get back into the blog saddle.

.

Today, I'd like to show you a quick tip for fixing the alignment of Custom Formatting Icons. If you don't know what those are, see the post I wrote on Improving the Default Percent Formatting.

.

In that post, I show you how to create a visualization effect with Custom Number Formatting where you tag your positive and negative percentages with up and down arrows. This is done by pasting up and down symbols directly into the into the Format Cells dialog box (up arrow goes with the positive number – down arrow goes with the negative number).

.

This gives you a visually appealing alternative to the boring default percentage formatting.

.

Although this is undeniably cool, one thing that has always bothered me about this trick is that the arrows are not aligned. The misalignment of the arrows somehow takes away from the effect.

.

It turns out; there is a fix to this.

Bob Umlas (Excel MVP extraordinaire) showed me this fix at our Excel BI Boot Camp in Arkansas.

To left align the arrows, we can simply add an asterisk and space (* ) after each arrow.

?* 0%;[Red]?* 0%

.


.

This tells Excel to put spaces between the arrows and the percentages. Excel will fill the cell with as many spaces needed to accommodate the width of the cell.

With this fix in place we get a much improved visualization.

.

Thanks Bob!

.

UPDATE: Jared asks a great question: "What if I wanted to add a macro of this format to the QAT? What syntax would I use?"
Here is the syntax you would use to code this Custom Format.
Selection.NumberFormat = ChrW(9650) & "* #,##0_);[Red]" & ChrW(9660) & "* (#,##0)"

.

I'll be back on Thursday for another mind-blowing Excel trick.

Advertisement

12 comments

  1. Jared says:

    I’ve also been bothered by the alignment so this is a great update. If I wanted to add a macro of this to the QAT what would I use if I wanted ? to equal Alt+30 and Alt+31?

    Selection.NumberFormat = “?* 0.0%;[Red]?* 0.0%”

  2. Jon Acampora says:

    Great trick Bob! I’ve seen the asterisk a million times in the number formats, but never realized it was the wildcard that filled the gap. Thanks for sharing!

  3. Umamaheshwar says:

    Thank you Bob!
    Nice trick for formatting.

  4. Very awsome trick!, thanks Mike and Bob!,
    Cheers from Lima Peru!

  5. datapig says:

    Hi Jared: Great Idea to make this into a macro! I added an update to the post.

    You can use this:
    Selection.NumberFormat = ChrW(9650) & “* #,##0_);[Red]” & ChrW(9660) & “* (#,##0)”

    You may have to fix the quotes when you copy and paste into Excel.
    Wordpress uses those crappy curly quotes.

  6. Mynda Treacy says:

    Me too, Jon.

    Thanks for sharing Bob’s tip, Mike. Looking forward to the new book.

  7. PJ Labanowski says:

    Thank you all for sharing and feedback. More awesome excel mojo to pack in my bag…
    P.J.

  8. MF says:

    Nice trick. Now I have a better understand of * in custom format.
    What I used to do is the add a space between the symbol and the #%, together with Distributed horizontal alignment to get similar result.

  9. Saul Espinoza says:

    Beautiful trick, Mike! Thanks for sharing! Greetings

  10. Chris says:

    This is GREAT! Is there a way to change the format to differentiate between zero value and a decimal value less than the number of digits in the format?
    e.g.
    0.00012 in “0.000%” format shows as 0.000%
    I’d like to use custom formatting to read that as “<0.001%"

    Thanks for all the knowledge and help!

  11. datapig says:

    Chris: You can add criteria to your custom number formatting.

    See this post to get an idea how:
    http://datapigtechnologies.com/blog/index.php/forcing-number-formatting-in-a-pivot-table/

Leave a Reply

Powered by sweet Captcha