Table of Contents
To round numbers to millions in Excel, follow these steps:
1. Select the cell(s) containing the numbers you want to round.
2. Right-click and click on “Format Cells” in the drop-down menu.
3. In the “Format Cells” dialog box, click on the “Number” tab.
4. Under “Category,” select “Custom.”
5. In the “Type” field, enter the following format: #,##0,,”M”
6. Click “OK” to save the format.
7. The numbers will now be rounded to the nearest million in the selected cell(s).
Round Numbers to Millions in Excel (With Examples)
You can use the following formulas to round numbers to millions in Excel:
Formula 1: Round Number to Nearest Million
=ROUND(B2,-6)
This particular formula rounds the number in cell B2 to the nearest million.
For example, if cell B2 contained 1,453,000 then this formula would return 1,000,000.
Formula 2: Round Number to Nearest Million with One Decimal Place
=ROUND((B2/1000000),1)&"M"
This particular formula rounds the number in cell B2 to the nearest million with one decimal place.
For example, if cell B2 contained 1,453,000 then this formula would return 1.5M.
Formula 3: Round Number to Nearest Million with One Decimal Place and Dollar Sign
="$"&ROUND((B2/1000000),1)&"M"
This particular formula rounds the number in cell B2 to the nearest million with one decimal place.
For example, if cell B2 contained 1,453,000 then this formula would return $1.5M.
The following example shows how to use each of these formulas in practice.
Example: Round Numbers to Millions in Excel
Suppose we have the following dataset in Excel that shows the annual salary of various athletes:
- C2: =ROUND(B2,-6)
- D2: =ROUND((B2/1000000),1)&”M”
- E2: =”$”&ROUND((B2/1000000),1)&”M”
We can then click and drag these formulas down to each remaining cell in each column:
Columns C, D and E now display the numbers in column B rounded to the nearest million in various formats.
Note that you can also round to a different number of decimal places by changing the value of the last argument in the ROUND function.
For example, you could use the following formula to round each number to the nearest million with two decimal places instead:
=ROUND((B2/1000000),2)&"M"
For example, if cell B2 contained 1,453,000 then this formula would return 1.45M.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: