ROUND in Excel: ROUND, ROUNDUP, ROUNDDOWN and Formatting
How ROUND, ROUNDUP and ROUNDDOWN work in Excel, rounding to tens or hundreds, MROUND for nearest 5, and why formatting decimals isn't the same as rounding.
By Ayoub Farid · · 1 min read
There are two ways to "round" in Excel, and mixing them up causes totals that are off by a cent. Formatting changes how a number looks. ROUND changes the value.
Round to any number of decimals with ROUND
=ROUND(number, num_digits)| Formula | Result |
|---|---|
=ROUND(3.14159,2) | 3.14 |
=ROUND(2.5,0) | 3 |
=ROUND(1234,-1) | 1230 |
=ROUND(1234,-2) | 1200 |
Negative digits round to tens, hundreds, thousands.
Always round up or down: ROUNDUP and ROUNDDOWN
=ROUNDUP(2.01,0) → 3
=ROUNDDOWN(2.99,0) → 2Use ROUNDUP for things you can't have part of: boxes needed, staff shifts, seats.
Nearest 5, 0.25, or 15 minutes
=MROUND(B2,5)
=CEILING(B2,0.25)
=FLOOR(B2,0.25)MROUND goes to the nearest multiple; CEILING up; FLOOR down.
Formatting vs rounding: why totals look wrong
Three items at 0.333 formatted to 2 decimals show 0.33 each, but their total shows 1.00 (because the real total is 0.999). If the displayed numbers must add up (invoices, reports), round the values:
=ROUND(B2*C2,2)Rounding a calculation
Round the result, not each input:
=ROUND(AVERAGE(B2:B13),0)
=ROUND(B2/$B$14,3)Drop the decimals: INT and TRUNC
=INT(-2.5)→ -3 (rounds down to the integer below)=TRUNC(-2.5)→ -2 (just cuts the decimals)
Frequently asked questions
How do I round to 2 decimal places in Excel?
=ROUND(A2,2).
How do I round to the nearest 100?
=ROUND(A2,-2).
Why doesn't my total match the rounded numbers?
The cells are formatted to show fewer decimals but still hold the full values. Use ROUND in the formulas.
How do I always round up?
=ROUNDUP(A2,0) for whole numbers, or CEILING for a specific multiple.