Excel ROUND Function — Complete Guide (2026)

Excel ROUND Function — Complete Guide (2026)

Short answer: the Excel rounding function you're looking for is probably =ROUND(number, num_digits). That's the standard one. But Excel actually ships nine related functions, and picking the wrong one can flip a positive number negative, truncate when you wanted to round, or send a budget total $1.27 off because formatting hid the real value. This guide walks every Excel rounding function — what each one does, when to use it, and what trips people up.

Here's the thing about Excel rounding: there's a hard line between changing what a cell stores and changing what a cell displays. Number formatting (the comma button, the decimal increase/decrease buttons) only changes the display. =ROUND() changes the stored value. Most accounting bugs trace back to confusing the two. We'll cover that in detail later.

If you're studying for an Excel certification, the rounding family appears on nearly every exam. The excel certification covers ROUND, ROUNDUP, ROUNDDOWN, and at least one of CEILING or FLOOR. Pair this guide with the countifs excel reference for two of the most-tested formula families, and brush up on excel pivot tables if data summarization is on your exam.

ROUND — The Standard Rounding Function

Syntax: =ROUND(number, num_digits). The first argument is the value (or cell reference). The second is how many decimal places to keep. Positive num_digits rounds to that many decimals to the right of the decimal point. Zero rounds to a whole number. Negative num_digits rounds digits to the left — that's the surprise feature most people miss.

Examples that show the full range:

• =ROUND(3.14159, 2) → 3.14 (two decimals)
• =ROUND(3.14159, 0) → 3 (nearest whole)
• =ROUND(127, -1) → 130 (nearest 10)
• =ROUND(127, -2) → 100 (nearest 100)
• =ROUND(1284, -3) → 1000 (nearest 1,000)

Excel uses standard arithmetic rounding (sometimes called "round half away from zero"). That means 0.5 rounds up to 1, and -0.5 rounds down to -1. This is different from banker's rounding, which most statistical software uses — and Excel does not natively support banker's rounding. We'll cover that workaround later.

A practical tip: when you wrap a =ROUND() around a financial calculation, do it once at the end, not inside every sub-step. Compounding small rounding errors is a real source of penny discrepancies in spreadsheets. If you're computing tax, round the tax once at the end: =ROUND(A1*0.0725, 2) — not =ROUND(A1,2)*0.0725, which rounds the input first and then multiplies.

Why does this matter so much in real workbooks? Because every spreadsheet eventually gets exported, shared, or audited. The moment your file leaves Excel — as a CSV, a PDF, a Power BI dataset, a payroll feed — the displayed format strips away. Only the stored value travels. If you've been relying on number formatting to clean up the appearance, the consumer of your data sees the messy raw numbers. ROUND fixes that at the source.

One more habit worth building: name your rounding precision. Don't sprinkle 2 throughout dozens of formulas — define =ROUND(value, $G$1) where G1 holds a single rule like "2" for currency. When the precision rule changes (say, your finance team adopts 4-decimal exchange rates), you update one cell instead of hunting through 200 formulas. Small habit, big payoff six months later when the requirements shift.

And know your limits. Excel stores numbers as IEEE 754 double-precision floats, which gives you about 15 significant digits. Beyond that, =ROUND(A1, 14) may not behave exactly as you expect because the underlying bits don't have room for more precision. For most business use (currency, percentages, scores), this never comes up. For scientific or engineering work with very large or very small numbers, switch to a tool with arbitrary-precision math.

ROUNDUP and ROUNDDOWN — Force the Direction

Use these when arithmetic rounding doesn't fit your business rule. A shipping calculator that bills by the pound? Always round up — =ROUNDUP(weight, 0). A discount applied to a unit count? Often round down so the customer never overcharges — =ROUNDDOWN(units, 0).

Syntax is identical to ROUND: =ROUNDUP(number, num_digits) and =ROUNDDOWN(number, num_digits). The same negative-digits trick works — =ROUNDUP(A1, -2) rounds up to the next hundred.

Watch the negative number behavior. ROUNDUP always rounds away from zero, which means it rounds down in magnitude direction for negatives. =ROUNDUP(-2.1, 0) returns -3, not -2. If that's not the behavior you want, use CEILING.MATH with a third argument to control negative handling, or use INT for predictable always-down behavior.

Where I see ROUNDDOWN trip people most: payroll. A timecard shows 7.46 hours worked. The company policy: pay in whole hours, no partial credit. =ROUNDDOWN(A1, 0) returns 7, exactly what the rulebook calls for. Switch to =ROUND() and you'd pay 7 hours; switch to =ROUNDUP() and you'd pay 8. The function choice is the policy. Document it next to the cell.

