One of the things that always bothered me about Excel was the way it handled right alignment in cells with certain types of number formatting applied.
For example, in the following illustration you’ll see that some cells containing values have currency formatting and other cells containing values have comma formatting applied. The line up nicely in a column. But when right-align the column headings as I’ve done here, the headings don’t line up with the numbers:
This is the case in all recent versions of Excel that I’ve worked with, including Excel 2004 for Macintosh and Excel 2007 and Excel 2003 for Windows. (The illustrations here are from Excel 2007 running on Windows Vista.)
Why It Happens
Before we can fix this problem, we should have a good understanding of why it occurs.
You may have noticed that when you apply currency or comma formatting (for example) the numbers shift to the left. Ever wonder why?
Well, you can thank Excel for thinking ahead about something that you might not have in a column of numbers: negative values. As shown here, certain number formatting options place parentheses around negative numbers. To make sure all the numbers in a column line up, positive numbers are shifted by the width of a parentheses character to the left. So every number formatting with currency or comma formatting actually has a tiny bit of space after it.
Since you don’t normally apply currency or comma formatting to text, cells containing text are truly aligned against the right side of the cell when you use the right alignment formatting option.
But guess what? You can apply currency or comma formatting to a cell containing text. And when you do, the contents of the cell will shift to the left by the width of one parentheses character. The result: everything is nicely aligned: