Rounding Numbers in Excel

Round numbers to a specified number of digits

In Excel, the ROUND function is used to round numbers to a specified number of digits. It can round on either side of a decimal point. When it does this, it alters the value of the data in the cell—unlike formatting options that allow you to change the number of decimal places displayed without actually changing the value in the cell. As a result of this change in data, the ROUND function affects the results of calculations in the spreadsheet.

01
of 02

The ROUND Function's Syntax and Arguments

Excel ROUND function
© Ted French

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

The syntax for the ROUND function is:

= ROUND ( Number, Num_digits )

The arguments for the function are Number and Num_digits:

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. It is a required element.

Num_digits is the number of digits that the Number argument will be rounded to. It is also required. 

  • If the Num_digits argument is set to zero, the function rounds the value up or down to the nearest integer.
  • If the Num_digits argument is greater than zero, the value is rounded to the specified number of decimal places.
  • If the value of the Num_digits argument is set to 2, the function leaves two digits to the right of the decimal point and rounds the last digit up or down to the next number.
  • If the Num_digits argument is negative, all decimal places are removed and the function rounds up or down that number of digits to the left of the decimal point.
  • If the value of the Num_digits argument is set to -2, the function remove all digits to the right of the decimal point and rounds the first and second digits to the left of the decimal point up or down to the nearest 100.

Note: If you always want to round numbers up, use the ROUNDUP function. If you always want to round numbers down, use the ROUNDDOWN function. 

02
of 02

ROUND Function Example

The image accompanying this article displays examples for a number of results returned by Excel's ROUND function for data in column A of a worksheet.

The results, shown in column C, depend upon the value of the Num_digits argument.

Options for Entering the ROUND Function

For example, to reduce the number 17.568 in cell A5 in the image to two decimal places using the ROUND function, options for entering the function and its arguments include:

  • Typing the complete function: =ROUND(A5,2) into cell C5 in the worksheet
  • Selecting the function and arguments using the function's dialog box.

Although it is possible to type the complete function by hand, many people find it easier to use the dialog box to enter a function's arguments.

How to Use the Dialog Box

For this example, open an Excel spreadsheet and enter the values in column A of the image into the corresponding column and rows of the spreadsheet.

To use the dialog box to enter the ROUND function into cell C5:

  1. Click on cell C5 to make it the active cell. This is where the results of the ROUND 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 ROUND 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 A5 in the worksheet to enter that cell reference into the dialog box.
  7. Click on the Num_digits line.
  8. Type a 2 to reduce the value in A5 to two decimal places.
  9. Click OK to close the dialog box and return to the worksheet.

The answer 17.57 should appear in cell C5. When you click on cell C5, the complete function =ROUND(A5,2) appears in the formula bar above the worksheet.

Why the ROUND Function Returned 17.57

Setting the value of the Num_digits argument to 2 reduces the number of decimal places in the answer from three to two. Because Num_digits is set to 2, the 6 in the number 17.568 is the rounding digit.

Since the value to the right of the rounding digit—the number 8—is greater than 4, the rounding digit is increased by one giving a result of 17.57.