Excel ROUND Function — ROUND, ROUNDUP & ROUNDDOWN (and Why Formatting Isn't Rounding)

TL;DR — =ROUND(number, num_digits) changes the stored value to a set number of decimal places. Setting a cell's format to two decimals does not — it only changes the display, while the full-precision number lives on underneath. That gap is why a column of "2-decimal" prices can sum to a total that's a penny off. num_digits controls the precision: positive = decimal places, 0 = whole number, negative = round to tens/hundreds/thousands. ROUNDUP and ROUNDDOWN are the always-away and always-toward-zero siblings — and the toward/away is measured from zero, which is the negative-number trap. Works in all versions.

=ROUND(2.345, 2)      ' -> 2.35   (round to 2 decimals)
=ROUND(1234.5, -2)    ' -> 1200   (round to the nearest hundred)

Rounding looks like the most boring function in Excel, and for positive numbers typed by hand it nearly is. The trouble is that rounding for display and rounding the value are two different operations, and Excel lets you do the first by accident while you think you've done the second. Get that distinction straight and the penny-off totals, the audit mismatches, and the "but the cell says 2.35" arguments all dissolve. This guide is built around that one mental model, then the rules that follow from it.

What you'll learn

The mental model: formatting hides digits, ROUND removes them
Why "2-decimal" columns still sum wrong — and the one-line fix
num_digits: positive, zero, and the underused negative case
ROUND vs ROUNDUP vs ROUNDDOWN — and what "up" really means
The negative-number trap: away-from-zero ≠ mathematically up
The judgment call: when to round, and which one to reach for

The mental model: formatting hides digits, ROUND removes them

Picture every number in a cell as having a long, true value and a separate mask over it. When you click Decrease Decimal or apply a currency format, you change the mask — the cell shows 2.35 — but the value underneath is still 2.345678. Excel calculates with the value, not the mask.

ROUND is the opposite: it throws the extra digits away for good and stores the shorter number. After =ROUND(2.345678, 2) the cell genuinely is 2.35 — to every formula that reads it later.

' Cell shows 2.35 either way, but:
A1: 2.345678 formatted to 2 dp   -> SUM sees 2.345678
A2: =ROUND(2.345678, 2)          -> SUM sees 2.35

Hold that picture and the most infamous rounding bug explains itself.

The penny-off total: the rule that saves you

Format a column of prices to two decimals, sum it, and the total can disagree with adding up the displayed numbers by hand. Each cell displays a rounded figure but stores full precision, so the sum carries all those hidden fractions of a cent and rounds once at the end — landing a penny or two away from what the eye expects.

' Displayed: 1.005 + 1.005 shown as 1.01 + 1.01 = 1.02 expected
' Stored:    1.005 + 1.005 = 2.01 -> rounds to 2.01, not 2.02
=ROUND(price, 2)     ' round each value first, then SUM is exact

The rule: if money has to add up to the penny, round the values, not the view. Round each line with =ROUND(...,2) (or round the final total once, depending on which your accounting rules require) so the numbers Excel adds are the numbers people see. There is even a workbook-wide Precision as displayed setting, but avoid it — it permanently truncates every value in the file and bites you later. Reach for ROUND at the specific cells that matter instead.

num_digits: positive, zero, and the negative trick

The second argument is where most of ROUND's power hides, because it isn't limited to "how many decimals."

=ROUND(3.14159, 2)    ' -> 3.14    positive: decimal places
=ROUND(3.14159, 0)    ' -> 3       zero: nearest whole number
=ROUND(3.14159, 4)    ' -> 3.1416  more decimals
=ROUND(1234.5, -1)    ' -> 1230    negative: nearest ten
=ROUND(1234.5, -2)    ' -> 1200    nearest hundred
=ROUND(1234.5, -3)    ' -> 1000    nearest thousand

Negative num_digits rounds to the left of the decimal point, and it's the part almost nobody knows. Rounding salaries to the nearest hundred, populations to the nearest thousand, or chart axes to clean intervals is one short formula, not a manual fudge. Think of num_digits as a number line position: positive walks right into the decimals, 0 stops at the ones place, negative walks left into tens, hundreds, thousands.

ROUND vs ROUNDUP vs ROUNDDOWN

Same first two arguments, different tie-and-direction behaviour:

=ROUND(2.4, 0)        ' -> 2     normal rounding: < .5 down, >= .5 up
=ROUND(2.5, 0)        ' -> 3
=ROUNDUP(2.1, 0)      ' -> 3     always away from zero
=ROUNDDOWN(2.9, 0)    ' -> 2     always toward zero

ROUND follows the rule you learned in school: 5 and above rounds up, below 5 rounds down. ROUNDUP always pushes to the next digit regardless of what follows — use it when going under is not allowed (boxes needed to ship every unit, minutes billed in whole increments, a price that must clear a cost). ROUNDDOWN always chops toward zero — use it when going over is not allowed (units you can actually afford within a budget, a person's whole-year age).

The negative-number trap: "up" means away from zero

Here is the subtlety that produces wrong results silently: ROUNDUP and ROUNDDOWN are defined by distance from zero, not by the number line's up and down.

=ROUNDUP(-2.1, 0)     ' -> -3    "up" = further from zero = more negative
=ROUNDDOWN(-2.9, 0)   ' -> -2    "down" = toward zero = less negative

So ROUNDDOWN(-2.9, 0) gives -2, even though -2 is mathematically higher than -2.9. If you actually want the mathematical floor and ceiling — always toward minus-infinity or plus-infinity, sign and all — those are different functions: INT (true floor) and CEILING.MATH/FLOOR.MATH. Mixing them up is the classic "my negative numbers are off by one" bug; see Excel INT, TRUNC & MOD for the floor-vs-truncate distinction and Excel MROUND, CEILING & FLOOR for rounding to a multiple.

A bonus reason to round: floating-point noise

Computers store decimals in binary, so a few values that look exact aren't. The famous example, 0.1 + 0.2, lands at 0.30000000000000004, and an equality test against 0.3 fails for no visible reason. Wrapping a comparison — or a stored result — in ROUND normalises that noise away:

=(0.1 + 0.2 = 0.3)               ' -> FALSE  (binary floating point)
=(ROUND(0.1 + 0.2, 10) = 0.3)    ' -> TRUE

This is one more argument for rounding monetary results to two decimals at the point you store them, rather than trusting that arithmetic "just works."

The judgment call: which rounding, and when

Reach for plain ROUND for money and any figure that has to add up exactly — round the values, not the format. Use ROUNDUP when undershooting is the expensive mistake (capacity, shipping, billing in whole units) and ROUNDDOWN when overshooting is (budgets, eligibility, ages). If you're rounding to a multiple rather than a number of decimals — nearest 5 cents, nearest 15 minutes, whole pallets — you want MROUND, CEILING, or FLOOR instead. And if you only need a cleaner display while keeping full precision for the maths, don't round at all: change the cell format. The skill is knowing which of those three jobs — store, constrain, or display — you're actually doing.

How ExcelMaster helps

Rounding rules are usually stated in plain business language — round each invoice line to the cent, but always round shipping cartons up — and the bug is almost always a mismatch between what's displayed and what's stored. ExcelMaster reads that intent and writes the right call: ROUND where totals must reconcile, ROUNDUP/ROUNDDOWN where a direction is mandated, and a number format (not a formula) when you only wanted a tidier view. You describe the rule; it picks the operation that won't leave you a penny short at audit.

Frequently asked questions

Why doesn't my column sum to what the cells show?

The cells are formatted to fewer decimals but still store full precision, so the sum carries the hidden fractions. Round the values themselves with =ROUND(cell, 2) (or round the total once) so Excel adds the same numbers you see.

How do I round to 2 decimal places in Excel?

Use =ROUND(number, 2). The 2 is num_digits — the number of decimal places to keep. Use 0 for a whole number.

What's the difference between ROUND and ROUNDUP?

ROUND follows normal rounding (.5 and above goes up, below goes down). ROUNDUP always rounds away from zero no matter the next digit, so 2.1 becomes 3. Use ROUNDUP when you can never undershoot.

How do I round to the nearest hundred or thousand?

Use a negative num_digits: =ROUND(1234, -2) gives 1200 (nearest hundred) and =ROUND(1234, -3) gives 1000 (nearest thousand).

Does ROUNDDOWN round negative numbers toward zero or down?

Toward zero. =ROUNDDOWN(-2.9, 0) returns -2, not -3, because "down" means toward zero. For a true mathematical floor (toward minus-infinity) use INT.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-27.