Every now and then I get a workbook from a client with numbers in Accounting format. If all the numbers are the same length, the currency symbols line up nicely. However, if the numbers are different lengths, we have trouble aligning Excel currency symbols.
Currency Symbols Do Not Line Up
For example, in the screen shot below, the $ sign in cell B4 is a bit to the right of the symbol in B5. It’s not much, but it looks sloppy, and some fonts show the effect more obviously than others. I added the blue line to make it easier to see the variations in column B.
Trying to Fix It
I’ve spent way too much time trying to fix the problem, with custom formatting, different fonts, and yelling. Even fist shaking didn’t help — very odd!
So, I learned to live with the problem, and tried to convince the client to use Currency format instead. The dollar signs aren’t supposed to line up in that format, so it’s much less stressful.
Finding the Solution
Finally, this weekend, after another attempt at lining up the dollar signs, I found the solution to the wobbly Accounting symbols. I checked the horizontal alignment for the cells, and discovered that my client had formatted them as Right aligned.
I clicked the button at the bottom right of the Alignment group on the Ribbon’s Home tab, to open the Format Cells dialog box.
Then, on the Alignment tab, I changed the Horizontal alignment to General, and clicked OK.
Problem Solved
Miraculously, all the dollar signs lined up, without a single wobble, for the different font and sizes.
I’m not sure why the right alignment had been applied earlier, because numbers would automatically align to the right. Anyway, the alignment is the first thing I’ll check, the next time I have trouble with wandering currency symbols.
And if you already knew how to fix this problem, you should have told me years ago!
______________
My OCD has been saved. Thank you very much for this simple fix! I would have never guessed to do it this way.
You’re welcome! Glad the formatting tip helped you
This fixed my $ line-up, but now my numbers don’t line at the ending spot (tens location for those accounting folks). Driving me nuts!
That’s the Text Wrap in the cells. In the same alignment dialogue uncheck the “Wrap text” box.
I realize this article is 7 years old (nearly), but it’s still every bit as valid. Thank you!
You’re welcome! I’m glad the information is still helpful, and thanks for letting me know.
All those years ago, and STILL useful — thank you!
I think I never even realized Accounting was an option, or what it did! But now, when I need to know, BOOM there you are. Much obliged!
THANK YOU SO VERY MUCH!! You have save me lots of aggravation! This problem stands out even MORE when you have an Accounting formatted column with both positive and negative numbers and now it is gone!
You’re welcome! Thanks for letting me know that it helped.
This misalignment thing has driven me nuts for years! Thanks for posting, mystery solved! Really, Thanks! -Bill
Thanks, Bill! Glad it helped you