Cellwise Try it free

Excel guide · 2 min read

How to round to the nearest 5, 10 or 100 in Excel

Quick answerFor 10 or 100 use ROUND with a negative digit count: =ROUND(A2,-1) rounds to the nearest 10 and =ROUND(A2,-2) to the nearest 100. For 5, use =MROUND(A2,5) or =ROUND(A2/5,0)*5.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

AB
1ItemPrice
2Monitor1234.5
3Keyboard47.3
4Mouse22.8
5Laptop stand63.1
6Desk388

Nearest 10, 100 or 1,000 with ROUND

ROUND(number, num_digits) usually rounds decimals, but a negative num_digits rounds to the left of the decimal point. -1 means tens, -2 hundreds, -3 thousands.

Halves round away from zero, so 1,235 would go up to 1,240.

=ROUND(B2,-1)

Result: 1230

1,234.50 rounds to 1,230, the nearest 10.

Nearest 5 (or any multiple)

MROUND(number, multiple) rounds to the nearest multiple you choose: =MROUND(B3,5) gives 45, =MROUND(B3,0.25) gives the nearest quarter. It works in all current versions of Excel.

The divide-round-multiply trick gives the same result and works everywhere, including with negative numbers, where MROUND returns #NUM! unless the multiple is negative too.

=ROUND(B3/5,0)*5

Result: 45

47.30 divided by 5 is 9.46, which rounds to 9, times 5 gives 45.

Always round up to the next 5 or 10

For prices, packaging or staffing you often need to round up, never down. ROUNDUP with the same trick does it. Excel also has CEILING.MATH(number, significance) in Excel 2013 and later: =CEILING.MATH(B4,5) gives 25.

=ROUNDUP(B4/5,0)*5

Result: 25

22.80 rounds up to 25.

Always round down

ROUNDDOWN cuts toward zero. Use it for things like "full hundreds only" when budgeting. FLOOR.MATH(number, significance) is the built-in equivalent.

=ROUNDDOWN(B6,-2)

Result: 300

388 rounds down to 300.

Make prices end in .99

A common retail trick: round up to the next whole number, then subtract a cent. The same idea works for "round to the nearest 10, minus 1" price points.

=ROUNDUP(B5,0)-0.01

Result: 63.99

63.10 becomes 63.99.

Common mistakes

Changing the display instead of the value

Decrease Decimal on the ribbon only hides digits. Totals still use the full value. Use ROUND when the rounded number must be used in later math.

MROUND with negative numbers

=MROUND(-7,5) returns #NUM! because the signs differ. Use =MROUND(-7,-5) or the =ROUND(A2/5,0)*5 pattern.

Expecting ROUND(x,1) to round to 10

A positive num_digits keeps decimals: ROUND(47.3,1) is 47.3. Use -1 for tens.

FAQ

How do I round to the nearest 5 in Excel?

Use =MROUND(A2,5). Or use =ROUND(A2/5,0)*5, which also handles negative numbers. To always round up, use =CEILING.MATH(A2,5).

How do I round to the nearest thousand in Excel?

Use =ROUND(A2,-3). For display only, a custom number format like #,##0,"K" shows thousands without changing the value.

How do I round to the nearest 0.5 or 0.25?

Use =MROUND(A2,0.5) or =MROUND(A2,0.25). The equivalent without MROUND is =ROUND(A2*4,0)/4 for quarters.

What is the difference between ROUND, ROUNDUP and ROUNDDOWN?

ROUND goes to the nearest value, with halves rounding away from zero. ROUNDUP always moves away from zero and ROUNDDOWN always moves toward zero, whatever the digits. All three take the same num_digits argument.

How do I round time to the nearest 15 minutes?

Times are fractions of a day, so use =MROUND(A2,"0:15") or =ROUND(A2*96,0)/96, since a day has 96 quarter hours. Format the result as a time.

Which rounding method should I use?

Use ROUND with -1 or -2 for tens and hundreds, MROUND for other multiples like 5 or 0.25, and ROUNDUP or CEILING.MATH when you must never come in under the value, such as shipping boxes or price floors.

Functions used

Related guides

Updated