Aligning Custom Formatting Icons

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.

13 thoughts on “Aligning Custom Formatting Icons

  1. Jared

    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

    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. datapig Post author

    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.

  4. PJ Labanowski

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

  5. MF

    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.

  6. Chris

    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?
    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!

  7. Jane

    I tried to paste a symbol into my custom format but it didn’t work. It showed up as a period. What’s the trick?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>