The Excel ROUND function, explained interactively.
ROUND rounds a number to a specified number of digits using “half away from zero” — 0.5 becomes 1, -0.5 becomes -1. Positive num_digits rounds decimals, zero rounds to integers, and — the surprise — negative rounds to the nearest ten, hundred, or thousand.
How to use ROUND
- Type
=ROUND(and select the number — a cell reference (A2) or any numeric expression. - Add a comma and num_digits — how many digits to keep. Positive for decimals, zero for integers, negative for nearest ten / hundred / thousand.
- Close the parenthesis. ROUND returns the rounded number ready for further formulas or display.
- For currency, use
2. For whole dollars,0. For “nearest $100”,-2.
Change num_digits in the demo — note how -1 rounds 154.6 down to 150 and 12345.678 down to 12350.
0.5 rounds up to 1, -0.5 rounds to -1.A2) but any numeric expression works — ROUND(B2/C2, 2), ROUND(SUM(x:y), 0).| A | B | |
|---|---|---|
| 1 | Number | Rounded |
| 2 | 3.14159 | 3.14 |
| 3 | 12.75 | 12.75 |
| 4 | 0.125 | 0.13 |
| 5 | 99.49 | 99.49 |
| 6 | 154.6 | 154.6 |
| 7 | -7.85 | -7.85 |
| 8 | 12345.678 | 12345.68 |
=ROUND(A2, num_digits). num_digits = 2: rounded to 2 decimal places. The other B cells show what B3:B8 would return if you filled the formula down.ROUND syntax and arguments
Two positional arguments — a number and a digit count. See Microsoft’s official ROUND reference for the canonical specification.
"3.14") are coerced automatically; true text returns #VALUE!.Positive → decimal places:
ROUND(3.14159, 2) = 3.14.Zero → nearest integer:
ROUND(99.49, 0) = 99.Negative → left of the decimal:
ROUND(154.6, -1) = 150, ROUND(12345, -2) = 12300.ROUND examples
Four patterns that cover almost every ROUND you’ll ever write.
Example 1: ROUND for currency
The canonical case — force a price to two decimal places so downstream sums don’t accumulate floating-point drift.
3.14159 → 3.14. Rounding at source is safer than formatting — formatting only changes display, so =SUM(...) on unrounded values accumulates tiny errors.
Example 2: ROUND to the nearest ten / hundred
Pass a negative num_digits to round left of the decimal — the feature most users don’t know exists.
12345.678 → 12300. Great for executive summaries where decimal precision is noise. Pair with formatting to show “$12,300” in reports.
Example 3: ROUND inside another formula
Rounding an intermediate calculation rather than the input keeps the stored value consistent with what the user sees.
Unit price from a total divided by quantity, rounded to cents. Every subsequent aggregate adds exact cents, not floating-point approximations.
Example 4: ROUND combined with IFERROR
Guard against divide-by-zero before rounding.
IFERROR catches the #DIV/0! when C2 is zero, substituting 0. Rounding the successful division keeps downstream precision clean.
Example 5: ROUND inside SUM vs ROUND on the total
Financial reports often demand that column totals match the penny. Applying ROUND at the wrong step creates the classic one-cent discrepancy.
The first rounds each value, then sums — every row is penny-exact. The second sums first, then rounds the total — the stored precision of the source values affects the result. Pick deliberately: for invoice line items, round per-row so each line reads cleanly; for an aggregate display, round the total so the visible figure matches the computed one. Mixing both strategies is the fastest route to a one-cent “bug” that isn’t a bug at all.
Common ROUND errors and fixes
Four failure modes, each with what to check and how to recover.
ROUND returns #VALUE!
Cause: the number argument isn’t numeric — it’s a text value or a range instead of a single cell.
Point number at a single cell or an expression that evaluates to a scalar. Use VALUE() to force text-number coercion if needed.
ROUND totals don’t match the cell display
Cause: you formatted cells to 2 decimals but the stored values are full precision. A SUM reads the stored precision, not the displayed rounded values. Two lines that each show $1.01 can total to $2.03 because the real values were1.005 and 1.025.
Replace the format-only step with actual ROUND on the source, so stored values match what’s displayed. Formatting is a UI hint; ROUND changes the data. There’s also a Set Precision As Displayed workbook option, but it’s a blunt instrument that rewrites every value on load — prefer explicit ROUND in the cells that need it.
ROUND appears to round wrong at .5
Cause: you expected banker’s rounding (round-half-to-even). ROUND always rounds half away from zero.
If you need banker’s rounding, use =IF(MOD(A2*100, 2)=1, MROUND(A2, 0.02), ROUND(A2, 2)) or a custom LAMBDA. Or accept ROUND’s behaviour; it’s standard for most financial contexts.
Negative zero (-0) appearing after ROUND
Cause: rounding a very small negative number to a digit count that zeros it out — Excel preserves the sign.
Wrap in ABS if sign-on-zero matters: =ROUND(A2, 2) * 1 also cleans it up. Cosmetic, but confusing in reports.
ROUND vs ROUNDUP, ROUNDDOWN, MROUND & INT
Five ways to reduce precision. Pick by which direction half should go — and whether rounding is to digits or to a multiple.
| Function | Direction | Unit | Typical use |
|---|---|---|---|
| ROUND | Nearest (half away from zero) | Digit places | General currency, summaries |
| ROUNDUP | Always away from zero | Digit places | Price ceilings, pessimistic estimates |
| ROUNDDOWN | Always toward zero | Digit places | Age from days, truncating decimals |
| MROUND | Nearest multiple | Arbitrary multiple | Round to nearest 5 minutes, 0.25, etc. |
| INT | Toward negative infinity | Integer only | Quick floor, but misbehaves on negatives |
Rule of thumb: ROUND for general purpose, ROUNDUP/ROUNDDOWN when the direction must be guaranteed (pricing, age),MROUND when the unit is a multiple rather than a digit count (nearest 0.05, nearest 15 minutes). Use INT only for non-negative integers — INT(-2.5) returns -3, which surprises most people.
ROUND frequently asked questions
6.01Does ROUND use banker’s rounding?▸
No. ROUND uses “half away from zero” — 0.5 rounds to 1, 1.5 rounds to 2, -0.5 rounds to -1. If you want banker’s rounding (half to even), you need a custom formula or a helper column.
6.02How do I round to the nearest 10 or 100?▸
Pass a negative num_digits: =ROUND(A2, -1) rounds to the nearest ten, =ROUND(A2, -2) to the nearest hundred, and so on. This is ROUND’s least-known feature — most users never realise negative digits are valid.
6.03What’s the difference between ROUND and formatting the cell?▸
Cell formatting changes only the display; the underlying value stays full-precision. ROUND changes the stored value itself. This matters when downstream formulas aggregate — ROUND gives consistent arithmetic, formatting doesn’t.
6.04When should I use ROUNDUP or ROUNDDOWN instead of ROUND?▸
ROUNDUP always rounds away from zero (2.1 → 3); ROUNDDOWN always rounds toward zero (2.9 → 2). Use ROUNDUP for price ceilings, ROUNDDOWN for age calculations or truncating decimals. Use ROUND (nearest) for general-purpose reporting.
6.05Why does ROUND return #VALUE! and how do I fix it?▸
ROUND returns #VALUE! when the number argument isn’t numeric — typically a cell containing text like "N/A" or an empty string. Wrap with IFERROR (=IFERROR(ROUND(A2, 2), 0)) or guard withISNUMBER. Text numbers like "3.14" are coerced automatically, so they don’t trigger the error.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.