Format numbers in thousands in excel

Format numbers in thousands in Excel

A very common requirement is to format numbers in thousands in Excel. With inflation it is now common to want to format numbers in millions!

There are a couple of ways to do this and one option is to divide by the 1 000 or the 1 000 000.

Another option is to make use of the Custom Format option at the bottom of you Format Cells dialogue box. There is however one little trick which relates to your regional settings and whether you should use a comma (,), or a space ( ).

So for this example lets say that my regional settings are set up so that groupings of 1000 are separated with a comma (,) . So 1000000 would be shown as 1,000,000 in my spreadsheet (to check which you use, enter a big number in a cell and format it in thousands).

In this case, to show the number in thousands, you would click on it, click on Format Cells, choose the closest format to what you want (perhaps Numbers, no decimals and tick the ‘Use 1000 separated box), then without clicking OK, immediately click on the Custom format option at the bottom.

You will see that under Type there are some symbols e.g. #,##0. Click at the back of these and add a comma (,) and see what happens to the sample above. Add another comma and see what happens. If you now click OK, you will see that in the cell you see the shortened form of the number, but if you look in the formula bar, the full number is still there (and the full number is used in the calculations)

HOWEVER, if you computer is set up to show thousands with a space (1 000 000), then you must add a space (or as many spaces as you need) at the back of the custom format type bar.