CEILING Function

Returns a number rounded up to a supplied number that is away from zero to the nearest multiple of a given number

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets. Start Free

What is the Excel CEILING Function?

The Excel CEILING function[1] is categorized under Math and Trigonometry functions. The function will return a number that is rounded up to a supplied number that is away from zero to the nearest multiple of a given number. MS Excel 2016 handles both positive and negative arguments.

The function is very useful in financial analysis as it helps in setting the price after currency conversion, discounts, etc. When preparing financial models, CEILING helps us round up the numbers as per the requirement. For example, if we provide =CEILING(10,5), the function will round off to the nearest 5 dollars.


=CEILING(number, significance)

The function uses the following arguments:

  1. Number (required argument) – This is the value that we wish to round off.
  2. Significance (required argument) – This is the multiple that we wish to round up to. It would use the same arithmetic sign (positive or negative) as per the provided number argument.

How to use the CEILING Function in Excel

To understand the uses of the CEILING function, let’s consider a few examples:

Example 1 – Excel Ceiling

Let’s see the results from using the function when we provide the following data:

CEILING Function

The formula used and results in Excel are shown in the screenshot below:

CEILING Function - Example 1

Example 2 – Using CEILING with other functions

We can use CEILING to highlight rows in a group of data. We will use a combination of ROW, CEILING, and ISEVEN functions. Suppose we are given the data below:

Excel CEILING Function - Example 2

If we wish to highlight rows in groups of 3, we can use the formula =ISEVEN(CEILING(ROW()-4,3)/3) in conditional formatting.

CEILING Function - Example 2a

We will get the result below:

CEILING Function - Example 2b

In applying the function, remember to select the data before clicking on conditional formatting.

First, we “normalized” the row numbers to begin with 1, using the ROW function and an offset. Here 3 is n (the number of rows to group) and 4 is an offset to normalize the first row to 1 as the first row of data is in row 6. The CEILING function rounds incoming values up to a given multiple of n. Essentially, the CEILING function counts by a given multiple of n.

CEILING Function - Example 2c

The count shown above in column G is then divided by n, that is in this case =3. Lastly, the ISEVEN function is used to force a TRUE result for all even row groups, which triggers the conditional formatting.

Notes about the Excel CEILING Function

  1. #NUM! error – Occurs when:
    • If we are using MS Excel 2007 or earlier versions, provide the significance argument with a different arithmetic sign from the given number argument.
    • If we are using MS Excel 2010 or 2013, if the given number is positive and the supplied significance is negative.
  2. #DIV/0! error – Occurs when the significance argument provided is 0.
  3. #VALUE! error – Occurs when any of the arguments is non-numeric.
  4. CEILING is like MROUND but it always rounds up away from zero.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

Article Sources

  1. CEILING Function
0 search results for ‘