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.
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.