MROUND — Round to a Multiple

MROUND is the function for non-decimal rounding. Round to the nearest 5, the nearest 0.25 (quarter), the nearest 15 minutes, the nearest $10. Syntax: =MROUND(number, multiple). Both arguments must share a sign — you can't pass =MROUND(-17, 5) and expect a clean answer (Excel returns #NUM!); use =MROUND(-17, -5) instead.

Real examples:

• =MROUND(17.3, 0.25) → 17.25 (nearest quarter)
• =MROUND(127, 5) → 125 (nearest nickel multiple)
• =MROUND("7:47 AM", "0:15") → 7:45 AM (nearest quarter hour — wrap times in quotes)
• =MROUND(0.13, 0.05) → 0.15 (nearest 5 cents)

Banker's rounding workaround: Excel doesn't have a native banker's rounding function, but =MROUND(A1, 0.01) doesn't fix it either — MROUND uses arithmetic rounding too. For true banker's rounding (round-half-to-even), use this formula: =IF(MOD(A1*100,1)=0.5,MROUND(A1*100,2)/100,ROUND(A1,2)). Most users don't need this — but if you're moving data between Excel and Python/R/SQL that all use banker's rounding by default, totals can diverge by pennies. Pre-rounded data avoids the surprise.

One niche MROUND trick: chunked progress bars. If you're building a dashboard that fills a bar in 10% increments, =MROUND(actual_percent, 0.1) snaps the live value to the nearest tenth. Cleaner visuals, less jitter. The same idea works for star ratings (snap to nearest half-star), survey scores (snap to nearest 0.5), and pricing tiers (snap to nearest $5).

One more important behavior to internalize: MROUND uses arithmetic rounding for the halfway case, exactly like ROUND. So =MROUND(7.5, 5) returns 10, not 5 — even though 7.5 is equally distant from both. If you need always-up rounding to a multiple, switch to =CEILING.MATH(7.5, 5). If you need always-down, switch to =FLOOR.MATH(7.5, 5). Picking between MROUND, CEILING.MATH, and FLOOR.MATH comes down to one question — when the value sits on the boundary, which direction does your business rule say to push it?

CEILING, FLOOR, and the .MATH Variants

CEILING rounds up to the nearest multiple of significance. FLOOR rounds down. The base syntax: =CEILING(number, significance) and =FLOOR(number, significance). The newer .MATH versions (introduced in Excel 2013) added a third argument to control negative-number behavior and made the function more predictable across platforms.

=CEILING.MATH(number, [significance], [mode]). Set mode to a non-zero value to round negative numbers away from zero (toward more negative). Leave it 0 (the default) to round negative numbers toward zero (which is the more intuitive direction for most accounting work).

Old CEILING quirk: in legacy Excel, =CEILING(-4.5, -1) returned -5 (away from zero), while =CEILING(-4.5, 1) returned a #NUM! error because the signs didn't match. CEILING.MATH eliminates the sign-matching requirement — you can pass a positive significance with a negative number and it just works.

Practical FLOOR.MATH example: a discount tier system that rounds order totals down to the nearest $50 — =FLOOR.MATH(order_total, 50). An order of $237 falls into the $200 tier. An order of $251 falls into the $250 tier. Easy bucketing without nested IFs.

CEILING.MATH also handles fractional significance gracefully. Need to round up to the next 0.05 (think pricing in nickels)? =CEILING.MATH(A1, 0.05) snaps any value up to the next nickel. Need to round up to the next quarter hour for a billing system? =CEILING.MATH(A1, TIME(0,15,0)) — and yes, time values work because they're stored as fractions of a day under the hood.

TRUNC vs INT (and What Happens With Negatives)

For positive numbers, TRUNC and INT do the same thing — they drop the decimal part. =TRUNC(7.9) returns 7. =INT(7.9) returns 7. Identical.

For negative numbers, they diverge. =TRUNC(-7.9) returns -7 (truncates toward zero). =INT(-7.9) returns -8 (rounds down to the next integer, which for negatives means away from zero). This is a frequent bug source: somebody uses INT thinking it just drops decimals, then negative inputs return unexpected values.

Rule of thumb: use TRUNC when you want "drop the decimal part, sign-aware" — it always moves toward zero. Use INT when you want "floor to the next lower integer" — it always moves toward negative infinity. They only behave differently with negatives, but that difference matters in time math, latitude/longitude work, and any subtraction that can produce negative values.

