ROUND Formula Generator
Round a number to a set number of decimal places, or left of the decimal to tens, hundreds, and thousands.
ROUND
Round a number to a set number of decimal places, or left of the decimal to tens, hundreds, and thousands.
The value to round. Can be a cell like A2, a number, or a calculation such as A2*1.0825.
2 keeps cents, 0 gives whole numbers, and negatives round left of the decimal point (-3 is the nearest thousand). Excel requires this argument, so leave a value here.
=ROUND(A2, 2)Worked Example
A supplier quote of 18,432 needs to be rounded to the nearest thousand for a summary slide.
=ROUND(18432, -3)Returns: Returns 18000. The negative num_digits rounds left of the decimal point, and -3 means the nearest thousand.
Checks Before You Paste
- •num_digits is a position, not a count of significant figures: 2 rounds to cents, 0 to whole numbers, and negative values round left of the decimal point, so -1 is the nearest ten, -2 the nearest hundred, and -3 the nearest thousand.
- •ROUND breaks ties away from zero, so ROUND(2.5, 0) is 3 and ROUND(-2.5, 0) is -3. ROUNDUP always moves away from zero, ROUNDDOWN always moves toward it, and MROUND(number, multiple) snaps to a step like 0.05 or 25 instead of a power of ten.
- •Changing a cell's number format only changes what you see: the stored value keeps every decimal, so a column that displays 10.00 + 10.00 can total 20.01. ROUND changes the value itself, which is why totals should be built from rounded cells rather than rounded displays.
How ROUND works
Availability: Every version of Excel, Excel for the web, Google Sheets and LibreOffice Calc. The related CEILING.MATH and FLOOR.MATH need Excel 2013 or later; MROUND, ROUNDUP and ROUNDDOWN are available everywhere.
ROUND changes the stored value of a number, keeping the digits you asked for and discarding the rest. It rounds half away from zero: 2.5 becomes 3 and -2.5 becomes -3. That is not the banker's rounding (half to even) used by many programming languages and by some statistical software, so a column rounded in Excel will not always match the same column rounded in Python or SQL.
The second argument is a position, not a count of significant figures. Two rounds to the nearest hundredth, zero to the nearest whole number, and negative values move left of the decimal point: -1 is the nearest ten, -2 the nearest hundred, -3 the nearest thousand. Excel insists on the argument even when it is zero, unlike Google Sheets, where it defaults to 0.
ROUND is the tool when the value itself must change - invoice lines that have to reconcile to the penny, quantities that will be multiplied downstream, figures being exported to a system with fewer decimals. It is the wrong tool when you only want a tidier display, because number formatting does that without touching the data. The distinction matters most in totals: a column of values like 10.004 displayed as 10.00 still sums with all its hidden decimals, so the total can be a cent away from the sum of what a reader can see.
For anything other than rounding to a power of ten, ROUND has siblings. ROUNDUP always moves away from zero and ROUNDDOWN always moves toward it, regardless of the digit that follows. MROUND snaps to an arbitrary step such as 0.05 or 25. CEILING.MATH and FLOOR.MATH do the same with explicit control over how negative numbers behave, and INT and TRUNC drop the fractional part outright - INT toward negative infinity, TRUNC toward zero, which is the same thing for positive numbers and different for negative ones.
Syntax
=ROUND(number, num_digits)| Argument | Required | What it does |
|---|---|---|
number | Required | The value to round. A number typed in, a cell reference, or a calculation such as A2*0.2. Text that looks like a number gives #VALUE!. |
num_digits | Required | Which position to round to. Positive counts decimal places (2 = hundredths), 0 rounds to whole numbers, and negative counts left of the decimal point (-2 = nearest hundred). Excel rejects the formula if this argument is missing. |
More worked examples
Calculating 20% VAT on a net price of 19.99 in A2, where the result has to be a real currency amount.
=ROUND(A2*0.2, 2)Returns: Returns 4, because 19.99 * 0.2 is 3.998 and rounding to two decimals gives 4.00. Currency formatting displays it as 4.00.
Without ROUND, the cell stores 3.998 and an invoice total built from several such lines drifts by a penny or two.
Presenting a headcount forecast of 2,749 to the nearest hundred.
=ROUND(2749, -2)Returns: Returns 2700. The 49 in the tens and units positions is below half a hundred, so the value rounds down.
Pricing that has to land on a five-cent step. A2 holds 7.13.
=MROUND(A2, 0.05)Returns: Returns 7.15. By contrast =ROUND(A2, 2) leaves 7.13 unchanged, because 7.13 is already exact to two decimals.
MROUND rounds half away from zero as well, so 7.125 with a step of 0.05 returns 7.15.
Common mistakes
- Formatting a cell is not rounding it
- Setting a column to two decimal places changes only what is drawn on screen; the stored value keeps every digit. Two cells holding 10.004 display as 10.00 each and total 20.01. If the printed figures have to add up, apply ROUND to the values, or switch on Precision as displayed for the whole workbook - which is destructive and hard to undo.
- Assuming banker's rounding
- Excel's ROUND breaks every tie away from zero, so a data set full of exact halves rounds high on average. Statistical tools that use half-to-even give a different total on the same data. If a reconciliation has to match another system, confirm which rule that system uses before treating Excel as wrong.
- Rounding at every intermediate step
- Rounding a unit price, then rounding the line total, then rounding the invoice total compounds the error and produces totals that do not match a single unrounded calculation. Keep full precision through the calculation and round once, at the point where the number is presented or committed.
- Using ROUND where you needed ROUNDUP
- =ROUND(7/3, 0) returns 2, but three boxes are needed to ship seven items. Anything that represents a count of whole units - boxes, licences, staff, trips - wants ROUNDUP or CEILING.MATH, which move away from zero no matter what the fraction is.
- Comparisons failing on stored binary values
- =(0.1+0.2)=0.3 returns FALSE, because binary floating point cannot store those values exactly. Excel hides this when displaying but not when comparing. Compare rounded values instead: =ROUND(0.1+0.2, 10)=ROUND(0.3, 10) returns TRUE.
Frequently Asked Questions
No. ROUND always breaks ties away from zero, so 0.5 goes to 1 and -0.5 goes to -1. Excel has no built-in half-to-even function, so matching a system that uses it takes a longer formula built around ISEVEN and a test for the exact-half case.
Positions to the left of the decimal point. -1 gives the nearest ten, -2 the nearest hundred, -3 the nearest thousand, and so on. =ROUND(18432, -3) returns 18000. The result is still a full number, not an abbreviated one, so 18000 is stored rather than 18k.
Because rounding each row and rounding the total are two different calculations, and both are correct. Twelve rows each rounded up by 0.004 shift the sum by nearly half a unit. Pick which figure has to be authoritative: round the rows if the detail must reconcile, or round only the total if the summary must be exact and note that the parts are approximate.
Use MROUND(number, multiple), which snaps to any step: =MROUND(A2, 25) gives the nearest 25. CEILING.MATH and FLOOR.MATH do the same in one direction only. Note that MROUND returns #NUM! when the number and the multiple have opposite signs.
No. A value imported as text, complete with a leading apostrophe or a non-breaking space, returns #VALUE! from ROUND. Convert the column to real numbers first; VALUE, a paste-special multiply by 1, or Text to Columns all work.
Related Tools
Convert Text to Number
Fix imported columns that ROUND rejects because the values are stored as text.
Excel Formula Generator
Describe a calculation in words and get a formula, rounding included.
Excel Cheat Sheet
The other maths functions in one place, including MROUND, INT and TRUNC.
Excel Formula Explainer
Check where in a long calculation the rounding is being applied.