Tanjima Hossain
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.
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.
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.
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.
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: 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.
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
#.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.
Finally, we will get sales values in our desired format millions with one decimal place and these values will be stored as numbers.
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.
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
➤ 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 Round Off Numbers in Excel (4 Easy Ways)
Similar Readings
- Excel round to nearest 10000 (5 Easiest Ways)
- How to Round up Decimals in Excel (4 Simple Ways)
- [Fixed!] Excel Not Recognizing Numbers in Cells (3 Techniques)
- Excel Number Stored As Text [4 Fixes]
- Add or Keep Leading Zeros in Excel (10 Suitable Ways)
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 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).
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
=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.
➤ 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 (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.
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.
➤ Press ENTER and drag down the Fill Handle tool.
In this way, you will have the following formats for millions with one decimal place.
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 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
0.0,, "M"➤ 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 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.
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.
