Round a number

Let's say you want to round a number to the nearest whole number because decimal values are not significant to you or round a number to multiples of 10 to simplify an approximation of amounts. There are several ways to round a number.

What do you want to do?

Change the number of decimal places displayed, without changing the number

On a worksheet

  1. Select the cells that you want to format.
  2. To display more or fewer digits after the decimal point, click Increase Decimal Button image or Decrease Decimal Button image on the Formatting toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).

In a built-in number format

  1. On the Format menu, click Cells, and then click the Number tab.
  2. In the Category list, click Currency, Accounting, Percentage, or Scientific.
  3. In the Decimal places box, enter the number of decimal places that you want to display.

Top of Page Top of Page

Round a number up

To do this task, use the ROUNDUP, EVEN, or ODD functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.
 
1
2
3
4
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

Top of Page Top of Page

Round a number down

To do this task, use the ROUNDDOWN function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.
 
1
2
3
4
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

Top of Page Top of Page

Round a number to the nearest number

To do this task, use the ROUND function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.
 
1
2
3
4
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

Top of Page Top of Page

Round a number to a near fraction

To do this task, use the ROUND function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.
 
1
2
3
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

Top of Page Top of Page

Round a number to a significant digit above 0

To do this task, use the ROUND, ROUNDUP, ROUNDDOWN, LEN, and INT functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.
 
1
2
3
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,5-LEN(INT(A2))) Rounds the top number up to 5 significant digits (5492900)

Function details

ROUNDUP

ROUNDDOWN

ROUND

LEN, LENB

INT

Top of Page Top of Page

Round a number to a specified multiple

To do this task, use the MROUND function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.
 
1
2
3
4
5
A B
Formula Description (Result)
=MROUND(16, 5) Rounds 16 to a nearest multiple of 5 (15)
=MROUND(-16, -5) Rounds -16 to a nearest multiple of -5 (-15)
=MROUND(2.6, 0.08) Rounds 2.6 to a nearest multiple of 0.08 (2.64)
=MROUND(5, -2) Returns an error, because 5 and -2 have different signs (#NUM!)

Function details

MROUND

Top of Page Top of Page

 
 
Applies to:
Excel 2003