How to Round Up to the Nearest 5 in Excel

To round a number up to the next multiple of 5 in Excel, use the CEILING function or divide, round up, and multiply back.

The difference between “up” and “to the nearest” matters when the value is already close to a multiple.

Round up to the next multiple of 5 with CEILING

For a value in cell A2, enter this formula in another cell:

=CEILING(A2,5)

For nonnegative values in current Excel versions, this rounds up to the next multiple of 5. Exact multiples remain unchanged.

For example, if A2 contains 12, the result is 15.

If it contains 15, the result stays 15.

Copy the formula down a column to apply it to more rows.

If the input values are in A2:A100, put the formula in B2, then fill it down through B100.

Use CEILING.MATH for more control

CEILING.MATH is available in Excel 2013 and later, including Microsoft 365.

It rounds a number up to a specified multiple and provides an optional argument for controlling how negative values are handled.

=CEILING.MATH(A2,5)

For positive inputs, this behaves like =CEILING(A2,5).

For example, 12 becomes 15, and 15 remains 15.

With negative inputs, the direction can be less intuitive: CEILING.MATH rounds toward a higher multiple on the number line, so -12 becomes -10.

To make the handling of negative numbers explicit, use the optional mode argument:

=CEILING.MATH(A2,5,1)

With a nonzero mode argument, CEILING.MATH rounds negative values away from zero: =CEILING.MATH(-12,5,1) returns -15. With the mode omitted, =CEILING.MATH(-12,5) returns -10.

These are different definitions of rounding upward. See Microsoft’s CEILING.MATH documentation.

Use a formula that works in older Excel versions

If you need a straightforward alternative, divide by 5, round up to a whole number, and multiply by 5:

=ROUNDUP(A2/5,0)*5

For A2 = 12, the calculation is ROUNDUP(2.4,0)*5, which returns 15. For A2 = 15, it returns 15.

ROUNDUP rounds away from zero, so -12 becomes -15 with this pattern. It agrees with the positive-number ceiling examples but differs from rounding a negative number toward positive infinity.

Another option for nonnegative values is:

=INT((A2+4)/5)*5

This shortcut returns 15 for 11 and 20 for 16. Use it only for nonnegative whole-number inputs: adding 4 does not correctly round every decimal value up. For example, 10.1 would incorrectly give 10 with this shortcut; use CEILING or CEILING.MATH for decimals.

Round up to 5 while keeping decimal places

To round upward to the next multiple of 5, the result is normally a whole number, even when the input contains decimals.

For example, 12.01 rounds to 15, while 15.00 stays 15.

If instead you mean round upward to the nearest 5 cents, use a significance of 0.05:

=CEILING(A2,0.05)

For example, an input of 12.31 returns 12.35.

The significance sets the interval: use 5 for multiples of five whole units, 0.5 for half-unit steps, and 0.05 for five-cent steps.

Round up only when the value is not already a multiple of 5

“Round up” often means “go to the next multiple,” but a ceiling formula leaves exact multiples unchanged.

If an exact multiple should move to the following multiple instead, use:

To advance an exact multiple while otherwise rounding upward, test whether the value is already divisible by 5:

=IF(MOD(A2,5)=0,A2+5,CEILING(A2,5))

With this rule, 12 becomes 15, while 15 becomes 20.

For decimals that should count as equivalent to a multiple because of floating-point precision, consider rounding the input to a known precision before applying the test.

Common issues and edge cases

• Negative values: Rounding toward positive infinity sends -12 to -10; rounding away from zero sends it to -15. Select the rule before choosing the formula.
• Text: Convert imported numeric text to numbers if necessary.
• Keep blanks blank: Use =IF(A2="","",CEILING(A2,5)).
• Decimal precision: A displayed 15.00 may contain a small stored fraction. Round to a known input precision first when that matches the business rule.
• Regional separators: Some settings require =CEILING(A2;5).

Does the formula work in Google Sheets?

Google Sheets supports CEILING and ROUNDUP, so the same basic formulas work there:

=CEILING(A2,5)
=ROUNDUP(A2/5,0)*5

Function names and argument separators can vary with spreadsheet locale settings.

If your sheet uses semicolons as argument separators, replace the commas accordingly.

Which formula should you choose?

• Use =CEILING(A2,5) for the simplest formula in current Excel and Google Sheets.
• Use =ROUNDUP(A2/5,0)*5 when you want a familiar approach that also works in older Excel versions.
• Use =CEILING(A2,0.05) for increments of five cents rather than five whole units.
• Use =IF(MOD(A2,5)=0,A2+5,CEILING(A2,5)) when exact multiples must advance to the following multiple.