Formatting Numeric Data to “Millions” in Excel

By Liying Cui, Statistical Modeling Analyst, Southern New Hampshire University

When we report large numbers (especially related to financials), the default number formatting in Excel does not show up nicely in tables or charts, making it hard to read in a glance.

One way to address this is to use custom number formatting in Excel, to show numbers in “millions.” instead of integers.

Follow these simple steps

  1. Select the cell you’d like to format. (A1 in the example)
    millions4.PNG

  2. Click the ribbon Home, right-click on the cell, then expand the default to show “Format Cells” dialog.
    millions3.PNG

  3. In the Format Cells dialog box, on the Number tab, select Custom, then enter #,, “Million” where it says General. (Note: there is a space between the second comma and the double quotation mark.) The displayed value changes from 1000000 to 1 Million.
    millions2.PNG

After you create the custom format code, you can reuse it in the same document. The format code does not automatically save to Excel when you create a new document.

The number format can be displayed on charts, too.

millions1.PNG
Once you master this code, you can change it to show thousands or millions, etc. with decimal points.

table.PNG

 

 Comments

 
To add a comment, Sign In
Total Comments: 4
 
Mete posted on 1/22/2018 3:03 AM
useful, thanks
Lee posted on 1/22/2018 8:29 AM
Thanks Liying, this is a helpful tip!
Lisa posted on 3/22/2018 12:50 PM
Handy! Thank you.
Latoya posted on 11/2/2018 8:53 AM
Is anyone familiar with how to format a number such as $34,553,125 to 34.5M in excel? I am currently using a formula =34553124.77/1000000 to obtain $34.55. Is there a custom format to apply? Thank you