Sabrina Ayon
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.
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!
