How to Round Numbers in Excel using:

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Microsoft Excel offers a few different options for rounding numbers.

The most generally useful and versatile of these is the ‘ROUND’ function.

There are also a couple of other more specialized functions useful for rounding in Excel…

… These are the functions ‘ROUNDUP’ and ‘ROUNDDOWN’.

The good news is you’re going to learn how to handle all of them. And don’t worry, it’s super easy!

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

How to round up in Excel
using the function ROUNDUP

Let’s first turn our attention to one of the more specialized rounding functions: ‘ROUNDUP’.

This function is useful when you need to round up numeric data.

Kasper Langmann, Co-founder of Spreadsheeto

Quick example:

If you have a value that’s supposed to be rounded down, Excel will instead round the number up.

So, ’24’ would normally be rounded to the nearest tens with the expected result of ‘20’.

But, when using the ‘ROUNDUP’ function, the result is ’30’!

There are syntax rules that you must follow when rounding in Excel.

Let’s look at the syntax for ‘ROUNDUP’.

‘=ROUNDUP(number, num_digits)’

There are two arguments in all three of the round functions. Microsoft has made it easy for us since the arguments are the same for each function!

Kasper Langmann, Co-founder of Spreadsheeto

The first argument is simply the number we wish to round.

This can be a literal number value in the function or a cell reference to a value we want to round.

The second argument is ‘num_digits’. This is a number value.

It indicates the number of digits to the left/right of the decimal place we would like to round our ‘number’ argument.

  • Positive numbers are places to the right of the decimal.
  • Negative numbers are places to the left of the decimal.

To better understand the concept of the ‘num_digits’ argument, let’s look at some examples!

Kasper Langmann, Co-founder of Spreadsheeto

First, let’s see the different results we get by applying the ‘ROUNDUP’ function to the same number value – but with different ‘num_digits’ values.

ROUNDUP function

Notice that for the number ‘132.54’, we start with a ‘num_digits’ of ‘2’ which is two places to the right of the decimal point.

This means that we want ‘ROUNDUP’ to round our number up based on the value that would be three places to the right of the decimal point.

Kasper Langmann, Co-founder of Spreadsheeto

But, our original value doesn’t go out to that many decimal places. The result of our formula is simply the original value.

Once we change our ‘num_digits’ argument to 1 in the next row, we now see ‘ROUNDUP’ in action.

Now, the value changes from ‘132.54’ to ‘132.6’.

At zero decimal places, a ‘num_digits’ value of ‘0’, our original value is now rounded up to ‘133’ – the nearest integer.

We can even go beyond zero decimal places and into negative values. This simply means “that many spaces to the left of the decimal point”.

Kasper Langmann, Co-founder of Spreadsheeto

For example, on row 10, our ‘num_digits’ argument is ‘-2’.

This tells ‘ROUNDUP’ to round our original value of ‘406.5’ to the next highest hundredths or ‘500’.

How to round down in Excel using
the function ROUNDDOWN

Now we have covered how to round up in Excel.

Let’s see exactly how you round down in Excel.

Luckily, it follows the same logic as before.

The syntax is the same requiring the ‘number’ and ‘num_digits’ arguments.

‘=ROUNDDOWN(number, num_digits)’

Let’s look at some ‘ROUNDDOWN’ examples!

The function ROUNDDOWN

Look at column B to make sure the concept of rounding using the ‘ROUNDDOWN’ function makes sense.

On row 5 the ‘num_digits’ argument is ‘-1’. The ‘5’ in the tenths place is the criteria digit the rounding is based on.

This is somewhat misleading. ‘ROUNDDOWN’ and ‘ROUNDUP’ override the value of the digit that the rounding would normally be based on.

Kasper Langmann, Co-founder of Spreadsheeto

So, since we are using ‘ROUNDDOWN’ the result on row 5 is ’130’ whereas with ‘ROUNDUP’ it was ’140’.

Round to your choice of decimals
using the function ROUND

Now we’ve have taken a quick look at the special round functions ‘ROUNDUP’ and ‘ROUNDDOWN’.

Let’s look at the more versatile and flexible ‘ROUND’ function.

There are special cases in which ‘ROUNDUP’ and ‘ROUNDDOWN’ are the best fit.

But, the ‘ROUND’ function follows the typical mathematical needs of general rounding.

The syntax for ‘ROUND’ is like that for ‘ROUNDDOWN’ and ‘ROUNDUP’:

‘=ROUND(number, num_digits)’

For all digits 1 through 4, the ‘ROUND’ function rounds down. While for all digits 5-9 it rounds up.

Kasper Langmann, Co-founder of Spreadsheeto

So, let’s look at the number ‘141.138’ in a few examples.

In our first example, we choose ‘3’ as our ‘num_digits’ argument.

Our function returns a result of ‘141.14’ since the value in the thousandths place is ‘8’.

ROUND function

On row 3, we selected ‘0’ for the ‘num_digits’ argument. The function rounds to the nearest integer, in this case, ‘141’.

On row 4 of the table, selecting a ‘num_digits’ argument of ‘-1’ rounds to the nearest ‘10’. This results in rounding down to ‘140’.

And finally, on row 5, we have selected a ‘num_digits’ argument of ‘-2’. This rounds down to the nearest ‘100’ based on the ‘4’ two places to the left of the decimal.

Practical application of ‘ROUND’

The ‘ROUND’ function is a very simple one in the arsenal of Excel tools.

But when exactly would such a function be useful?

Here’s when…

When performing mathematical calculations in Excel, results don’t always appear correct. This is because of the constraints of formatting.

Kasper Langmann, Co-founder of Spreadsheeto

To illustrate, let’s say we have calculated the sales commission of four salespersons.

Our dollar amounts are formatted to show our results to the penny (or 2 decimal places).

When the sales amounts are multiplied by the commission rate something happens behind-the-scenes.

What we cannot see is that Excel has made the calculations to a precision level beyond the two decimal places that are visible.

Because of this, sometimes calculations like a sum will appear to be in error.

Sum discrepancy

If we take our commission calculations and compare it to the total of $299.73, our total is a penny higher than it should be. This is going to freak the accounting department out and we can’t let that happen!

Kasper Langmann, Co-founder of Spreadsheeto

Let’s see what happened here and then look at how to use the round function in Excel to fix it.

First, we will look at our commission calculations to three decimal places.

Three decimal places

So, what first appears to be a sum of 0 + 0 + 1 + 1 in the tenths place is actually a sum of 03 + 04 + 12 + 12 as far as Excel is concerned.

This comes to 33.

That is why the sum comes out to a 3 in the tenths place rather than a 2.

*See the manual column for reference. Here the commission figures were keyed in manually to just two decimal places.

This is a very common issue in Excel. So, the use of the ‘ROUND’ function comes in very handy as a remedy for this problem.

Kasper Langmann, Co-founder of Spreadsheeto

Place the commission calculation formula in the ‘number’ argument of the ‘ROUND’ function.

Select ‘2’ for our ‘num_digits’ argument, so each one rounds to the nearest penny.

This is what we have done below so our sum calculation now appears to be arithmetically correct.

Using ROUND to fix sum discrepancy

Conclusion: your next steps

You’ve now seen how easy it is to round in Excel.

You’ve learned about two functions that round up and down in an absolute fashion.

You’ve also seen how rounding in Excel can be performed up – or down – to the number of decimal places we choose.

These are just a few of the functions that any Excel user should be aware. I’ve included many more awesome functions in my free Excel training, which you can sign up to right below. Check it out!

Kasper Langmann, Co-founder of Spreadsheeto