Show All
Round a number
Change the number of decimal places displayed, without changing the number
On a worksheet
- Select the cells you want to format.
-
To display more or fewer digits after the decimal point, click Increase Decimal
or Decrease Decimal
on the Formatting
toolbar.
In a built-in number format
-
On the Format menu, click Cells, and then click the Number tab.
-
In the Category list, click Currency, Accounting, Percentage, or Scientific.
-
In the Decimal places box, enter the number of decimal places you want to display.
Round a number up
Use the ROUNDUP, EVEN, or ODD functions to do this task.
Worksheet example
The example may be easier to understand if you copy it to a blank worksheet.
How?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
A |
Data |
20.3 |
-5.9 |
12.5493 |
Formula |
Description (Result) |
=ROUNDUP(A2,0) |
Rounds 20.3 up to the nearest whole number (21) |
=ROUNDUP(A3,0) |
Rounds -5.9 up (-6) |
=ROUNDUP(A4,2) |
Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55) |
=EVEN(A2) |
Rounds 20.3 up to the nearest even number (22) |
=ODD(A2) |
Rounds 20.3 up to the nearest odd number (21) |
|
Function details
ROUNDUP
EVEN
ODD
Round a number down
Use the ROUNDDOWN function to do this task.
Worksheet example
The example may be easier to understand if you copy it to a blank worksheet.
How?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
A |
Data |
20.3 |
-5.9 |
12.5493 |
Formula |
Description (Result) |
=ROUNDDOWN(A2,0) |
Rounds 20.3 down to the nearest whole number ( 20) |
=ROUNDDOWN(A3,0) |
Rounds -5.9 down (-5) |
=ROUNDDOWN(A4,2) |
Rounds the number down to the nearest hundredth, two decimal places ( 12.54) |
|
Functions details
ROUNDDOWN
Round a number to the nearest number
Use the ROUND function to do this task.
Worksheet example
The example may be easier to understand if you copy it to a blank worksheet.
How?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
A |
Data |
20.3 |
5.9 |
-5.9 |
Formula |
Description (Result) |
=ROUND(A2,0) |
Rounds 20.3 down, because the fractional part is less than .5 (20) |
=ROUND(A3,0) |
Rounds 5.9 up, because the fractional part is greater than .5 (6) |
=ROUND(A4,0) |
Rounds -5.9 down, because the fractional part is less than -.5 (-6) |
|
Function details
ROUND
Round a number to a near fraction
Use the ROUND function to do this task.
Worksheet example
The example may be easier to understand if you copy it to a blank worksheet.
How?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
A |
Data |
1.25 |
30.452 |
Formula |
Description (Result) |
=ROUND(A2,1) |
Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3) |
=ROUND(A3,2) |
Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45) |
|
Functions details
ROUND
Round a number to a significant digit above 0
Use the ROUND, ROUNDUP, ROUNDDOWN, LEN, and INT functions to do this task.
Worksheet example
The example may be easier to understand if you copy it to a blank worksheet.
How?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
A |
Data |
5492820 |
22230 |
Formula |
Description (Result) |
=ROUND(A2,3-LEN(INT(A2))) |
Rounds the top number to 3 significant digits (5490000) |
=ROUNDDOWN(A3,3-LEN(INT(A3))) |
Rounds the bottom number down to 3 significant digits (22200) |
=ROUNDUP(A2,6-LEN(INT(A2))) |
Rounds the top number up to 5 significant digits (5492900) |
|
Function details
ROUNDUP
ROUNDDOWN
ROUND
LEN
INT