How to round numbers in Excel?

For rounding the numbers e.g. 45.135785 to 45.14, you may use the built-in functions in Excel.

The Excel has a number of functions that enable rounding the decimal numbers as per the need. These functions are:

  • ROUND
  • ROUNDUP
  • ROUNDDOWN
  • MROUND

Let us look at the syntax/examples of using these functions one by one.

The Excel ROUND function

The general syntax of ROUND function is:

ROUND(number, num_digits)

  • The number argument is the decimal number to be rounded. It can be a number, cell reference or decimal number from other function.
  • num_digits specifies the numbers after the decimal point.

The example of ROUND in Excel

In the following example, the given number is rounded to 2 decimal points. The number contains five numbers after the decimal point.

ROUND formula:

=ROUND(A2,2)

The result of rounding 124.45712:

Excel ROUND function

Rounding a given number to one decimal place

The formula of ROUND:

=ROUND(A2,1)

Excel ROUND one

How to round a number nearest multiple of 1000 by ROUND function?

The following formulas will round the number to the nearest 1000 by using ROUND function. For that, I entered different numbers in A column and used the ROUND formula in B column:

The formula for nearest 1000:

=ROUND(A2,-3)

The resultant sheet:

ROUND nearest 1000

You can see various numbers rounded to nearest 1000 by using -3 value for the num_digits argument.

Nearest 100 formula using ROUND

The formula for getting the nearest hundred is:

=ROUND(A4,-2)

ROUND nearest 100

The same formula is applied from C2 to C8 cells by changing cell reference in ROUND function.

Using ROUND for nearest 10

Similarly, you may use the -1 value in the ROUND function to get the nearest 10 value. Have a look:

Get ROUND nearest 10

The formula for nearest 10:

=ROUND(A4,-1)

In the same way, you may get the nearest multiple of 10000 by using -4 e.g.:

=ROUND(16000,-4)

Result: 20000

and using -5 can be used for 100000.

Getting the nearest integer example

By using 0 value, you may get the nearest integer value of the given decimal number:

Formula:

=ROUND(525.256,0)

Result = 525

An example of using ROUNDUP function

For getting the round up number i.e. away from 0, use the ROUNDUP function. See an example with different numbers:

Get ROUNDUP

The formula used in B3 cell:

=ROUNDUP(A3,0)

The result for 14.23 is 15. If we used ROUND function, the result would have been 14.

The example of ROUNDDOWN function

To round the number towards zero, use the ROUNDDOWN function. See the example formula and output below using the same numbers as in ROUNDUP function:

Excel ROUNDDOWN

The formula used in B2 cell:

=ROUNDDOWN(A2,0)

You can see the difference by comparing both result sheets. In ROUNDOWN case, the result for 15.52 is 15 while ROUND would have produced 16.

The MROUND function

Although, I showed you examples of rounding a number to a specific multiple by using ROUND function. For example, nearest multiple of 10, 100, 1000 etc.

The specific function for getting the nearest multiple is MROUND in Excel. The following example shows using MROUND for getting the nearest multiple of 5, 10, 100, 1000, and 10000. See the formulas and output sheet:

Excel MROUND

The nearest multiple of 5 in B2 cell formula:

=MROUND(A2,5)

10 in B3 cell:

=MROUND(A3,10)

100 in B4 cell:

=MROUND(A4,100)

1000 in B5:

=MROUND(A5,1000)

10000 in B6:

=MROUND(A6,10000)

You may check the formula and compare the numbers in above excel sheet.