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

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.

Different Ways of Excel Number Format Millions


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

Different Ways of Excel Number Format Millions

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.

Different Ways of Excel Number Format Millions

  • Finally, we can see the result in column E.

Different Ways of Excel Number Format Millions

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)

Different Ways of Excel Number Format Millions

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.

Different Ways of Excel Number Format Millions

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

Different Ways of Excel Number Format Millions

  • 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:


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.

Different Ways of Excel Number Format Millions

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

Different Ways of Excel Number Format Millions

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

Excel Number Format Millions

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.

Excel Number Format Millions

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

Excel Number Format Millions

  • 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!


Related Articles

Sabrina Ayon

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 Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo