Excel Custom Number Format Millions and Thousands

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.

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

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.

 

 

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.

 

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

 

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

 

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

 

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

 

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

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.

 

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.

 

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

 

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

 

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

Follow the same process for formatting Numbers in Millions.

 

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

Excel number format millions & thousands is now ready!

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!

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.

 

STEP 1: Select cell E7.

 

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

 

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

 

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

 

STEP 5: Copy the formula down.

 

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

 

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.

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

 

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!