CEILING

Round a number up to the nearest multiple of a value you choose with Excel's CEILING function.

Math & Trigonometry
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=CEILING(number, significance) Returns: Number

Arguments

Argument Required Description
number Yes The number you want to round up.
significance Yes The multiple to round the number up to.

About

CEILING rounds a number up, away from zero, to the nearest multiple of a value you set. You give it the number you want to round and the significance (the multiple to snap to), and it returns the next multiple at or above that number. If the number is already an exact multiple of the significance, CEILING leaves it as is.

Reach for CEILING whenever values need to land on a fixed step. Round prices up to the nearest 5 cents, order stock in full packs of 12, or bill time in 15-minute blocks. Both arguments are required, so you always control the step size. Note that the number and significance must share the same sign, otherwise Excel returns an error.

For the opposite direction, use FLOOR to round down to a multiple. Use MROUND to round to the nearest multiple in either direction, and ROUNDUP when you want to round up by decimal places instead of to a multiple. For finer control over how negative numbers round, try the newer CEILING.MATH.

Examples

Round negative balances away from zero

See how CEILING rounds negative numbers when you give it a negative significance. A balance of -47 snaps to -50. Edit the values and watch each result update.

Spreadsheet editor

Count full cases to order

Combine CEILING with division to turn units needed into whole cases of 12. Change the units and see both the order quantity and the case count update.

Spreadsheet editor

Watch out for

⚠

Number and significance have different signs

Pairing a positive number with a negative significance (or the reverse) returns the #NUM! error instead of a result.

→ Make sure both arguments share the same sign. To round a negative number down away from zero, use a negative significance, such as =CEILING(-4.3, -1).

⚠

Confusing CEILING with ROUNDUP

CEILING rounds to a multiple, while ROUNDUP rounds to a number of decimal places, so swapping them gives unexpected values.

→ Use CEILING when values must land on a step like 5 or 0.25, and use ROUNDUP when you just need fewer decimal places.

⚠

Expecting exact multiples to bump up

If the number is already a multiple of the significance, CEILING returns it unchanged, which can surprise you if you expected the next step up.

→ This is the intended behavior. If you always want to move to the next multiple, add the significance first or rethink whether CEILING is the right tool.

⚠

Leaving out the significance

In Excel both arguments are required, so =CEILING(B2) on its own does not work.

→ Always supply the multiple, for example =CEILING(B2, 1) to round up to the nearest whole number.

Tips & notes

CEILING handles negative numbers based on the sign of the significance: a negative significance rounds further from zero, while pairing a negative number with a positive significance is not allowed. For consistent rounding that does not depend on matching signs, the newer CEILING.MATH function is a good alternative.

Common questions

What is the difference between CEILING and MROUND?

CEILING always rounds up to the next multiple, while MROUND rounds to the nearest multiple, which can be up or down depending on which is closer.

Why does CEILING return a #NUM! error?

That error appears when the number and significance have different signs, such as a positive number with a negative significance. Give both arguments the same sign to fix it.

How do I round up to the nearest whole number?

Use 1 as the significance, for example =CEILING(B2, 1). To round up by decimal places instead, use ROUNDUP.

Does CEILING work in Google Sheets?

Yes. Google Sheets supports CEILING with the same CEILING(value, factor) syntax, and there the factor is optional and defaults to 1.