Excel Custom Number Format – Millions with One Decimal (6 Ways)

If you are looking for some of the easiest ways of custom number format for millions with one decimal place in Excel, then you are in the right place. Because millions (1,000,000) are a large number to do any type of calculation or read the value immediately is so tough and to do this task easier we can shorten these values by up to one decimal value with a suffix as M to recognize 1.0 M for 1,000,000.
So, let’s start our main article to learn more about this number format.

Excel Custom Number Format – Millions with One Decimal: Learn 6 Ways to Get This

Here, we have the following dataset with large sales values which is troublesome to read within a short time. So, we are going to use the custom number format option for these values to change them to millions with one decimal. Moreover, we will use some functions for this purpose.

We have used Microsoft Excel 365 version here, you can use any other version according to your convenience.

Method-1: Using Custom Number Format Option with Number 0

Here, we will use the custom number format option with the number 0 for changing the formats of the sales values into millions with one decimal like 1.0 M for 1,000,000.

Steps:
➤ Select the range and then go to the Home Tab >> Number Group >> click on the Number Format dialog box symbol.
You can press CTRL+1 instead of going through this path.

Then, the Format Cells dialog box will open up.
➤ Click on the Custom option and then write the following format in the Type box

Here, 0 is for the numbers, and the two commas are for millions as the million value has two commas (1,000,000), and “M” is the suffix we want after the value to represent millions.

➤ Press OK.

After that, we will get the millions with a digit after the decimal point and the million indicator suffix M. The values will be in Number format so you can do any type of calculations.

Read More: How to Custom Number Format in Excel with Multiple Conditions

Method-2: Using Hashtag Symbol in Custom Number Format for Million Numbers

Here, we will also use the custom number format for changing the formats of the sales values but instead of using 0 only, we will use the Hashtag (#) symbol also.

Steps:
➤ Select the range, and then, go to the Home Tab >> Number Group >> click on the Number Format dialog box symbol.

Afterward, the Format Cells dialog box will open up.
➤ Click on the Custom option and then write the following format in the Type box

Here, # is the placeholder of any numeric value before the decimal point and 0 is used after the decimal point if any value has no value after the decimal point then it will show zero-like 1.0 M. And, the two commas are for millions as million value has two commas (1,000,000) and “M” is the suffix we want after the value to represent millions.

➤ Press OK.

Finally, we will get sales values in our desired format millions with one decimal place and these values will be stored as numbers.

Method-3: ROUND Function to Set Custom Number Format for Millions with One Decimal

In this section, we are going to use the ROUND function along with the Ampersand operator to change the formats of the large sales values into millions with a value up to one decimal place.

Steps:
➤ Type the following formula in cell E4

Here, D4 is the sales value.

➤ Press ENTER and drag down the Fill Handle tool.

As a result, we will have our desired formats for the sales values but they will work as text here so no calculation can be done.

Read More: How to Format Number to Millions in Excel

Method-4: Using Paste Special Option for Millions with One decimal

Using the Paste Special option with the combination of the ROUND function and Ampersand operator we will change the format of the numbers of the sales values to show them as millions with one digit after the decimal point.
For the execution of the Paste Special option, we need the value 1000000 (as 1M = 1000000), and so we have entered it following the dataset.

Steps:
➤ Choose the value of cell C13 and then press CTRL+C.

➤ Select the range of the Sales column right-click on your mouse and then select the Paste Special option.
You can do it also by pressing the shortcut key ALT + E, S (ALT and E at a time and S later).

After that, the Paste Special wizard will appear.
➤ Click on the options Values and Divide, and then, press OK.

Now, we can see that the sales values have been divided by that value and give us the fraction numbers. So, we need to round them and add the million indicator suffix M.

➤ Write down the following formula in cell E4

D4 is the fraction sales value and ROUND will round it to a value with one digit after the decimal.
Then, & will add up this value with the suffix M including a space between them.

➤ Press ENTER and drag down the Fill Handle tool.

Finally, we will have the following formats for the sales values but they will be stored as text here so no calculation can be done.

Read More: How to Format a Number in Thousands K and Millions M in Excel

Method-5: Using TEXT Function for Millions with One Decimal in Excel

In this section, we will use the TEXT function to change the formats of the sales values to an easily readable format indicating millions with one decimal place.
However, after the completion of formatting, we will not be able to calculate them as they will not remain as numbers anymore.

Steps:
➤ Type the following formula in cell E4

Here, D4 is the sales value, “0.0,,” is the formatting for millions, and finally & will add the Million indicator suffix M after a space.

➤ Press ENTER and drag down the Fill Handle tool.

In this way, you will have the following formats for millions with one decimal place.

Read More: How to Apply Number Format in Millions with Comma in Excel

Method-6: Using Conditional Formatting for Millions with One decimal

Ultimately, we are going to explain another way to change the formatting with the help of the Conditional Formatting feature of Excel.

Steps:
➤ Select the range and go to the Home Tab >> Styles Group >> Conditional Formatting Dropdown >> New Rule option.

After that, the New Formatting Rule wizard will pop up.
➤ Choose the option Format only cells that contain.
➤ Select and type the following in the three boxes of the Format only cells with the option and press the Format option.
First Box → Cell Value
Second Box → greater than or equal to
Third Box → 1000000

Afterward, you will get the Format Cells dialog box.
➤ Go to the Number Tab >> Custom Option >> type the following format in the Type box

➤ Press OK.

Finally, you will be taken to the New Formatting Rule dialog box and press OK here.

In this way, we will get the following formats for the large million numbers by shortening them and indicating them as million with suffix M and with a digit after the decimal point.

Read More: How to Add Number with Text in Excel Cell with a Custom Format

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to show the ways of custom number format for millions with one decimal place in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Apply Custom Format Cells in Excel
• How to Add Text after Number with Custom Format in Excel
  

<< Go Back to Custom Number Format | Number Format | Learn Excel