An interesting client question is where Excel refuses to align a number left or centre. The most likely cause for this is the formatting.
Table of contents
YouTube Excel refuses to align a number left or center
Formatting options impact on number alignment
When you (or someone else if you are working with another person’s spreadsheet) format numbers, you generally have 3 options being:
- Number,
- Currency (you can have no currency symbol which makes it just a number),
- Accounting (you can also specify no currency symbol so it just looks like a number).
As shown below, all these number formats have been centred BUT the accounting format refuses.
So one solution is to rather use Number or Currency format as the Accounting format is pre programmed to align right always.
But people use Accounting format for a reason so perhaps you just want to use the benefits of the accounting format but stop this forced right alignment. You can achieve this by changing the format in custom format.
As shown below, highlight the cells where the Excel numbers won’t left or centre align and:
- go to Format Cells,
- Click on Custom,
- You will see the code that tells Excel to use the Accounting Format,
- Delete the * in the format,
- Click OK.
You will now have the benefits of the accounting format (zero shown as a – is one we like) without the difficulty of alignment.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Why you may struggle to align numbers in Excel
How to merge cells in Excel (and why not to)
Display how data was entered into a cell for audit trail purposes