Pinterest Pixel

Excel Custom Number Format Millions and Thousands

Excel custom number format Millions and Thousands Many times, you might have large numbers in an Excel... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Excel Custom Number Format Millions and Thousands | MyExcelOnline Excel Custom Number Format Millions and Thousands | MyExcelOnline

Excel custom number format Millions and Thousands

Many times, you might have large numbers in an Excel report and it is hard to decipher and read the number at one glace.

The best way is to show the numbers in Thousands (K) or Millions (M).

Say, a number 45,200,000 will be displayed as 45.2 Million.

Excel Custom Number Format Millions and Thousands

Custom Formatting

Fortunately, large numbers in Excel can be formatted so they can be shown in “Thousands” or “Millions”.

By using the Format Cells dialogue box shortcuts CTRL+1, you will need to select CUSTOM and then enter one comma to show Thousands or two commas to show Millions. You can even add some text in your cells by entering any word within the quotation marks ” your word “.

But before we move forward, it is important to know that certain characters in custom formatting have specific meaning.

  • Zero (0) – Display insignificant zeros
  • Pound Sign (#) – Display significant zeros
  • Comma (,) – Thousand separator
  • Quote (” “) – Add text present within the quotes
See also  Drop Down List with Data Validation

You can create Excel Custom Number Format Millions and Thousands using either placeholder zero or pound sign. Let’s look at both of them one-by-one.

 

With Placeholder Pound Sign (#)

#,##0, “ths”

#,##0,, “mills”

In the example below, you have sales data with the sales amount mentioned in columns D & E. Using the Excel number formatting, you need to convert excel format number in millions & thousands.

Excel Custom Number Format Millions and Thousands

 

 

Follow the step-by-step tutorial to understand how to add Excel custom number format millions and thousands and make sure to download this workbook to follow along:

Download workbookNumber-Formats-Ths-Mills.xlsx

 

STEP 1: Select Column D in the data below.

Excel Custom Number Format Millions and Thousands

 

STEP 2: Press Ctrl + 1 to open the Format Cells dialog box.

Excel Custom Number Format Millions and Thousands

 

STEP 3: In the Format Cells dialog box, Under Number Tab select Custom.

Excel Custom Number Format Millions and Thousands

 

STEP 4: Type #,##0, “ths” and Click OK.

Excel Custom Number Format Millions and Thousands

 

STEP 5: This is how the Column D after number formatting will look –

Excel Custom Number Format Millions and Thousands

 

STEP 6: Follow the same steps for Column E as well and type #,##0,, “mills” under the custom section.

Excel Custom Number Format Millions and Thousands

The only difference between the two customer format (Thousands & Millions)  is that you have to put 1 comma for Thousands and two commas for Millions.

See also  How to Use Flash Fill In Excel

 

Using Placeholder Zero (0) & Decimal Point

0.0, “K”

0.0,, “M”

 

Zero is used to display insignificant zeros when the number has fewer digits than the format represented using zero. For example, a custom format 0.00 will display number 5, 8.5, and 10.99 as 5.00, 8.50, and 10.99 respectively.

Also, you can round off the number using decimal points symbol.

 

To get this formatting done, follow the steps below:

STEP 1: Select Column D in the data below.

Excel Custom Number Format Millions and Thousands

 

STEP 2: Right-Click and then Select Format Cells.

Excel Custom Number Format Millions and Thousands

 

STEP 3: In the Format Cells dialog box, Under Number Tab select Custom.

Excel Custom Number Format Millions and Thousands

 

STEP 4: In the Type section, type format – 0.0, “K” and click OK.

Excel Custom Number Format Millions and Thousands

Follow the same process for formatting Numbers in Millions.

 

STEP 4: In the type section, Enter 0.0,, “M” and Click OK.

Excel Custom Number Format Millions and Thousands

Excel number format millions & thousands is now ready!

Excel Custom Number Format Millions and Thousands

One thing to note is that this will just format the way the number looks like on the Sheet. The number stored in the cell remains the same!

See also  Lock & Protect Formula Cells

You can use the ROUND function to not just change the formatting but the change the number as well.

 

ROUND Function

In this method, you have to do three things :

  • Divide the number by 1000,000.
  • Round off the decimal places.
  • Use & sign to add text “M”.

 

In this example, you have the sales amount mentioned in Column D. Let’s use the combination of division, round, and & sign to get the formatting done.

Excel Custom Number Format Millions and Thousands

 

STEP 1: Select cell E7.

Excel Custom Number Format Millions and Thousands

 

STEP 2: Start with the division. Type =D7/1000000.

Excel Custom Number Format Millions and Thousands

 

STEP 3: Add Round Function to this. Type =ROUND(D7/1000000,1).

Excel Custom Number Format Millions and Thousands

 

STEP 4: Add Text to this formula using & sign. Type =ROUND(D7/1000000,1)&” M”.

Excel Custom Number Format Millions and Thousands

 

STEP 5: Copy the formula down.

Excel Custom Number Format Millions and Thousands

 

STEP 6: Copy the Column and the Press Alt + E + S to open the Paste Special Box and Select Value. Click OK.

Excel Custom Number Format Millions and Thousands

 

The only difference between using Custom Format & Round Function is that :

  • In Custom Format, only the formatting changes but the number stored remains the same.

Excel Custom Number Format Millions and Thousands

  • In Round Function, both the formatting and number changes.

Excel Custom Number Format Millions and Thousands

 

See also  Turn Text To Values With Paste Special Values

Conclusion

In this article, you have been taught how to do Excel custom number format millions and thousands. There are two ways to do so. You can either use custom format options available in Excel or use a combination of division, round, and & sign.

There are various analytical tools inside Excel, like Conditional Formatting, Data Validation, Tables, Sort & Filter plus MORE!  Click here to learn more!

You can learn more about Excel with Formulas, Pivot Tables, and Macros here!

If you like this Excel tip, please share it
Excel Custom Number Format Millions and Thousands | MyExcelOnline Excel Custom Number Format Millions and Thousands | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...