Rounding numbers in Excel is a very common task. But did you know Excel has 8 different functions to do it? They’re all simple to use and share similar arguments—the number to round and the number of digits. However, depending on your chosen function, the rounding result will be different. Let's see when to use them.
INT & TRUNC – Round down to a whole number
The INT and TRUNC function chops off the decimal and always rounds down. . These functions have a single argument, the number to round down to an integer. There is a difference in the negative numbers. TRUNC doesn’t round negative numbers further down
- INT(4.1) → 4
- INT(4.99999) → 4
- TRUNC(4.99999) → 4
- INT(-4.99999) → -5
- TRUNC(-4.9999) → -4
For instance, the INT function is very useful for easily Extracting Date and Time.
ROUND – For everyday rounding
ROUND is the basic rounding function. It has been in Excel since the beginning. The function rounds numbers down when the last digit is 0 to 4, and up when it's 5 to 9. It has only two arguments:
- number: The value you want to round
- num_digits: How many digits to keep after the decimal
This function is great for rounding prices after tax, averages, or estimates.
- ROUND(4.571, 2) → 4.57
- ROUND(4.571, 1) → 4.6
- ROUND(4.571, 0) → 5
⚠️ Note: Rounding with ROUND reduces precision. Depending on your dashboard needs, this might create small differences.
But if you want to round to the nearest tenth, hundredth, or thousandth — this function does the job perfectly.
ROUNDUP & ROUNDDOWN
The ROUNDUP function always rounds up your number, even if it's close to rounding down. This is ideal for things like packaging, shipping, or increasing prices.
ROUNDUP(4.571, 2)→ 4.58- A shop wants all prices rounded up to the nearest 0.99 for marketing
=ROUNDUP(2.35, 0)-0.01→ 2.99
The ROUNDDOWN function always rounds down your number. This is ideal for calculating discounts or limits that shouldn't be exceeded.
- ROUNDDOWN(4.998, 2) → 4.99
- ROUNDDOWN(4.998, 0) → 4
- ROUNDDOWN(5.12%, 0) → 5%
CEILING – For rounding up to a specific multiple
CEILING, or CEILING.MATH return numbers rounded up, away from zero, to the nearest multiple of significance. The second argument of the function is a multiple
- number: The value you want to round
- significance: The multiple to round up to
This function is perfect for bulk orders or time blocks.
- A consultant charges at 15-minute intervals. A meeting lasted 47 minutes,
=CEILING(47, 15)→ 60 - You need to ship 97 items, and each box can hold 12
=CEILING(97, 12)→ 108 - You want to round all your prices to the next 0.5 cents
=CEILING.MATH(12.33, 0.5)→ 12.50
FLOOR – For rounding down to a specific multiple
The opposite of CEILING is FLOOR. The FLOOR function rounds down to a specific multiple.
- You want to group customer arrival times into 15-minute slots, like in a restaurant
=FLOOR(A2, 00:15)
MROUND – For rounding to the nearest multiple
The MROUND function rounds to the nearest multiple, either up or down.
- A population pyramid uses age groups in 5-year intervals to show the distribution by age and gender.
- =MROUND(27, 5) → 25
- =MROUND(28, 5) → 30
