The CEILING function — round up to the next multiple, every time.
CEILING(number, significance) rounds away from zero to the next multiple of significance. The “never under-charge” function — perfect for pricing ladders, pack-size minimum orders, and billable-time rounding. Unlike MROUND, it has no sign restriction. For fine-grained control over how negative numbers round, use the modern CEILING.MATH variant (§4).
| Input | MROUND(x, 5)nearest | CEILING(x, 5)always up | FLOOR(x, 5)always down |
|---|---|---|---|
| 12 | 10 | 15 | 10 |
| 13 | 15 | 15 | 10 |
| 17.5 | 20 | 20 | 15 |
| 22 | 20 | 25 | 20 |
| 47.5 | 50 | 50 | 45 |
| -17 | #NUM! | -15 | -20 |
How to use CEILING
- Type
=CEILING(in the cell where you want the rounded-up value. - Pass the source number, then the significance— the multiple to round up to.
=CEILING(A2, 5)rounds up to the next 5. - Close the parenthesis and press Enter. CEILING never returns a value below the input (for positive numbers).
- For nearest-multiple (not always-up), use MROUND. For always-down, use FLOOR.
Mental model. Stand on the number line at your input value. Walk away from zero until you land on the next multiple of significance. That landing spot is CEILING's answer. For positive inputs, that's walking up; for negatives, that's walking up too (toward zero) — unless you use CEILING.MATH with mode=1 to walk farther down.
CEILING syntax and arguments
CEILING(number, significance) — both required.CEILING.MATH(number, [significance], [mode]) — last two optional.
5, 10, 12 (dozens), 15 (minutes), 0.05 (cents), 0.25 (quarters). In CEILING.MATH this defaults to 1 if omitted.number. 0 (default) rounds toward zero (matches plain CEILING). 1 rounds away from zero (more negative). Has no effect on positive numbers.| Input | Formula | Result | Why |
|---|---|---|---|
12.4 | =CEILING(12.4, 5) | 15 | Next multiple of 5 above 12.4 |
15 | =CEILING(15, 5) | 15 | Already a multiple — no change |
23.7 | =CEILING(23.7, 5) | 25 | Next 5 above 23.7 |
7 | =CEILING(7, 12) | 12 | Round up to next dozen |
0.347 | =CEILING(0.347, 0.05) | 0.35 | Decimal significance works the same way |
-12 | =CEILING(-12, 5) | -10 | Toward zero (less negative) — see §4 for control |
CEILING examples
The same function, four common shapes of rounding-up problem.
Example 1: pricing tiers
Round costs up to the next $5 so the price ladder reads cleanly. $12.40 → $15; $23.70 → $25. CEILING is the right choice here because under-pricing isn't acceptable.
Example 2: pack-size minimum orders
Inventory comes in packs of 12. You need 7 — order 1 pack (12). You need 25 — order 3 packs (36). CEILING snaps demand up to the next valid pack quantity.
Example 3: billable-time rounding
Professional services often bill in 15-minute increments — and always round up. 7 min → 15 min; 18 min → 30 min. CEILING is standard for this; MROUND would under-charge for short tasks.
Example 4: CEILING.MATH for negative control
Plain CEILING for -17 with significance 5 returns -15 — that's “up” in the toward-zero sense. If you want “up” in the away-from-zero sense (deeper negative), use =CEILING.MATH(A2, 5, 1) → -20. The 3rd argument mode is the switch.
CEILING.MATH and the negative-number rule
CEILING's behaviour on negatives surprises most people. CEILING.MATH gives you the explicit knob.
For positive numbers, “up” is unambiguous — away from zero is also the larger value. For negative numbers, the two diverge. =CEILING(-17, 5) returns -15 — that's “toward zero”, which Excel calls “up” in numerical terms (a less-negative number is larger). But intuitively, users often mean “round further from zero”, which would give -20.
=CEILING(-17, 5) → -15 (toward zero)CEILING.MATH mode=0 (default)
=CEILING.MATH(-17, 5, 0) → -15 (same as plain)CEILING.MATH mode=1
=CEILING.MATH(-17, 5, 1) → -20 (away from zero — “floor” in absolute terms)For positive numbers, the mode argument has no effect. CEILING.MATH is otherwise a strict superset of CEILING and can replace it in any formula. CEILING.MATH was added in Excel 2013; if you need to support Excel 2010 or older, stick with CEILING.
CEILING vs MROUND, FLOOR & ROUNDUP
Four functions with subtly different jobs. Pick by direction (up/down/nearest) and unit (multiple/decimal).
| Function | Direction | Unit | Typical use |
|---|---|---|---|
| CEILING | Always up (away from zero) | Multiple | Pricing tiers, pack sizes, billable time |
| MROUND | Nearest (ties away from zero) | Multiple | Display rounding to clean multiples |
| FLOOR | Always down (toward zero) | Multiple | Binning, conservative quotes |
| ROUNDUP | Always up (away from zero) | Decimal place | Round to 2 decimals, next integer |
Rule of thumb: CEILING when the target is a multiple of 5/12/0.05 etc., ROUNDUP when the target is a decimal place. The two overlap for powers of 10 — =CEILING(A, 10) and =ROUNDUP(A, -1) are equivalent.
CEILING frequently asked questions
6.01What does CEILING do in Excel?▸
CEILING(number, significance) rounds the first argument AWAY from zero to the next multiple of the second. =CEILING(12, 5) returns 15. =CEILING(15, 5) returns 15 (already a multiple). For negatives, plain CEILING rounds toward zero — =CEILING(-12, 5) returns -10. Use it for pricing tiers, pack-size minimum orders, or billable-time rounding (always round up to next 15 minutes).
6.02What's the difference between CEILING and CEILING.MATH?▸
CEILING (the original, since Excel 97) has quirks around negatives. CEILING.MATH (introduced in Excel 2013) adds an optional 3rd argument mode that lets you choose negative-number behaviour: mode=0 (default) rounds toward zero, mode=1 rounds away from zero. =CEILING.MATH(-17, 5, 0) returns -15; =CEILING.MATH(-17, 5, 1) returns -20. Use CEILING.MATH whenever you need explicit control.
6.03How is CEILING different from MROUND?▸
CEILING always rounds UP; MROUND picks the NEAREST. =CEILING(12, 5) returns 15. =MROUND(12, 5) returns 10 (12 is closer to 10). Use CEILING when you must never end up below the input (pricing, billable time, pack sizes). Use MROUND when “closest” is what you want.
6.04How do I round up to the nearest 10, 100, or 0.05?▸
Pass the multiple as the second argument. =CEILING(A1, 10) rounds up to nearest 10; =CEILING(A1, 100) to nearest 100; =CEILING(A1, 0.05) to the next nickel. The pattern works for any positive significance — 25, 0.25, 0.1 all behave identically. For decimal-place rounding (not multiples), use ROUNDUP instead.
6.05Does CEILING work in Google Sheets?▸
Yes — Google Sheets has CEILING and CEILING.MATH with identical syntax and behaviour to Excel. =CEILING(A1, 5) works the same on both. Sheets actually accepted the modern CEILING.MATH-style negative-mode argument on CEILING itself earlier than Excel did, but using CEILING.MATH is more portable.
6.06What's the difference between CEILING and ROUNDUP?▸
Both round away from zero — but to different units. CEILING rounds to a MULTIPLE: =CEILING(12.4, 5) returns 15. ROUNDUP rounds to a DECIMAL place: =ROUNDUP(12.4, 0) returns 13 (next integer). =ROUNDUP(12.4, -1) returns 20 (next multiple of 10). For nearest 5/15/0.05 use CEILING; for nearest 1/10/100/0.01 either works. CEILING's syntax is more direct when the unit isn't a power of 10.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.