Custom format in excel to display easier to read millions and thousands

Sam is a financial manager at a large investment bank and has to work with large numbers. She wants to format her sheet so that 25,000,000 can be displayed as 25M which is easier to read. However, she wants to be still able to use the data for other calculations.

Select the cell that has the number and open the Format cells window (Right click on the cell and choose Format cells or use the shortcut Ctrl+1)

Select the custom category on the left and type #  ” M” under the Type text box. Note that there are 2 spaces after the # and one space before the M. The sample of the result will be displayed in the Sample box above the text box. You can use that to verify that your formula is correct. So 25,000,000 gets converted to 25 M

If you want to display the number in thousands with a K, use the format # ” K”. Here we have a single space after # and a single space before K. Note the 25,600 automatically rounds up to 26K. If the number was 25,400 it will round it down to 25 K

In practical instances you could have a large or a small number in the cell and you would like to use the appropriate suffix (M if greater or equal to a million, K if greater or equal to a 1000, otherwise leave it as it). To do that change the format to

[>=1000000]#  ” M”;[>=1000]# ” K”;#

The semicolons (;) indicate that there are more than one conditions. The section in the brackets [] indicates the condition under which the rule should be applied.

 

Below is a sample of how all the results will be displayed

Leave a Reply

Your email address will not be published. Required fields are marked *