Custom Number Format: Millions with One Decimal in Excel (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 know more about this number format.

Download Workbook


6 Ways for Custom Number Format Millions with One Decimal in Excel

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 as millions with one decimal. Moreover, we will use some functions also for this purpose.

Excel custom number format millions with one decimal

We have used Microsoft Excel 365 version here, you can use any other versions 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.

Excel custom number format millions with one decimal

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 also.

custom number format with zero

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

0.0,, "M"

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.

custom number format with zero

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.

Excel custom number format millions with one decimal

Read More: Excel Custom Number Format 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 here the Hashtag (#) symbol also.

Excel custom number format millions with one decimal

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

custom number format with hashtag

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

#.0,, "M"

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.

custom number format with hashtag

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

Excel custom number format millions with one decimal

Read More: How to Format Number to Millions in Excel (6 Ways)


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.

Excel custom number format millions with one decimal

Steps:
➤ Type the following formula in cell E4

=ROUND((D4/1000000),1)&" "&"M"

Here, D4 is the sales value.

  • D4/1000000 becomes 4307392/1000000
    Output → 4.307392
  • ROUND((D4/1000000),1) becomes
    ROUND(4.307392,1) → rounds a number up to one decimal place
    Output → 4.3
  • ROUND((D4/1000000),1)&” “&”M” becomes
    4.3 &” “&”M” → & will join the value with a space and the suffix M
    Output → 4.3 M

ROUND function

➤ Press ENTER and drag down the Fill Handle tool.

ROUND function

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.

Excel custom number format millions with one decimal

Read More: How to Round Off Numbers in Excel (4 Easy Ways)


Similar Readings:


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.

Excel custom number format millions with one decimal

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

paste special option

➤ Select the range of the Sales column and 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).

Excel custom number format millions with one decimal

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

paste special option

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.

paste special option

➤ Write down the following formula in cell E4

=ROUND(D4,1)&" "&"M"

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.

paste special option

➤ Press ENTER and drag down the Fill Handle tool.

paste special option

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.

Excel custom number format millions with one decimal

Read More: How to Format a Number in Thousands K and Millions M in Excel (4 Ways)


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

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

Excel custom number format millions with one decimal

Steps:
➤ Type the following formula in cell E4

=TEXT(D4,"0.0,,")&" "&"M"

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.

TEXT function

➤ Press ENTER and drag down the Fill Handle tool.

TEXT function

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

Excel custom number format millions with one decimal


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.

Excel custom number format millions with one decimal

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

Conditional Formatting

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 option and press the Format option.
First Box → Cell Value
Second Box → greater than or equal to
Third Box → 1000000

Excel custom number format millions with one decimal

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

0.0,, "M"

➤ Press OK.

Conditional Formatting

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

Conditional Formatting

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.

Excel custom number format millions with one decimal

Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)


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.

Practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo