How to Round in Excel: ROUND, ROUNDUP and ROUNDDOWN

Formatting a cell to show fewer decimals and rounding a number aren't the same thing, and the difference matters. Formatting only changes what you see; Excel still uses the full number in every calculation. ROUND changes the number itself. If your stock averages or delivery times need to be whole numbers or a tidy one decimal place, ROUND is the function you want.

Rounding a number with ROUND

1. Click the cell where you want the rounded result.
2. Type =ROUND(, then click the cell holding your number, for example A2.
3. Type a comma and the number of decimal places you want, then close the bracket: =ROUND(A2, 1).
4. Press Enter, then drag the formula down the column.

Microsoft's own example: if A1 holds 23.7825, =ROUND(A1, 2) gives 23.78. The second part of the formula, which Microsoft calls num_digits, is the only thing you need to think about.

What the second number does

Second argument	Effect	Microsoft's example
2	Two decimal places	=ROUND(-1.475,2) gives -1.48
1	One decimal place	=ROUND(2.15,1) gives 2.2
0	Nearest whole number	Rounds to an integer
-1	Nearest 10	=ROUND(21.5, -1) gives 20

Microsoft explains the rule: above zero rounds to that many decimal places, zero rounds to the nearest whole number, and below zero rounds to the left of the decimal point. So -1 rounds to tens, -2 to hundreds.

Rounding up, rounding down and rounding to a step

• ROUNDUP always rounds away from zero. =ROUNDUP(A2, 0) turns 2.15 into 3, which is what you want when working out boxes needed to pack an order.
• ROUNDDOWN always rounds towards zero. =ROUNDDOWN(A2, 0) turns 1.98 into 1, handy for counting complete units.
• MROUND rounds to a multiple. Microsoft's example is rounding to the nearest 0.5; for packs of 6, =MROUND(A2, 6) rounds to the nearest pack size.

I find ROUNDUP more useful than ROUND in day-to-day stock work. If an order needs 2.15 boxes, you need 3, not 2.

The catch with formatting instead of rounding

This is the mistake that makes totals look wrong. If you use the Decrease Decimal button on the Home tab, a cell holding 2.149 shows as 2.1, but Excel still adds up 2.149. Add up a column of those and the total can be slightly off from the numbers you can see, which confuses anyone checking it by hand.

ROUND fixes that, because the cell then holds the rounded number. Microsoft's examples show the subtlety too: =ROUND(2.15,1) gives 2.2, but =ROUND(2.149,1) gives 2.1. The extra digits decide which way it goes.

My rule is simple. If the number only needs to look tidy, format it. If other formulas or people depend on the rounded value, use ROUND.

Rounding inside another formula

You don't need a separate column. Wrap ROUND around any calculation: =ROUND(B2/C2, 1) divides items by orders and rounds the result to one decimal place in one go. That's useful for averages, which are often long strings of decimals. If you're new to division formulas, my guide to dividing in Excel covers the basics and the #DIV/0! error.

Negative numbers and ties

ROUND treats negative numbers symmetrically. Microsoft's example =ROUND(-1.475,2) returns -1.48. ROUNDUP moves away from zero, so a negative number gets more negative, and ROUNDDOWN moves towards zero. If you're working with differences, such as stock counted against stock expected, check a negative example before you copy the formula down.

Rounding to the nearest 10

For a rough stock count or a headline number in a report, round to tens with a negative argument. Microsoft's example =ROUND(21.5, -1) gives 20. Use -2 for hundreds. That's a nice way to make a summary feel readable without anyone mistaking it for an exact count.

If you're rounding percentages, my guide to calculating percentages in Excel covers the formulas behind them.

A worked example for a stock sheet

Here's a small example I'd use. Column A lists products, column B the units in stock and column C how many units fit in a box. In D2, =B2/C2 tells you how many boxes the stock fills, often with a long decimal. Change it to =ROUNDUP(B2/C2, 0) and you get the number of boxes you'll need to store it all, as a whole number. Change it to =ROUNDDOWN(B2/C2, 0) and you get how many full boxes you can pack, with the leftovers ignored.

Both are right; they just answer different questions. That's the real skill with rounding in Excel. Before you pick a function, ask whether a part-filled box should count as one or not at all. Once you've answered that, the formula almost writes itself, and the numbers on your sheet will match what's on the shelves.

Rounding in Excel for the web and on a Mac

ROUND, ROUNDUP and ROUNDDOWN work the same way in every version of Excel, including the browser and the Mac app. You type them into a cell exactly as above. The Increase Decimal and Decrease Decimal buttons sit on the Home tab in all of them too, so remember they only change the display, not the stored value.

My verdict

Use ROUND when the rounded number matters, ROUNDUP when you can't have a fraction left over, and plain formatting only when it's cosmetic. Two decimal places, one, zero or minus one covers nearly everything a small business sheet needs.

Frequently asked questions

How do I round to a whole number in Excel?

Use =ROUND(A2, 0). ROUNDUP and ROUNDDOWN with 0 always go up or down.

How do I round to 2 decimal places in Excel?

Use =ROUND(A2, 2). Microsoft's example turns 23.7825 into 23.78.

What is the difference between ROUND and formatting?

Formatting only changes how the number looks. ROUND changes the value Excel uses in calculations.

How do I always round up in Excel?

Use ROUNDUP, for example =ROUNDUP(A2, 0) to round 2.15 up to 3.

How do I round to the nearest 10?

Use a negative second argument: =ROUND(A2, -1).