If you don’t want unnecessary decimal places in cells because they cause ###### symbols to appear, or you don’t need accuracy down to the microscopic level, change the cell format to get the number of decimal places you want.
Or if you want to round to the nearest major unit, such as thousands, hundreds, tens, or ones, use a function in a formula.
By using a button:
- Select the cells that you want to format.
- On the Home tab, click Increase Decimal or Decrease Decimal to show more or fewer digits after the decimal point.
By applying a built-in number format:
- On the Home tab, in the Number group, click the arrow next to the list of number formats, and then click More Number Formats.
- In the Category list, depending on the type of data you have, click Currency, Accounting, Percentage, or Scientific.
- In the Decimal places box, enter the number of decimal places that you want to display.
By using a function in a formula:
Round a number to the number of digits you want by using the ROUND function. This function has only two arguments (arguments are pieces of data the formula needs to run).
- The first argument is the number you want to round, which can be a cell reference or a number.
- The second argument is the number of digits you want to round the number to.
Suppose that cell A1 contains 823.7825. To round the number to the nearest:
- Type =ROUND(A1,-3) which equals 1,000
- 823.7825 is closer to 1,000 than to 0 (0 is a multiple of 1,000 )
- Use a negative number here because you want the rounding to happen to the left of the decimal point. The same thing applies to the next two formulas that round to hundreds and tens.
- Type =ROUND(A1,-2) which equals 800
- 800 is closer to 823.7825 than to 900. We think you get the idea by now.
- Type =ROUND(A1,-1) which equals 820
- Type =ROUND(A1,0) which equals 824
- Use a zero to round the number to the nearest single digit.
- Type =ROUND(A1,1) which equals 823.8
- Use a positive number here to round the number to the number of decimal points you specify. The same thing applies to the next two formulas that round to hundredths and thousandths.
- Type =ROUND(A1,2) which equals 823.78
- Type = ROUND(A1,3) which equals 823.783
Round a number up by using the ROUNDUP function. It works just the same as ROUND, except that it always rounds a number up. For example, if you want to round 3.2 up to zero decimal places:
- =ROUNDUP(3.2,0) which equals 4
Round a number down by using the ROUNDDOWN function. It works just the same as ROUND, except that it always rounds a number down. For example, if you want to round down 3.14159 to three decimal places:
- =ROUNDDOWN(3.14159,3) which equals 3.141
Tip To get more examples, and to play around with sample data in an Excel Online workbook, see the ROUND, ROUNDUP, and ROUNDDOWN articles.
More about rounding numbers