New Excel Forum

This forum has been moved to TeachExcel.

Ask all future questions in the new excel forum.

ExcelKey

Division by 1000 using Cell Format

Free Excel Tips and Tutorials
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at TeachExcel.com.

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

Kevin
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 1 guest