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.

**Table of Contents**hide

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