The ROUND function in Excel
ROUND changes a number to a set number of digits, such as cents, whole units or the nearest thousand. Unlike number formatting, which only changes what you see, ROUND changes the stored value, so totals and comparisons use the rounded figure. Use it for prices, invoices, payroll and any report that has to tie out to the penny.
Syntax
=ROUND(number, num_digits)
| number | The number or calculation to round, like B2 or B2*C2. |
|---|---|
| num_digits | How many decimal places to keep. 2 rounds to cents, 0 to a whole number, and negative values round to the left of the decimal point: -3 gives the nearest thousand. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In D2, calculate the line total (Qty × Unit price) and use ROUND to round it to 2 decimal places.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line total | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: Put the multiplication inside ROUND as its first argument.
Examples
=ROUND(B2,2)→ 12.35Rounds the unit cost to two decimals, the format you'd use for money.
=ROUND(B3,0)→ 8Rounds to a whole number. Halves round away from zero, so .5 goes up.
=ROUND(B4,-3)→ 19000A negative digit count rounds to the left of the decimal point, here to the nearest thousand.
Example data
| 1 | Measure | Value |
|---|---|---|
| 2 | Unit cost | 12.3456 |
| 3 | Hours logged | 7.5 |
| 4 | Annual budget | 18740 |
Common mistakes
Formatting instead of rounding
Showing two decimals with cell formatting hides the extra digits, but they're still used in totals, so sums can be off by a cent. Use ROUND when the stored value matters.
Rounding too early
Rounding each step of a long calculation adds up small errors. Round once, at the final result, unless a rule requires rounding at each line (like per-item tax).
Using ROUND when you need to always go up
ROUND goes to the nearest value. If you must always round up, such as boxes needed for an order, use ROUNDUP. For always down, use ROUNDDOWN.
FAQ
How do I round to 2 decimal places in Excel?+
Use =ROUND(A2,2). For a calculation, wrap the whole thing: =ROUND(B2*C2,2).
How do I round to the nearest thousand or hundred?+
Use a negative second argument: -2 for the nearest hundred, -3 for the nearest thousand.
What is the difference between ROUND, ROUNDUP and ROUNDDOWN?+
ROUND goes to the nearest value, ROUNDUP always moves away from zero, and ROUNDDOWN always moves toward zero. All three take the same two arguments.
Lesson
Master ROUND step by step: ROUND, ROUNDUP and ROUNDDOWN
Control decimals properly, which isn't the same as formatting.