Thousands format

Display values as thousands

By Neale Blackwood

If you need to display numbers as thousands in a report you don’t need to divide all the numbers by 1000, you can apply a Custom format to the cells and have Excel display the values as thousands. The underlying values are unchanged but they are displayed as thousands.

To format as thousands, first select the range, then right click it and select Format Cells (Ctrl + 1 (that’s the number one) will also display the Format Cells dialog). In the Number Tab, at the bottom of the Category list select Custom. In the Type box enter

#, 

and Click OK.

To see values as millions use

 #,,

And Click OK

Custom Format warning!
Whenever you use a custom format there is a danger that a user may misinterpret the figure displayed. Eg displaying 3,000 for a cell value of 3,000,000.

For example a user may multiply the cell displayed as 3,000 by 1000 (thinking it actually contained 3,000) to convert the value to dollars and end up with a value of 3,000,000,000.

If other people are using a spreadsheet make it plain that custom formats have been applied. You can do this via cell comments, text boxes or explanations in an Instructions or Notes sheet.