# How to Format Number to Millions in Excel (6 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 with millions works to simplify our dataset.

## How to Format Number to Millions in Excel: 6 Easy Ways

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 a 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 from normal numbers to numbers in millions. 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 a 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. ### 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. ### 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 that column E values are now becoming shorter and easier to understand. This will overwrite the large numbers with values divided by 1000000. ### 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. ### 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:

• 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 the Type field, type #,##0,,”M”. Then, OK. • Now, we will notice that the large numbers are now formatted as millions in column E. ### 6. Applying Conditional Formatting for Number Format

We can create a conditional formatting rule to format 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. ## How to Convert 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  