There are a ton of ways to round numbers in Excel. Ways you may not have thought of at first, like rounding to the nearest 1000 or truncating a specific number of digits.
This page is meant as an overview to find the specific rounding type that you’re looking for. Check out the table of contents below to jump to the part that’s interesting to you.
Changing the number of decimal places
The easiest way of rounding is by using the .00 <- .0 (increase decimal places) and .00 -> .0 (decrease decimal places) buttons in the Number command group on the Ribbon.
Note: If you’re not completely sure on how rounding works, here’s a summary: In general, a number with a last digit 1 up and including 4 will be rounded down. A number with last digit 5 up and including 9 will be rounded up.
You can see in the video above how a 0.7568 is rounded to 0.757, 0.76, 0.8, and then to 1. After rounding to a whole number, it cannot be rounded any further.
It’s all cosmetic
Behind the scenes, the number is still the same unrounded number that it was before. Any formulas working with this number will use the original, unrounded number for their calculations.
Changing the number of decimal places doesn’t affect number the number behind the scenes.
As you can see, this rounding method is mostly cosmetic. So use it for values that are shown to the user. If you want to round a number and use the rounded number for calculations, you should round using a function.
Rounding using a function
To round a number, you can use the ROUND function. Here’s what that looks like:
The syntax of the Round function is:
Round
=ROUND(Number,Number of digits)
Rounds Number to Number of digits decimal places.
The Round function can also be used with a negative Number of digits value. Doing so will result in rounding to the nearest 10th (for -1), 100th (for -2), 1000th (for -3), etc.
Round function with negative number of digits.
So we’ve rounded to a specific number of digits. There’s another way of rounding in Excel: to a multiple of a specific number.
Round to a specific multiple
To round to a specific number, use the MRound function. What do we mean by rounding to a number? Well, you could for example round the number 5 to the nearest multiple of 3. Which would result in 6. Because 6 is 2 x 3.
MRound looks like this:
MRound
=MROUND(Number, Multiple)
Rounds the given Number to the nearest Multiple.
So for this example it would be: =MROUND(5,3)
Round vs MRound
In short: Round will round to a specific number of digits and MRound will round to a multiple of a specific number.
Round a number up
You can use the method shown in the first section (clicking .00 -> .0) to round a decimal value up. But this will not change the underlying value. So any formulas using the resulting value will use the original unrounded value.
Also, they only work for numbers with decimal digits (behind the dot). You cannot round to the nearest 10th, 100th or 1000th, for example.
Roundup vs Ceiling
There are also two functions specifically for rounding up: ROUNDUP and CEILING.
RoundUp
=ROUNDUP(Number, Number of digits)
Rounds Number up to the given Number of digits.
Ceiling
=CEILING(Number, Significance)
Rounds Number up to a multiple of a specified number (the Significance).
The difference between the two is that the ROUNDUP function rounds the number to the specified number of digits and the CEILING function rounds the number up to the nearest multiple of the specified number.
So this is similar to what we explained in the Round vs MRound section, except in this case, both functions round the number up.
Round a number down
There are four functions that can round a number down. We understand that this can be quite complicated at first. But we’re going to explain exactly how they differ in a second.
First, let’s look at their syntaxes, for referencing:
RoundDown
=ROUNDDOWN(Number, Number of digits)
Rounds Number down to the given Number of digits.
Floor
=FLOOR(Number, Significance)
Rounds Number down to a multiple of a specified number (Significance).
Int
=INT(Number)
Rounds down the Number to the nearest whole number (called an integer).
Trunc
=TRUNC(Number, [number of digits])
Truncates Number down to a specified number of digits.
RoundDown vs Floor vs Int vs Trunc
Now, let’s look at the difference between these four.
We’ve spent some time coming up with an example that shows when all of these functions return a different value. Check it out here:
Rounddown vs Floor vs Int vs Trunc
Look at the image above and try to understand why the result is the way it is. Look at the notes to see an explanation.
Let’s compare these functions, one by one:
First, there are Floor and RoundDown. These two differ similar to how the Round and MRound differ. RoundDown rounds the number to the specified number of digits and the Floor function rounds the number down to the nearest multiple of the specified number.
Then, the Int and Trunc functions.
Int is the simplest, it’s a simple round down to the nearest whole number. Trunc is pretty easy to understand as well. It truncates down to a number of decimal places. So a Trunc to 0 decimal places will round it down to the nearest whole number, usually (!). So in that sense, the Trunc and Int functions can do the same thing. However, when the number is negative, Int will still round down (away from zero), but Trunc will result in a rounding up (towards zero).
That’s it for rounding in Excel
We’ve discussed almost every aspect of rounding numbers in Excel. It’s not super difficult, but it can be overwhelming with the number of rounding functions Excel has to offer.
We hope that we’ve improved your Excel knowledge with this post. If you have any more rounding issues or questions, you can leave them in the comments below.