TRUNC also has an optional second argument: =TRUNC(3.14159, 2) returns 3.14 — it truncates to a specific decimal place. INT has no second argument; it always returns a whole integer.

A clean way to demo the difference: drop -2.7 in A1 and put =TRUNC(A1) in B1 and =INT(A1) in C1. You'll see -2 and -3 side by side. Now flip the sign — put 2.7 in A1. Both columns show 2. The divergence only shows up when the input is negative, which is exactly why this bug slips through testing. Reviewers test happy-path positive cases, never realize the negative path is broken, and find out two quarters later when a refund report shows the wrong totals.

For separating the integer and decimal portions of a number, TRUNC is the friendlier choice. The integer part: =TRUNC(A1). The decimal remainder: =A1 - TRUNC(A1). This works for both positive and negative inputs without surprises — the decimal piece keeps the sign of the original. Try the same with INT and a negative input and the math no longer matches what you'd hand-calculate, because INT shifted the integer one step further from zero than you expected.

EVEN and ODD

Less common but genuinely useful for packaging and pairing logic. =EVEN(number) rounds up (away from zero) to the next even integer. =ODD(number) rounds up to the next odd integer. Both ignore the sign in a sense — they always move away from zero. Already-even inputs stay even; already-odd inputs stay odd. No surprise jumps. The boundary cases follow a single predictable rule, which makes these two functions feel less surprising than ROUNDUP for negative numbers.

One quick check before using EVEN or ODD on a real dataset: confirm that your inputs are positive, or that you genuinely want the away-from-zero behavior on negatives. A common mistake is treating EVEN as a magnitude rounder when it's really a directional one — moving toward larger absolute value every time, never toward zero.

Examples:

• =EVEN(3.1) → 4
• =EVEN(4) → 4 (already even, no change)
• =EVEN(-3.1) → -4 (away from zero, so more negative)
• =ODD(2.1) → 3
• =ODD(3) → 3 (already odd)

Use cases: pairing items (always round up to an even count so nothing's left over), ordering packs of two, generating odd-only sequence IDs. Niche but the right tool when you need it. Event planners use EVEN to round attendance up to the next pair so seating works at round tables. Schedulers use ODD when each shift needs a tie-breaker headcount.

Number Format vs ROUND — The Money Trap

This is the single biggest source of "my totals are off by a penny" support tickets in Excel. Number formatting changes what you see; ROUND changes what's stored. A column of fifty values formatted to show 2 decimals will sum to the unrounded total, even though every individual cell looks like a clean 2-decimal number.

To see the difference: put 5.4729 in A1. Format it to 0 decimals — it shows 5. Now write =A1*2 in A2. You get 10.9458 (formatted as 11), not 10. The formula used the stored value, not the displayed one. This trips up every junior analyst at least once.

Two fixes. The safer one: wrap each value in =ROUND(A1, 2) at the point you commit it. The drastic one: File → Options → Advanced → Set precision as displayed. That toggle permanently truncates all stored values to their displayed precision in the entire workbook. It's irreversible — once Excel writes the truncated value, the precision is gone. Don't use it unless you fully understand the cost.

Diagnostic trick: when totals don't match, widen the column and increase decimal display to 10 places. Hidden precision becomes visible immediately. You'll see something like 5.47294839 where you thought you had 5.47. Now you know exactly where the discrepancy lives and which inputs to wrap with =ROUND().

For more on building clean spreadsheet workflows, see how to separate first and last name in excel using formulas that pair well with rounding, or learn how to change column width in excel when rounded numbers no longer fit a narrow column.

A final note on currency. If you're modeling money in Excel, the safe pattern is: store raw inputs at full precision, run all intermediate math at full precision, and apply =ROUND(value, 2) only at the leaf cells that get displayed, exported, or summed. Rounding in the middle introduces drift that snowballs across long calculation chains. Rounding at the edge gives clean output without sacrificing internal accuracy. Get this pattern right once and most of the "why is the total off by a penny" questions in your workbook simply stop happening.

One last reminder before you close this guide and go fix your spreadsheet. Test your rounding choices against negative inputs, against zero, and against values that already sit exactly on a multiple. Those three edge cases catch about 90 percent of rounding bugs before they ship. Drop sample inputs of -2.5, 0, and a clean multiple like 10 into a scratch column, fill the formula across, and eyeball the output. If the outputs look right at the edges, the middle of your dataset will be fine too.