In Excel, number formatting is an amazing feature. Sometimes we have large numbers which may be difficult to read. We can improve the readability of our dataset by using excel number formatting. In this article, we will learn how excel number format millions work to simplify our dataset.

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

**6 Different Ways to ****Format ****Number to Millions in Excel**

It’s hard to evaluate a number in the millions. There are various simple methods to format numbers in excel. Suppose, we want to start a business. For this, we are using the dataset which contains some **Product ID** in column **B**, the total number of products in column **C**, and the budget of all the products in column **E**. Now to make it easier for others who are involved with the business, we want to format the budget column into number format millions in column **E**.

**1. Format Numbers to Millions Using Simple Formula**

To format the budget to number format millions, we can use a simple formula by following the simple steps below.

**STEPS:**

- First, select the cell where we want to change the format in normal numbers to numbers in million. Cell
**D5**contains the original number. And we want to see the formatted number in cell**E5**. - Second, to get the number in million units, we can use the formula.

`=D5/1000000`

Simply divide the number by **1000000**, as we know that million is equal to **1000000**. So if we divide the number by **1000000**, it makes the number shorter.

- Now, drag the
**Fill Handle**over the cell where we want to show the shorter numbers.

- Finally, we can see the result in column
**E**.

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

**2. Insert Excel ROUND Function to Format Numbers to Millions**

To make the decimal point shorter we can use the **ROUND** function. It will round up the large values and make them easier to read. Following the simple steps can round up the values we want to make shorter.

**STEPS:**

- In the beginning, select the cell where we want to round up the numbers. We select cell
**E5**. - Next, we need to type the formula.

`=ROUND(D5/10^6,1)`

As we take the value from **D5**, the million is equal to **10^6**. So, we divide the cell by **10^6**.

- After that, drag the
**Fill Handle**down.

- Now, we can see the formatted number as we wanted.

**Read More:** **Excel Round to Nearest 100 (6 Quickest Ways)**

**3. Paste Special Feature to Format Number to Millions**

**Paste Special **feature is another way to divide the number by a million but in a different way. For this, we need to follow the steps down.

**STEPS:**

- First of all, we need to put the million value anywhere in our workbook. We put it on cell
**F7**. - In the second place, we need to copy the cell
**F7**, (we put the million value**1000000)**by pressing**Ctrl + C**.

- Next, select the cells where we want to use the paste special feature. So, we select cell
**E5:E10**. - Furthermore, right-click on the mouse and click on
**Paste Special**.

**Paste Special**dialog box will appear. Now, choose the**Divide**operation.- And, click on the
**OK**button.

- Finally, we can see the column
**E**values are now becoming shorter and easy to understand. This will overwrite the large numbers with values divided by**1000000**.

**Related Content:** **How to Format Number with VBA in Excel (3 Methods)**

**Similar Readings:**

**How to Add Leading Zeros in Excel (4 Quick Methods)****Excel Round to 2 Decimal Places (with Calculator)****How to Round to Nearest 5 in Excel (3 Quick Ways)****Round Off Numbers in Excel (4 Easy Ways)****How to Round up Decimals in Excel (4 Simple Ways)**

**4. Using TEXT Function for Excel Number Format into Millions**

To format numbers into millions we can use the **TEXT **function to make it much easier to understand by putting an **“M” **at the end of the number. Let’s take a look at the steps below.

**STEPS:**

- To begin with, select the cell where we want to change the format. So, we select cell
**E5**. - Now, we write the formula below.

`=TEXT(D5,"#,##0,,")&"M"`

As we take the value from **D5**, we write **D5 **in the formula.

- Likewise the above methods, again drag the
**Fill Handle**down.

- The above formula takes the number from cell range
**D5:D10**and returns the text value in millions in cell range**E5:E10**.

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

**5. Format Number to Millions with Format Cell Feature**

We can customize the number format in excel. For this, there is a feature called** Format Cells**. Now we’ll see how we can use this feature.

**STEPS:**

- At first, choose the cells to which we wish to apply custom formatting.
- Now, right-click on the mouse and select
**Format Cells**. This will open the**Format Cells**dialog box.

- In the format cell menu, from the number tab, go to
**Custom**. In type field, type**#,##0,,”M”**. Then,**OK**.

- Now, we will notice that the large numbers are now formatted as millions in column
**E**.

**Read More:** **How to Apply Number Format in Millions with Comma in Excel (5 Ways)**

**6. Applying Conditional Formatting for Number Format**

We can create a conditional formatting rule to formate numbers according to the values.

**STEPS:**

- First, select the range of cells that we want to format.
- Next, go to the
**Home**tab in the ribbon. - After that, click on
**Conditional Formatting**. - From the drop-down menu select the
**New Rules**option.

- A
**New Formatting Rule**window will appear. Now, select**Format only cells that contain**, which is in the**Select a Rule Type**list. - Select greater than or equal to and type
**1000000**under the**Format only cells with**preferences. - Then, click on
**Format**.

- Now, again the format cells window will open up. So, go to
**Custom**> type**#,##0,,”M”**. Then**OK**.

- Finally, click on the
**OK**button in the**New Formatting Rule**dialog box.

- And, that’s it. We can now see the results in column
**E**.

**Read More:** **Excel Custom Number Format Multiple Conditions**

**Millions to Normal Long Number Format in Excel**

Sometimes, we may want to do the opposite. We can convert millions to long numbers by using the formula below which is the combination of **IF**, **ISTEXT**, **LOOKUP**, **RIGHT**, **LEFT **& **LEN **functions. Suppose, we have a value in cell **A2 **which is **48M**. Now we want to convert it into a normal number format in cell **C2**.

`=IF(ISTEXT(A2),10^(LOOKUP(RIGHT(A2),{"M"}, {6}))*LEFT(A2,LEN(A2)-1),A2)`

As a result, the formatted value will show in cell **C2**, which is **48000000**.

**Conclusion**

Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!