Excel CEILING Function - Formula, Examples, How to Use?

What Is CEILING Function In Excel?

The CEILING function in Excel rounds up the numeric value from the dataset to its immediate or closest multiple as required or set in the formula. Users can use it to perform tasks, such as setting the rates after currency conversions during financial analysis.

The Excel CEILING function is an inbuilt Math & Trig function, so we can enter the formula directly in the worksheet or insert it from the “Function Library”. However, the CEILING.MATH() is replaced in Excel version 2013, and CEILING() is now available as a Compatibility function in Excel. For example, the below table shows a list of input values and the number rounding-up requirements.

📥Download the ready-to-use Excel template to practice this tutorial yourself.

Download Excel Template

Frequently Asked Questions (FAQs)

1. Where is the CEILING function in Excel?

The CEILING function in Excel is in the Formulas tab. Click Formulas → More Functions → Compatibility → CEILING to access it.

2. How to use the CEILING() in Excel VBA?

We can use the CEILING() in Excel VBA as follows:
The following table contains values for the two CEILING() arguments, number, and significance.

Step-by-Step procedure to update column C with the rounded-up numbers using the Excel CEILING function and VBA Macro are:

1: In the worksheet with the above table, press the keyboard shortcut Alt+F11 to open the VBA Editor window.

2: Choose the required VBA Project from the left menu in the VBA Editor window and click Insert → Module to open the Module1 window.

The module window opens, as shown below.

3: Enter the VBA code containing the CEILING function in the Module1 window, as shown in the image below.

Sub Ceiling_fn()
Dim rv As Worksheet
Set rv = Worksheets(“FAQ2”)
rv.Range(“C2”) = Application.WorksheetFunction.Ceiling_Math(rv.Range(“A2”), rv.Range(“B2”))
rv.Range(“C3”) = Application.WorksheetFunction.Ceiling_Math(rv.Range(“A3”), rv.Range(“B3”))
rv.Range(“C4”) = Application.WorksheetFunction.Ceiling_Math(rv.Range(“A4”), rv.Range(“B4”))
End Sub

4: Click the Run Sub/UserForm icon to run the code.

The output is shown below. Once we run the code, the rounded-up values generate in the target cells.

[Note: The CEILING.MATH() replaced CEILING() in Excel version 2013. So, the VBA code uses the “Application.WorksheetFunction.Ceiling_Math()” to round up the values.]

3. Why is the CEILING function not working in Excel?

A few reasons the CEILING function is not working in Excel are:
· The supplied CEILING() arguments are non-numeric.
· One of the arguments, i.e., the significance argument, maybe 0.
· The number argument is positive, and the significance argument is negative.

4. What is the use of the CEILING function in Excel?

The use of the CEILING function in Excel is that it rounds up the given numeric value to the immediate next multiple of the specified significance.
For example, we can set up the price values after currency conversions.

Download Template

Download the ready-to-use Excel template to practice this tutorial yourself.