New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Division by 1000 using Cell Format

Free Excel Tips and Tutorials

Division by 1000 using Cell Format

Postby Kevin UK » Fri May 23, 2014 4:16 pm

To display a number as 1/1000 of its actual value (divided by 1000) in any cell you can use the comma in the cell format.

For example, where the cell value = 5760000
  • apply the Custom cell format #,##0, (with a trailing comma) to display 5,760
  • apply the Custom cell format #,##0,, (with 2 trailing commas) to display 5.76
The cell value isn't altered by the display format.
This is very useful for balance sheets where millions and billions need to be converted to readable numbers.

To set a Custom format click Format -> Format Cells -> Number (tab) -> Custom
Type the format in the Type field.

To do the same thing in a pivot table, right click a cell in a column that you wish to format, select Value field settings then number. Apply the formatting that you require.

Kevin UK
Excel Master
Posts: 568
Joined: Jun 24, 2012
Reputation: 86
Excel Version: 2016

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 24 guests