Trouble Aligning Excel Currency Symbols

Trouble Aligning Excel Currency Symbols

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.

CurrAlign01

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.

CurrAlign02

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.

CurrAlign03

Then, on the Alignment tab, I changed the Horizontal alignment to General, and clicked OK.

CurrAlign04

Problem Solved

Miraculously, all the dollar signs lined up, without a single wobble, for the different font and sizes.

CurrAlign05

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

38 thoughts on “Trouble Aligning Excel Currency Symbols”

  1. Regarding the following procedure from your blog article…

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

    You can do this more quickly (in my estimation) by simply right clicking the selected cell or cells, selecting “Format Cells…” from the popup menu that appears and then clicking the Alignment tab on the dialog box.

  2. Rick, I’m a big fan of shortcuts, and right-clicks, but in this case the Ribbon button works faster for me.

    Dominik, thanks, that shortcut would help if you already have your hands on the keyboard.

  3. @Debra… I guess the reason the right click is faster for me is it still takes me about 15 seconds just to find the Alignment Tab on that damn Ribbon 😉

  4. Thank you!

    I have been having this problem for sometime now and had given up on trying to solve it. Now, having gone through your post, I can deal with it effectively. 🙂

    Cheers,
    Sumit

  5. Debra,

    I found two mouse solutions that appear to be faster than yours. The first if you’re already on the Home tab of the ribbon is to select the Align Right Text button to turn it on (if it isn’t already) and then press it again to turn it off. This forces text alignment to General. The other solution that is faster for me is if the cells are highlighted (and wouldn’t they be if you’re going to change the alignment?) is to right-click and select center in the mini-toolbar and then deselect center. This also forces alignment to General.

  6. Debra – I’m just glad you and your clients are going to move away from the currency format. That format drives me nuts! The dollar sign is so close to the number that it takes me extra effort to focus on just the value.

  7. Wow thank you for finding the solution to this. I can now cross this off as one of those Excel quirks that has driven me nuts for years. And I work in finance!

  8. Amazing! I can’t believe it’s such a simple solution. I also can’t believe the many times I have googled this and this is the first time it has appeared!! Thank you for sharing.

  9. I want dollar signs aligned at far left. Not a problem unless you want to show zero amounts. Then they just show blanks instead.
    Instead of $ 0.00 it becomes $ –

  10. I have a similar problem and have been unable to find a solution. I am trying to format a balance sheet and put a $ to the far left for the first number in the column, but not a symbol for the rest of the numbers in the column, until the last number in the column. I can not get the first and the last number’s to align in even columns right justified, the numbers with the $ are off centered. Any suggestions?

  11. Holy cr*p I spent way too much time on this problem! Then did a simple Google search (should have done that first), and found your answer. Bang! Fixed. You’re a legend, thanks for posting the solution.
    Now to go see someone about my OCD. Silly dollar signs!

  12. Thank you Debra for posting this solution! It’s great to finally eliminate the wandering symbols. Not sure why your solution is not the Excel default setting.

  13. My OCD has been saved. Thank you very much for this simple fix! I would have never guessed to do it this way.

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

  15. I realize this article is 7 years old (nearly), but it’s still every bit as valid. Thank you!

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

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

  18. This misalignment thing has driven me nuts for years! Thanks for posting, mystery solved! Really, Thanks! -Bill

  19. You’re awesome! This has plagued me for years. And since this is such an old post, I sorely lament not finding it earlier, but as Thanksgiving is coming, the main thing is – gratitude! Thank you for sharing this.

  20. I found another solution. I don’t know why it works. I select the column and left align the cells. The $ signs move to the left but the amounts stay to the right. I then either leave the header cell alone or center it by itself.

Leave a Reply to Rick Rothstein (MVP - Excel) Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.