What is CEILING in Excel?

The CEILING function in Excel is a useful mathematical function that rounds a number up to the nearest multiple of a specified number. It is commonly used when you need to round prices, quantities, measurements, or other numerical values to a specific increment.

For example, if you want to round 23 to the nearest multiple of 5, the CEILING function returns 25.

Whether you are a student, office professional, accountant, or beginner learning Microsoft Excel, understanding the CEILING function can make numerical calculations easier and faster.

What Does the CEILING Function Do in Excel?

The CEILING function rounds a number upward to the nearest multiple of a given significance.

For example:

• 21 rounded up to the nearest 5 = 25
• 26 rounded up to the nearest 10 = 30
• 103 rounded up to the nearest 50 = 150

The important point is that CEILING generally moves the number up to the next required multiple rather than simply rounding it to the nearest value.

CEILING Function Syntax

The basic syntax of the CEILING function is:

=CEILING(number, significance)

Arguments:

number:
The number that you want to round up.

significance:
The multiple to which you want to round the number.

Example:

=CEILING(23,5)

Result:

25

Here, Excel rounds 23 upward to the nearest multiple of 5.

CEILING Function Examples in Excel

Let's understand the CEILING function with some simple examples.

Example 1: Round a Number to the Nearest 10

Formula:

=CEILING(43,10)

Result:

50

Because 50 is the next multiple of 10 after 43.

Example 2: Round a Number to the Nearest 5

Formula:

=CEILING(27,5)

Result:

30

The multiples of 5 around 27 are 25 and 30. Since CEILING rounds upward, the result is 30.

Example 3: Round a Number to the Nearest 100

Formula:

=CEILING(325,100)

Result:

400

Example 4: Using Cell References

Suppose cell A2 contains 37 and cell B2 contains 10.

You can use:

=CEILING(A2,B2)

Result:

40

This method is particularly useful when working with large Excel tables.

CEILING Function Example with an Excel Table

This type of calculation can be useful in inventory management, billing, packaging, and business calculations.

How to Use CEILING in Excel

Follow these simple steps:

1. Open Microsoft Excel.
2. Enter a number in a cell.
3. Enter the required multiple in another cell.
4. Select the cell where you want the result.
5. Type the CEILING formula.
6. Press Enter.

For example:

=CEILING(A2,5)

Excel will round the value in A2 upward to the nearest multiple of 5.

Practical Uses of the CEILING Function

The CEILING function can be useful in many real-world situations.

1. Inventory Management

If products are packed in boxes of 10, you can use CEILING to determine how many boxes are required.

For example:

=CEILING(47,10)

Result:

50

This means you need capacity for 50 units.

2. Pricing and Billing

CEILING can be used when prices or charges need to be rounded upward to a fixed increment.

3. Packaging

If an item must be packed in groups of 20, CEILING can help calculate the required quantity.

4. Business Calculations

Businesses can use CEILING to round quantities, units, or measurements to predefined intervals.

5. Working with Time

CEILING can also be useful in calculations where values need to be rounded to a specific interval.

CEILING vs ROUND in Excel

The CEILING and ROUND functions are different.

ROUND rounds a number to the nearest value based on standard rounding rules.

CEILING rounds a number upward to the specified multiple.

For example:

=ROUND(23,0)

Result:

23

But:

=CEILING(23,5)

Result:

25

Therefore, use ROUND when you need standard rounding and CEILING when you need to move a value upward to a particular multiple.

CEILING vs FLOOR in Excel

CEILING and FLOOR perform opposite types of rounding.

• CEILING rounds a number upward to a specified multiple.
• FLOOR rounds a number downward to a specified multiple.

For example:

=CEILING(27,5) → 30

=FLOOR(27,5) → 25

Understanding the difference between these two functions is important when working with quantities and business calculations.

CEILING Function in Newer Versions of Excel

Modern versions of Excel also provide functions such as CEILING.MATH and CEILING.PRECISE.

For many everyday calculations, the following functions may be useful:

=CEILING.MATH(number, significance)

=CEILING.PRECISE(number, significance)

If you are using a newer version of Microsoft Excel, you may prefer these newer functions depending on your calculation requirements.

Common Mistakes When Using CEILING

Beginners sometimes make mistakes while using the CEILING function.

Mistake 1: Forgetting the Significance

Incorrect:

=CEILING(25)

Correct:

=CEILING(25,5)

Mistake 2: Confusing CEILING with ROUND

CEILING is not the same as normal rounding. It is designed to round a value to a specified multiple.

Mistake 3: Using the Wrong Multiple

Always make sure that the significance value matches your requirement.

For example, if products are sold in packs of 10, use:

=CEILING(A2,10)

Frequently Asked Questions About CEILING in Excel

What is the CEILING function in Excel?

The CEILING function rounds a number up to the nearest multiple of a specified significance.

What is the formula for CEILING in Excel?

The basic formula is:

=CEILING(number, significance)

What does CEILING(23,5) return?

=CEILING(23,5) returns 25.

What is the difference between CEILING and ROUND?

ROUND rounds a number according to standard rounding rules, while CEILING rounds a number upward to a specified multiple.

What is the difference between CEILING and FLOOR?

CEILING rounds upward to a specified multiple, while FLOOR rounds downward.

Is CEILING available in Excel 365?

Yes. Microsoft Excel 365 supports CEILING-related functions, including CEILING.MATH and CEILING.PRECISE.

Conclusion

The CEILING function in Excel is a simple but powerful function for rounding numbers upward to a specified multiple. It is especially useful for inventory, packaging, pricing, billing, business calculations, and quantity calculations.

The basic formula is:

=CEILING(number, significance)

For example:

=CEILING(23,5)

returns 25.