The Excel ROUND function looks deceptively simple. You hand it a number, you tell it how many decimal places you want, and it spits back a rounded value. Yet I've watched accountants stare at a 0.01 discrepancy for half an hour because they treated ROUND as a cosmetic tool rather than a mathematical one. It is not formatting. It rewrites the underlying value, and that distinction matters when totals refuse to add up.
This guide walks through ROUND the way you'd actually use it on the job. We cover the syntax, the quirks of negative digit arguments, the difference between ROUND and ROUNDUP, the banker's rounding myth, and the moment when you should reach for MROUND or FLOOR instead. By the end, you'll know exactly which rounding function to grab and why.
If you're brushing up for a certification exam or a job interview, the Excel skills guide covers the broader landscape. For a quick reference once you're comfortable, the Excel cheat sheet bundles ROUND alongside its cousins. This page is the deep dive.
Quick reference: =ROUND(number, num_digits). Positive digits trim decimals, zero rounds to integers, negative digits round to tens, hundreds, or thousands. Excel rounds halves away from zero, not using banker's rounding. ROUND changes the stored value, not just the display.
The function signature is =ROUND(number, num_digits). The first argument is the value you want to round. The second is how many decimal places you want after the rounding finishes. Both arguments are mandatory. Leave one out and Excel will throw a #NAME? or argument error depending on how badly you've fumbled the parentheses.
A positive num_digits rounds to the right of the decimal point. So =ROUND(3.14159, 2) returns 3.14. Setting num_digits to zero rounds to the nearest whole number, which means =ROUND(7.6, 0) gives you 8. That part is intuitive.
The surprise comes with negative digits. =ROUND(1234.5, -2) rounds to the nearest hundred and returns 1200. =ROUND(1234.5, -3) rounds to the nearest thousand and returns 1000. Negative digits are the trick that makes ROUND useful for financial summaries where you want figures shown in thousands or millions without the fractional clutter.
Rounds to the nearest, with halves moving away from zero. This is the default workhorse for general use.
Always rounds away from zero regardless of the fractional part. Useful for shipping boxes and capacity planning.
Always rounds toward zero. Functionally truncation for positive numbers. Differs from INT on negatives.
Rounds to any custom multiple like 5, 25, or 0.05. The number and multiple must share a sign.
Excel rounds halves away from zero. That means 2.5 rounds to 3 and -2.5 rounds to -3. This is sometimes called "round half up" for positive numbers, though strictly speaking it's "round half away from zero". You won't find banker's rounding (round half to even) anywhere in the standard ROUND function. If you need that behavior, you'll have to build it yourself or use the EVEN and ODD functions in clever combinations.
Why does this matter? Because some financial systems, scientific instruments, and statistical packages use banker's rounding to avoid systematic bias when summing many rounded values. If your Excel totals don't match an external system, the rounding rule is the first place to look. The numbers themselves are usually fine. The convention is the gremlin.
One more gotcha worth flagging: ROUND operates on the stored value, not the displayed value. If you've formatted a cell to show two decimal places but it actually contains 3.14159, ROUND sees 3.14159. To round what you see, you have to apply ROUND inside the formula that generates the value in the first place.
=ROUND(3.14159, 2) returns 3.14. =ROUND(0.005, 2) returns 0.01 in most cases (with floating-point caveats). Each unit of num_digits shaves one more decimal place from the result.
=ROUND(7.5, 0) returns 8. =ROUND(-7.5, 0) returns -8. Halves move away from zero in both directions, regardless of sign.
=ROUND(1234, -2) returns 1200. =ROUND(1234, -3) returns 1000. Negative digit values zero out place values to the left of the decimal point.
=ROUND(SUM(A1:A10)/COUNT(A1:A10), 2) computes a rounded average. Wrap any calculation in ROUND to control precision exactly where it matters.
Currency conversion is the textbook case. You multiply USD by an exchange rate and end up with a value like 47.328974. Display formatting will show $47.33 but the underlying number still trails decimals into the abyss. Sum a thousand such rows and the displayed total can differ from the actual total by several cents. Wrap the formula in =ROUND(A2*B2, 2) and the displayed value equals the stored value. Audits get a lot quieter.
Time-based reporting is another. Suppose you want to bin durations into the nearest five-minute slot. =MROUND(A2, "0:05") handles it cleanly if A2 holds a duration. Or for an hourly rate calculation where you need round numbers for invoicing, =ROUND(A2*B2, 0) produces an integer dollar amount.
Scientific notation is the third common case. If you're computing measurements that should respect significant figures, ROUND with a negative num_digits handles the larger magnitudes. For instance, rounding 12,847 to two significant figures becomes =ROUND(12847, -3), returning 13000. There's no built-in SIGFIG function in Excel, so ROUND with a calculated num_digits is how you get there.
Standard ROUND is bidirectional. Sometimes you want one-way behavior. ROUNDUP forces values away from zero regardless of the fractional part. If you're calculating how many shipping boxes you need for 47 items at 12 per box, =ROUNDUP(47/12, 0) returns 4 because three boxes wouldn't be enough. Standard ROUND would round 3.916 to 4 too in this case, but try the same trick with 47 items and 20 per box. ROUND returns 2, ROUNDUP returns 3, and only ROUNDUP gives the operationally correct answer.
ROUNDDOWN does the opposite. It always pushes toward zero, which makes it functionally equivalent to truncation. Use it for things like calculating how many full hours someone worked when partial hours don't count. =ROUNDDOWN(7.92, 0) returns 7. INT also returns 7 for positive numbers, but they behave differently on negatives: INT rounds toward negative infinity, so INT(-7.2) is -8 while ROUNDDOWN(-7.2, 0) is -7. Choose carefully when negatives are in scope.
FLOOR and CEILING add yet another dimension. They round to multiples in a directional way. =CEILING(127, 25) returns 150 because that's the next multiple of 25 above 127. =FLOOR(127, 25) returns 125. These show up constantly in pricing, scheduling, and inventory calculations.
ROUND plays well with most numeric functions. Wrap any calculation in it. =ROUND(AVERAGE(A1:A10), 1) rounds the average to one decimal. =ROUND(SQRT(A1), 4) rounds a square root to four decimals. The function returns a number, so you can chain it inside SUM, IF, LOOKUP, and the rest of Excel's arsenal.
One pattern that comes up in sum-style aggregations is rounding each row before totaling. =SUMPRODUCT(ROUND(A2:A100*B2:B100, 2)) rounds each product before summing. This produces a result that matches what a hand-checker would compute from the printed values. It can differ from a straight =SUMPRODUCT(A2:A100, B2:B100) by a few cents on large datasets.
If you're working with lookups, ROUND is sometimes the trick that makes a VLOOKUP work. Floating-point keys are notoriously prone to mismatches because 0.1 isn't exactly 0.1 in memory. Rounding both the lookup value and the lookup column to the same precision turns flaky lookups into deterministic ones.
Financial modelers use ROUND defensively. The reason is reconciliation. When your model has to tie to external statements, accounting systems, or audit reports, every line needs to round at the same level of precision the source uses. Mixing rounded and unrounded intermediate values silently introduces drift. A model that's off by $7 on a $50 million figure is technically fine, but try explaining that to an auditor.
The convention most senior analysts follow is to round at output, not at input. Keep the underlying calculations at full precision. Round only when the value lands in a presentation cell or a reportable total. This preserves accuracy for downstream math while keeping displayed values clean. The exception is when you're feeding a system that itself rounds, in which case you round in your model to match its behavior.
Tax calculations deserve a special mention. Tax tables and brackets often specify exact rounding rules in legislation. The IRS, HMRC, and equivalents elsewhere publish their rounding conventions. Build your formulas to match the statute, not your intuition. =ROUND(taxable_income * rate, 0) rounds tax to the nearest dollar, which is what most jurisdictions expect, but always verify against the local rule before you trust the result.
The most frequent ROUND complaint is "my totals don't add up". This is almost always because rounded values were summed instead of the underlying precise values. Or because some intermediate cells round and others don't. The fix is to standardize: either round every input consistently or round only the final output. Half-measures cause the discrepancies.
The second-most-frequent error is the #SPILL error when you feed an array into ROUND in a dynamic-array Excel and the target range is occupied. Clear the cells beneath or wrap in @ to force a single value. Older Excel versions never had this problem; modern ones do.
A subtler bug arrives when you reference a rounded cell in a chart axis or formula that expects exact values. The chart will look fine, but conditional formatting or threshold tests might fire incorrectly because the stored value is now the rounded one, not what you originally calculated. Mark rounded cells with a color or comment so future-you remembers what happened.
TRUNC strips decimals without rounding. =TRUNC(7.89, 1) returns 7.8. It's useful when you specifically don't want any rounding behavior. INT goes one further by always rounding toward negative infinity, which differs from TRUNC for negative numbers. INT(-7.2) returns -8, TRUNC(-7.2, 0) returns -7.
MROUND rounds to any multiple. =MROUND(127, 5) returns 125 because that's the nearest multiple of 5. Use it for pricing tiers, time-bucketing, and inventory packaging. The catch is that the number and the multiple must share a sign or you get a #NUM! error.
For deeper Excel topic coverage, the INDEX function guide and the INDIRECT function reference sit alongside ROUND as the math-and-lookup building blocks every analyst should command. Get those four down and most of Excel's expressive power opens up to you.
Rounding rules aren't universal. Swiss banks use a special rounding to the nearest five rappen (0.05 CHF) on cash transactions because the smallest physical coin is five rappen. In Excel that's =MROUND(A1, 0.05). Australian and New Zealand cash transactions round to the nearest five cents for the same reason. Card transactions usually don't round at all and keep the cent precision. Building both behaviors into the same model requires a flag column and a CHOOSE or IF to switch between rounding rules per transaction type.
Some jurisdictions require rounding to specific increments for sales tax. The US has no federal rule, so each state and county sets its own. Some round per line item, some round only the total, some use banker's rounding for fairness. Get the rule wrong and you'll over- or under-collect by pennies per transaction, which adds up fast at scale. When in doubt, ask the accounting department which convention applies and document the answer next to the formula.
Cryptocurrency adds another wrinkle. Most crypto assets quote eight decimal places, which is one satoshi for Bitcoin. If your spreadsheet treats them like standard two-decimal currency, you'll lose precision on every conversion. ROUND with num_digits set to 8 preserves the resolution. For ultra-high-precision settlement systems, even eight decimals isn't enough, and you'd typically reach for a dedicated decimal library outside Excel itself.
TRUNC accepts an optional digits argument. ROUNDDOWN requires it. Behavior matches for positive numbers and standard cases.
INT rounds toward negative infinity. TRUNC rounds toward zero. Same result for positives, opposite for negatives.
MROUND picks nearest multiple. CEILING and FLOOR force direction. Choose by whether direction matters more than distance.
Round to nearest even or odd integer. Niche but useful for parity-sensitive calculations like pair counting.
If you're onboarding a colleague who's new to Excel, ROUND is one of the first functions worth covering after the basics like SUM and AVERAGE. The reason isn't that ROUND is complicated. It isn't. The reason is that the consequences of misusing ROUND are invisible until someone runs an audit. Building the habit early prevents the kind of slow-burn bug that costs a team a weekend tracking down phantom cents.
The lesson plan I use: explain the syntax, demo positive and negative digits, show the displayed-versus-stored distinction with a formatted cell, then run the same calculation with and without ROUND wrapping intermediate values to demonstrate the drift. Most learners get it in under ten minutes. The lightbulb moment is usually when they see two SUM totals differ by a few cents and realize they would have shipped the wrong number if not for the demo.
Worth flagging during the training: keyboard shortcuts that pair well with ROUND, like Ctrl+1 for cell formatting and F9 for evaluating a selected formula portion. Both help reviewers understand whether the rounding has actually been applied or whether the cell just looks rounded. Pair the demo with a sample workbook showing common gotchas, and most teams come out of the session writing safer formulas immediately.
ROUND is the kind of function that rewards a few minutes of careful study. Master the syntax once and you'll catch bugs in your own and others' models for the rest of your career. The function itself is small; the discipline around it is what separates senior analysts from juniors. If you take one thing from this guide, let it be the displayed-versus-stored distinction. That's the source of nearly every rounding bug I've ever had to debug.
The next step is to put ROUND through its paces with real data. Pull up a financial workbook, find an intermediate calculation that doesn't round, and add ROUND to it. Watch what happens to the totals. Compare the result to the unrounded version. The differences will be small, often a cent or two, but the lesson sticks. After that, work through the cousin functions: ROUNDUP, ROUNDDOWN, MROUND, FLOOR, CEILING, INT, and TRUNC. By the time you've built a small reference workbook covering all of them, you'll never reach for the wrong one again.
One last habit worth adopting: when you finish a spreadsheet that uses ROUND in critical spots, save a snapshot before any further edits and tag it. Future audits frequently demand the version that produced a particular total, and an isolated archived copy with clear notes about the rounding convention saves hours of investigation later. Small effort, large payoff.