Excel’s CEILING function can eliminate unwanted decimal places or insignificant digits in data by rounding up the numbers to the nearest significant value. A practical use for the CEILING function is to round up costs to the nearest dime to avoid dealing with pennies and nickels.

Changing Data With the CEILING Function

Like other rounding functions, the CEILING function alters the data in your worksheet and, therefore, affects the results of any calculations that use the rounded values.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, 2016, 2013, and 2010.

As an alternative, Excel has formatting options that allow you to change the number of decimal places displayed by your data without changing the numbers themselves. Making formatting changes to data does not affect calculations.

Excel CEILING Function

A function’s syntax refers to the function’s layout and includes its name, brackets, and arguments.

To round numbers up without specifying the amount of rounding, use the ROUNDUP function.

The syntax for the CEILING function is:

= CEILING (Number, Significance)

Number — The value to round. This argument can contain the actual data for rounding, or it can be a cell reference to the location of the data in the worksheet.

Significance —The number of decimal places present in the argument indicates the number of decimal places or significant digits present in the result (rows 2 and 3 of the example).

  • The function rounds the Number argument specified up to the nearest multiple of this value.If an integer is used, all decimal places are removed, and the result is rounded up to the nearest multiple of this value (see row 4 of the example).For negative Number arguments and positive Significance arguments, the results are rounded upward toward zero (see rows 5 and 6 of the example).For negative Number arguments and negative Significance arguments, the results are rounded downward away from zero (see row 7 of the example).

CEILING Function Example

You can enter the CEILING function by typing the function name and arguments into the desired cell, or using the function’s dialog box as outlined.

  • Select cell C2 to make it the active cell where the CEILING function results will display.
  • Select the Formulas tab.
  • Choose Math & Trig in the Function Library group.
  • Select CEILING in the list to bring up the function’s dialog box.
  • In the dialog box, select the Number line.
  • Select cell A2 in the worksheet to enter that cell reference into the dialog box.
  • In the dialog box, select the Significance line.
  • Type in 0.1.
  • Select OK to complete the function and close the dialog box. The answer 34.3 should appear in cell C2.

When you click on cell E1, the complete function = CEILING (A2, 0.1) appears in the formula bar above the worksheet.

Select cell C2 to make it the active cell where the CEILING function results will display.

Select the Formulas tab.

Choose Math & Trig in the Function Library group.

Select CEILING in the list to bring up the function’s dialog box.

In the dialog box, select the Number line.

Select cell A2 in the worksheet to enter that cell reference into the dialog box.

In the dialog box, select the Significance line.

Type in 0.1.

Select OK to complete the function and close the dialog box. The answer 34.3 should appear in cell C2.

How Excel arrives at this answer is that:

  • First, it removes one insignificant digit (2) from the end of the number. One decimal place in the Significance argument means only one decimal place in the result.Next, it rounds the remaining digit of the number up to 34.3 since this is the next highest multiple of 0.10 after 34.2.

Cell C3 to C7 Results

When you repeat the above steps for cells C3 to C7, you obtain the following results:

  • Cell C3 contains the value 34.25 because the two decimal places in the Significance argument require two decimal places in the result, and 34.25 is the next highest multiple of 0.05 after 34.22.Cell C4 contains the value 35. Because the Significance argument is an integer, all decimal places are removed from the result, and 35 is the next highest multiple of 1 after 34.Cell C5 contains the value -34.2 for the same reasons given for cell C2.Cell C6 contains the value -34 for the same reasons as cell C4.Cell C7 contains the value -35. Combining a negative Number argument and a negative integer for the Significance argument removes all decimal places and rounds the result down to the next multiple of 1 after -34.

Excel returns the #NUM! error value for the CEILING function if a positive Number argument is combined with a negative Significance argument.

Get the Latest Tech News Delivered Every Day