Round Numbers Up to the Nearest 5 or 10 in Excel

Use the CEILING function to remove unwanted decimal places

Excel's CEILING function can be used to eliminate unwanted decimal places or insignificant digits in data by rounding the numbers upward to the nearest value that is considered significant. For example, the CEILING function can be used to round a number upward to the nearest 5, 10, or another specified multiple.

A multiple of a number can quickly be determined by counting by the number. For example, 5, 10, 15, and 20 are all multiples of 5. A practical use for the CEILING function is to round up the cost of items to the nearest dime ($ 0.10) to avoid having to deal with smaller change such as pennies ($ 0.01) and nickels ($ 0.05).

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

01
of 03

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.

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.

02
of 03

Excel CEILING Function

Rounding Numbers Up to the Nearest 5 or 10 with Excel's CEILING Function
Rounding Numbers Up to the Nearest 5 or 10 with the CEILING Function.

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the CEILING function is:

= CEILING (Number, Significance)

Number is the value to be rounded. 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 that will be 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 for this argument, all decimal places in the result 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).
03
of 03

CEILING Function Examples

The CEILING function can be entered by typing the function name and arguments into the desired cell, or it can be entered using the function's dialog box as outlined below.

The steps used to enter the function into cell C2 of the example are:

  1. Click on cell C2 to make it the active cell. This is where the results of the CEILING function will be displayed.
  2. Click on the Formulas tab of the ribbon menu.
  3. Choose Math & Trig from the ribbon to open the function drop-down list.
  4. Click on CEILING in the list to bring up the function's dialog box.
  5. In the dialog box, click on the Number line.
  6. Click on cell A2 in the worksheet to enter that cell reference into the dialog box.
  7. In the dialog box, click on the Significance line.
  8. Type in 0.1.
  9. Click OK to complete the function and close the dialog box.
  10. 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.

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 the above steps are repeated for cells C3 to C7, the following results are obtained:

  • 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.

#NUM! Error Value

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