NEW! FREE 90 Minutes Excel Charts Course for Reporting Watch now 


How to use Ceiling Function in Excel

2 Mins read
Ceiling Function in Excel

The CEILING Function in Excel returns a given number rounded up to a specified multiple. CEILING Function is always used for rounding up. Its purpose is to Round a number up to nearest multiple. It returns a rounded number.

  • What is a CEILING Function in Excel?
  • What is the syntax for CEILING Function?
  • Where can be CEILING Function used?
  • What is the difference between CEILING Function and CEILING.MATH Function?

➡ What is a Ceiling Function in Excel?

The CEILING function in Excel rounds up a number to a given multiple. CEILING function can be used to set the pricing after currency conversion, discounts, etc. It works similar to the MROUND function, but the only difference is that CEILING always rounds up. CEILING function is a sub-category of the Compatibility Functions in Excel.


➡ What is the Syntax for Ceiling Function?

The syntax for CEILING function is very simple and easy to understand.



            Number – The number that should be rounded

            Significance – The multiple to use when rounding

The formula for CEILING Function can be written as

=CEILING(A1,1)  It will round the value in cell A1 upto the number 1.

Where can be Ceiling Function used?

CEILING Function is an advanced excel function can be used to round the time in hours, minutes or seconds upto their nearest interval. Time is a very important measure and used everywhere. Therefore, CEILING Function plays a very important role as it is used for rounding time. Likewise, it is also used to roundup the dates. Also, other applications of CEILING Function are to round up to the bundle sizes, randomly assign people to groups or teams of a specific size etc.


Let’s take a simple example to understand the working of CEILING Function. As shown in below picture we have positive and negative numbers in the data table. We have also given a significance for rounding the numbers.

Here, the first number is 50 and the significance is 5. But we all know that 50 is the exact multiple of 5. That’s why we get the output as 50.

The next number is 68 and the significance is 3. The output we get here is 69, because 69 is the multiple of 3 which is nearest to 68.

In this way, CEILING Function helps in rounding the numbers.

Ceiling Function in Excel

➡ What is the difference between Ceiling Function and Ceiling.Math Function?

The difference between CEILING Function and CEILING.MATH Function is as follows:

  • Unlike the CEILING.MATH Function, the CEILING Function does not provide a default multiple of 1 and -1 for positive numbers and negative numbers
  • It provides more control for rounding negative numbers than CEILING Function
  • MATH Function uses the absolute value of the significance argument. Altering the sign of significance does not affect the result
  • CEILING Function falls under the category of Compatibility functions whereas CEILING.MATH Function falls under the category of Math & Trig Functions


Recommended Blog:

➡ Top 3 Points to Remember:

    • Rounding does not occur in CEILING Function if the number is an exact multiple of significance
    • If number and significance are both negative in CEILING Function, it returns a value that will round down, away from zero
    • When the number is negative and significance is positive in CEILING Function, the value will round up towards zero

➡ Conclusion

Here, we come to know about the function. Its purpose, its uses, the formula to be use, where can we find this function. Hope your concept regarding the CEILING Function is clear and you can now carry out this function easily.

Related posts
ExcelExcel VBA Code

VBA Code to Split table by Columns

3 Mins read
Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window. Step 2: In the Visual…
ExcelExcel VBA Code

Create A Table Of Contents By VBA Code

1 Mins read
Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window. Step 2: In the Visual…
ExcelExcel VBA Code

Save Each Worksheet as a Separate PDF

1 Mins read
Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window. Step 2: In the Visual…