How to Round Numbers in Excel, ROUND vs ROUNDUP vs ROUNDDOWN

You split a $100 bill three ways, format the cells to two decimals, and the sheet shows $33.33, $33.33 and $33.33. The total cell underneath says $100.00. Anyone checking with a calculator gets $99.99 and wonders who is wrong. Nobody is, exactly. The cells were formatted, not rounded, and Excel treats those as two very different things.

1. Formatting changes what you see, not what is stored

Microsoft's page on calculation precision is direct about this: by default, Excel calculates with stored values, not displayed values. Changing how a value is displayed does not change the value.

Here is the split from the opening, with =100/3 in A2, A3 and A4, formatted to two decimals:

The total is correct for the stored values. If the shares will be paid out as cents, round them as you calculate:

=ROUND(100/3,2)

Filled into B2:B4, each cell now truly holds 33.33, and =SUM(B2:B4) returns 99.99. The sheet now matches reality: three payments of $33.33 leave one cent unassigned, and you can decide where it goes.

Microsoft's precision page also describes a workbook option called Set precision as displayed, and warns that it permanently changes stored values to whatever is displayed, with no way to restore the original precision. Rounding in formulas is safer because it only affects the cells you choose.

2. ROUND: the nearest value

Microsoft gives the syntax as ROUND(number, num_digits). The second argument decides where the rounding happens:

• Positive: that many decimal places. =ROUND(2.149,1) returns 2.1.
• Zero: the nearest whole number.
• Negative: to the left of the decimal point. =ROUND(21.5,-1) returns 20, and =ROUND(626.3,-3) returns 1000.

Negative digits are handy for summary reports. =ROUND(1234567,-3) returns 1,235,000, which reads better on a slide than the exact figure.

Microsoft's own examples also show how halves behave: =ROUND(2.15,1) returns 2.2 and =ROUND(-1.475,2) returns -1.48. A half rounds away from zero in both directions.

3. ROUNDUP and ROUNDDOWN: always one direction

Microsoft describes ROUNDUP as rounding away from zero and ROUNDDOWN as rounding toward zero. They take the same two arguments as ROUND.

These are the right tools when a partial unit is not allowed:

=ROUNDUP(130/24,0)

If 130 items ship in boxes of 24, you need 6 boxes. 130/24 is about 5.42, and plain ROUND would return 5, leaving ten items with no box.

=ROUNDDOWN(47/7,0)

If you want the number of complete weeks in 47 days, this returns 6. The leftover five days do not make a seventh week.

Watch negative numbers. Because ROUNDDOWN moves toward zero, =ROUNDDOWN(-3.14159,1) returns -3.1, while =ROUNDUP(-3.14159,1) returns -3.2. Both match Microsoft's documented examples. If you expected "down" to mean "smaller," note that INT works differently: =INT(-3.2) returns -4, but =ROUNDDOWN(-3.2,0) returns -3.

4. MROUND: the nearest multiple

When values must land on a step size, such as quarter hours on a timesheet or five-cent prices, MROUND rounds to the nearest multiple you name:

=MROUND(7.38,0.25)

This returns 7.5. A shift of 7.1 hours returns 7. Microsoft's rule: MROUND rounds up, away from zero, when the remainder is at least half the multiple. For 7.38, the remainder after 7.25 is 0.13, which is more than half of 0.25, so it goes up.

Two limits from Microsoft's page are worth knowing:

• The number and the multiple must have the same sign. =MROUND(5,-2) returns #NUM!.
• With a decimal multiple, exact midpoints are not guaranteed to round the same way. Microsoft's example: =MROUND(6.05,0.1) returns 6.0, while =MROUND(7.05,0.1) returns 7.1. Those two results come from Microsoft's page. If midpoints matter, say for pay, test your actual values first.

5. Which function to use

6. Round once, at the right step

Round at the point where the real-world rule applies, such as each invoice line or each payment, and then add up the rounded values. Rounding only the grand total gives a different number from rounding every line, and the two will not always reconcile.

7. Key takeaways

• Number formatting only changes the display; Excel still calculates with the full stored value.
• ROUND goes to the nearest value; negative digits round to tens, hundreds or thousands.
• ROUNDUP and ROUNDDOWN always move away from or toward zero, which matters for negative numbers.
• MROUND rounds to a multiple, but its midpoints are not guaranteed with decimal steps.
• Avoid "Set precision as displayed" unless you accept permanently losing the original values.