=ROUND(A2,2) rounds the number in A2 to two decimal places, so 3.14159 becomes 3.14. The second argument is the number of digits to keep: 0 rounds to a whole number and -1 rounds to the nearest ten.
| A | B | C | |
|---|---|---|---|
| 1 | Value | 2 decimals | Whole number |
| 2 | 3.14159 | 3.14 | 3 |
| 3 | 2.675 | 2.68 | 3 |
| 4 | 12.5 | 12.5 | 13 |
| 5 | -2.5 | -2.5 | -3 |
| 6 | 1234.567 | 1234.57 | 1235 |
Each row runs both formulas on the value in column A, so 1234.567 becomes 1234.57 and 1235. Change A2 to another number and both columns follow.
ROUND syntax
=ROUND(number, num_digits)
numberis the value to round: a number, a cell, or a whole formula such as=ROUND(B2*C2,2).num_digitssays where to round. A positive number counts places after the decimal point,0rounds to a whole number, and a negative number counts places before the decimal point.
ROUND looks only at the first digit it drops. If that digit is 5 or more, the kept part goes up; otherwise it stays. So =ROUND(2.675,2) gives 2.68 and =ROUND(2.674,2) gives 2.67.
Round to the nearest 10, 100 or 1000
A negative num_digits rounds to the left of the decimal point. This is how you turn 1234.567 into 1230, 1200 or 1000 for a report.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | num_digits | Result | Number | |
| 2 | 2 | 1234.57 | 1234.567 | |
| 3 | 1 | 1234.6 | ||
| 4 | 0 | 1235 | ||
| 5 | -1 | 1230 | ||
| 6 | -2 | 1200 | ||
| 7 | -3 | 1000 |
Click B5: it is =ROUND($D$2,-1), the nearest 10. Now change D2 to 48750. The row for -2 shows 48800, because a 5 in the first dropped position always rounds up.
ROUND vs number format
Formatting a cell as 0.00 (Home > Number > Decrease Decimal) changes only what the cell shows. The full value is still there, and every formula that reads the cell uses it. ROUND changes the value itself.
That difference shows up in totals. Below, each price in column A is 1.333 shown with two decimals, so the column reads 1.33, 1.33, 1.33, but its total shows 4.00. Column B rounds each price first, and its total is 3.99.
| A | B | |
|---|---|---|
| 1 | Formatted only | Rounded |
| 2 | 1.33 | 1.33 |
| 3 | 1.33 | 1.33 |
| 4 | 1.33 | 1.33 |
| 5 | 4.00 | 3.99 |
Neither total is wrong: 4.00 is the true sum, 3.999, shown with two decimals, and 3.99 is the sum of what an invoice prints. Use ROUND when the rounded numbers are the real ones, such as money charged per line. To show fewer decimals without changing anything, use the number format. File > Options > Advanced > "Set precision as displayed" makes Excel store every displayed value, for the whole workbook, and cannot be undone; ROUND is the safer choice.
How Excel rounds 0.5
ROUND rounds a 5 away from zero: 2.5 becomes 3 and -2.5 becomes -3. Look at rows 3 to 5 of the first sheet: 2.675 gives 2.68, 12.5 gives 13 and -2.5 gives -3.
Some tools round a 5 to the nearest even number instead (banker's rounding), so 2.5 becomes 2. Excel's ROUND function never does that, but VBA's Round function does, which explains a macro and a cell disagreeing. Google Sheets' ROUND also rounds away from zero.
Round a formula result
Wrap the whole calculation in ROUND, so only the final result is rounded. Here the price with 8.25% tax is rounded to cents.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Tax rate | With tax |
| 2 | Lamp | $19.99 | 8.25% | $21.64 |
| 3 | Chair | $74.50 | 8.25% | $80.65 |
| 4 | Desk | $129.00 | 8.25% | $139.64 |
The same pattern works for averages, percentages and divisions: =ROUND(AVERAGE(B2:B6),1) gives an average with one decimal. If you want the rounded number as text in a sentence, =TEXT(A2,"0.00") does both at once (see the TEXT function).
Round to the nearest 5, 0.05 or 15 minutes with MROUND
=MROUND(number, multiple) rounds to the nearest multiple of any number. Use it for prices in steps of 5 cents, quantities in packs of 12, or times to the quarter hour.
| A | B | C | |
|---|---|---|---|
| 1 | Value | Multiple | MROUND |
| 2 | 12 | 5 | 10 |
| 3 | 13 | 5 | 15 |
| 4 | 2.37 | 0.05 | 2.35 |
| 5 | 9:08 | 0:15 | 9:15 |
MROUND also rounds a halfway value away from zero, so 12.5 with a multiple of 5 gives 15. The number and the multiple must have the same sign: =MROUND(-12,5) returns #NUM!, while =MROUND(-12,-5) gives -10. To always round up or down to a multiple, use CEILING and FLOOR on the ROUNDUP and ROUNDDOWN page.
Try it: round an average
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Average | ||
| 2 | Ana | 78 | |||
| 3 | Ben | 91 | |||
| 4 | Cleo | 85 | |||
| 5 | Dan | 66 | |||
| 6 | Eve | 88 | |||
| 7 | Finn | 73 |
Your turn: In E2, show the average of the scores in B2:B7, rounded to one decimal place.
Hint: put AVERAGE inside ROUND, and use 1 as the number of digits.
Which rounding function to use
| You want | Formula | 12.345 gives |
|---|---|---|
| Nearest value, 2 decimals | =ROUND(A2,2) | 12.35 |
| Nearest whole number | =ROUND(A2,0) | 12 |
| Nearest 10 | =ROUND(A2,-1) | 10 |
| Always up | =ROUNDUP(A2,1) | 12.4 |
| Always down | =ROUNDDOWN(A2,1) | 12.3 |
| Nearest multiple of 0.5 | =MROUND(A2,0.5) | 12.5 |
| Drop the decimals | =TRUNC(A2) or =INT(A2) | 12 |
A common mistake is rounding in the middle of a long calculation and again at the end, which can move the last digit. Round once, at the step whose rounded value you actually report or charge.
Frequently Asked Questions
How do I round to 2 decimal places in Excel?
Use =ROUND(A2,2). It changes the stored value, so 3.14159 becomes 3.14 and later formulas use 3.14. Formatting the cell as 0.00 only changes what you see; the full value stays underneath.
How do I round to the nearest whole number in Excel?
Use =ROUND(A2,0): 12.5 becomes 13 and 12.4 becomes 12. To always go up or always go down, use =ROUNDUP(A2,0) or =ROUNDDOWN(A2,0).
How do I round to the nearest 10, 100 or 1000 in Excel?
Give ROUND a negative number of digits: =ROUND(A2,-1) rounds to the nearest 10, =ROUND(A2,-2) to the nearest 100 and =ROUND(A2,-3) to the nearest 1000. 1234.567 becomes 1230, 1200 and 1000.
Does Excel round 0.5 up or down?
ROUND rounds a 5 away from zero: =ROUND(2.5,0) is 3 and =ROUND(-2.5,0) is -3. It does not use banker's rounding (round half to even), which VBA's Round function does.
How do I round to the nearest 5 in Excel?
Use =MROUND(A2,5): 12 becomes 10 and 13 becomes 15. MROUND rounds to any multiple, such as =MROUND(A2,0.05) for prices in steps of 5 cents.
