Rounding Numbers in Excel: Round vs MRound vs Ceil vs Trunc

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 decimal places doesnt affect number.

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 with negative nr of digits.

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.

Round​Up

​=ROUND​UP(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 referenc​ing​:

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

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. T​hese 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.


You may also like

Is there a ‘minus sum’ for spreadsheets?

7 Excel Tricks for Marketers

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Join List
>