How to use the rounding function in Excel simply and effectively

Rounding numbers in Excel helps make calculations more accurate. Discover how to use the ROUND(), CEILING(), FLOOR() functions to optimize your spreadsheets.
 

Table of Contents:
1. Rounding numbers after and before the decimal point
2. Rounding to a multiple of another number
3. ROUNDDOWN() and ROUNDUP() functions
4. CEILING() and FLOOR() functions
5. EVEN() and ODD() functions
6. INT() and TRUNC() functions

1. ROUND() function - Rounds numbers after and before decimal points

Syntax:

ROUND(Number to round, num_digits)

In there

Example:

With the number 1234.5678, when you use the Round function, the result will be as shown below:

2. MROUND() function - Rounds to a multiple of another number

Syntax:

MROUND(number to round, multiple)

- Multiple : The number you need to round the number to its multiple

- If Number and Multiple have different signs, the function will report the #NUM! error.

- If Number and Multiple are equal, the result is the number itself.

- MROUND() will round up, if the divisor of Number by Multiple is greater than or equal to 1/2 Multiple , and round down if the divisor of Number by Multiple is less than 1/2 Multiple

Example:

Suppose you need to round to a multiple of another number, the result of rounding will be as illustrated below:

3. ROUNDDOWN() and ROUNDUP() functions

These two functions are essentially the same as the ROUND() function , except that they only round in one direction: ROUNDDOƯ() always rounds a number toward 0 , while ROUNDUP() always rounds a number away from 0 .

Syntax:

ROUNDDOWN(Number to round, num_digits)

ROUNDUP(number, num_digits)

Example:

You can see the comparison result between ROUNDDOWN() and ROUNDUP() functions in the Excel table below:

4. CEILING() function and FLOOR() function

These two functions are similar to the MROUND() function - rounding to the nearest multiple of a specified number, with a slight difference in the calculation method: CEILING() always rounds a number away from 0 , while FLOOR() rounds towards 0 .

Syntax:

CEILING(Number to be rounded, significance)

FLOOR(number, significance)

- Significance : The number to which you need to round the Number to a multiple of it.

- If Number and Significance have different signs, the function will report the #NUM! error.

- If Number is a multiple of Significance -> the result is that number.

Example:

Suppose we need to compare between CEILING() , FLOOR() and MROUND() - in this example, consider Significance as Multiple of MROUND() -> you will get the result as shown below:

5. EVEN() and ODD() functions

These two functions round very simply. EVEN() rounds to the nearest even integer, and ODD() rounds to the nearest odd integer. Both round away from zero.

Syntax:

EVEN(number)

ODD(number)

Example:

Suppose you need to round numbers in Excel and use 2 functions EVEN and ODD , you get the result as illustrated below:

6. INT() and TRUNC() functions

These two functions are almost the same in usage if you want to round a number to an integer.

Syntax:

INT(number)

TRUNC(number [, num_digits])

- Num_digits : Is an integer, indicating how you want to truncate the number

- Num_digits > 0 if Number is a decimal number, then Num_digits indicates the number of decimal places you want to keep (after the decimal point)

- Num_digits = 0 or not entered: remove all decimal parts of Number (if any)

- Num_digits  less than rounds  Number to an integer and rounds Number left to a multiple of 10.

The INT() function rounds a number to the nearest integer.

For positive numbers, INT() and TRUNC() give the same result ( Num_digits of TRUNC() = 0 or none), but for negative numbers, the two functions give completely different results.

Example:

INT(123,456) = 123 | TRUNC(123,456) = 123

INT(-123.456) = -124 | TRUNC(-123.456) = -123

When Num_digits is not 0, TRUNC() is different from ROUND() in that: ROUND() rounds, while TRUNC() only truncates the number but does not round.

Some examples of the TRUNC() function to help you visualize:

TipsMake has just introduced you to how to use rounding functions  in  Excel, including: ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING, FLOOR, EVEN, ODD, INT and TRUNC.

When working with Excel, understanding the rounding functions will help you calculate more accurately and conveniently. In addition, you should also learn the basic functions in Excel to improve work efficiency and process data effectively.

Jessica Tanner

Update 18 February 2025