Excel refuses to align a number left or centre

An interesting client question is where Excel refuses to align a number left or centre. The most likely cause for this is the formatting.

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.

excel refuses to align a number left or centre

So one solution is to rather use Number or Currency format.

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.

excel refuses to align a number left or centre

You will now have the benefits of the accounting format (zero shown as a – is one we like) without the difficulty of alignment.