Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



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.

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.

excel refuses to align a number left or centre

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

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.

Why you may struggle to align numbers in Excel

How to merge cells in Excel (and why not to)

Write paragraphs in Excel

Display how data was entered into a cell for audit trail purposes