How to Round Off Numbers in Excel [9 Methods]

In a decimal fraction number, having extra digits after the decimal point is not always necessary. Maybe you don’t need that much additional precision or maybe the decimal places are not fitting right within the cell width. So Excel is showing you #### in the cell. The reasons could be anything. So as part of an escape from this problem, you are probably thinking about rounding off numbers in Excel. If it is, then read the whole article. Because you are going to get a complete guideline that will help you to round off numbers in Excel.

Here, you can download the Excel file that we’ve used to make this tutorial. The Excel file will help you practice all the methods discussed in this article.

Rounding Up Numbers in Excel.xlsx

Table of Contents

Get Some Basics About the Decimal Fraction Numbers

A decimal fraction number has two parts in total which are the whole number part and the fractional part respectively.

A decimal point separates those parts. So in any decimal number, you will get the sequence as the whole number part, the decimal point, and the fractional part respectively.

As you move left to the decimal point you will get ones, tens, etc. To the right side of the decimal point, the decimal places are tenths, hundredths, thousandths, etc.

You can get the whole idea from the picture below:

A Complete Guide to Round Off Numbers in Excel

Excel provides tons of flexibility in the case of rounding off numbers. You can use one of two primary methods to round off numbers in Excel. Those are,

Button

Function

Now, let’s learn both of the techniques from the following sections.

Round Off Numbers in Excel with ROUND Function

To round off the numbers with ROUND function, follow these steps below:

Use the ROUND function.
Inside the function, first, insert the number or its cell address.
Then insert the number of digits that you want to have after the decimal point.

Example

=ROUND(B5,2)

This formula rounds off numbers 3.1416 to 3.14. Inside the formula, B5 is the cell address where the number 3.1416 is located. And 2 means, after the decimal point, only 2 digits are allowed. So 3.1416 ultimately becomes 3.14.

Summary

To round off numbers without a formula, use the Increase/Decrease Decimal command.

Use ROUND function to round a number to a certain decimal place.
Apply ROUNDUP function to round up a number, away from zero (0).

Employ the ROUNDDOWN function to round down a number towards zero (0).
Utilize MROUND function to round off a number to your desired multiple.
Consider the FLOOR function to round down a number to the nearest multiple.

Exploit the CEILING function to round up a number to the nearest multiple.
Use the INT function to round off a number to the nearest integer value.
Apply the TRUNC function to round off a number by truncating the fractional part.

Round Off Numbers in Excel Without Formula

We can adjust the decimal places after the decimal point as we like using the increase decimal and the decrease decimal command. This is the fastest way to round off values in Excel without a formula.

Increase Decimal Command

We have a list of decimal fraction numbers having 4 decimal places after the decimal point. Let’s say we want to increase two more decimal places after the decimal point. As we are thinking of increasing the decimal places, we can do so easily using the Increase Decimal Command. To do so, go through these steps below:

First of all, select all the decimal fraction numbers.

Then, select HOME from the ribbon.
After that, choose the Number option.
Under the Number option, you will find the Increase Decimal command. Just click on it twice.

After clicking on the Increase Decimal command, one additional decimal place will be added after the decimal point. So, you can adjust the decimal places by hitting on the Increase Decimal command.

Decrease Decimal Command

For now, we want to reduce from 4 decimal places after the decimal point to only 2 decimal places. To do this, we can use the Decrease Decimal command. Follow these steps below:

At first, select the numbers where you want to apply this command.
Then click on the HOME tab.
Navigate to the Number option.

Under the Number option, just click twice on the Decrease Decimal command.

The more you click on this command, the more decimal places begin to reduce. So, just by clicking on the Decrease Decimal command, you can adjust the decimal places after the decimal point of any decimal fraction numbers.

Using the ROUND Function

The ROUND function can round off a number to a specific number of digits after the decimal point.

ROUND Function Syntax

 ROUND(number, num_digits)

The ROUND function has the following two arguments,

number: This is a mandatory field. You must enter a number to round it off.
num_digits: This field is also mandatory. This specifies the number of digits after the decimal point of a decimal fraction number.

Example

Look at the following formula:

=ROUND(3.148,2)

Here, in place of the number argument, we have 3.148; this is the number to round off. Then in place of the num_digits argument, we have 2; this specifies we want only 2 digits after the decimal point of 3.148.

Round Off a Number to the Closest Integer

We have a list of decimal fraction numbers. But we don’t want the decimal places after the decimal point. Thus, we’re going to round all of the values to the nearest integer value.

Now we will seek the help of the ROUND function. To round all decimal fraction numbers off, here are the steps below:

First select a blank cell.
Insert the formula of the ROUND function: =ROUND(B5,0)

After that, press the ENTER button.
So, the above formula will return 3 in cell C5. Because 3.1416 lies between two integer numbers. Those are 3 and 4. As the integer 3 is much closer to 3.1416 than 4 is, the formula has returned 3 in cell C5.
Now, move your mouse cursor to the bottom-right corner of cell C5 and you will see the Fill Handle icon appear.
Drag down the “+” icon until C10.

After dragging the Fill Handle icon, you will see all the decimal fraction numbers have rounded off to the closest integer values as in the image below:

Round Off a Number to One Decimal Place

If you want to have only one decimal place after the decimal point i.e. only one digit after the decimal point then, go through these steps below:

Click on an empty cell.
Insert the following formula: =ROUND(B5,1)

Hit the ENTER button.
Drag the Fill Handle icon to the end of the Output Numbers column.

That’s it.

The digit 1 in place of the second argument of the ROUND function specifies that the number specified in place of the first argument will be rounded off to only one decimal place after the decimal point.

Round Off a Number up to Two Decimal Places

By inserting the digit 2 in place of the second argument of the ROUND function, you can round off a number mentioned in place of the first argument of the ROUND function. To do this, follow these steps:

Click a blank cell.
Type the following formula: =ROUND(B5,2)
Hit the ENTER button.

Draw the Fill Handle icon at the end of the second column of the data table.

Round Off a Number to Three Decimal Places

To round off a decimal fraction number to three decimal places, insert the number in place of the first argument of the ROUND function. Then insert 3 in place of the second argument is the following formula: =ROUND(B5,3)

Round Off a Number to the Nearest 10

In this section, you will learn to round off a decimal fraction number to its nearest multiple of 10 such as 20, 30, 40, etc. To do so, you can use the formula: =ROUND(B5,-1)

In this formula, B5 is the cell address of the decimal fraction number to round off. Then -1 specifies that the number referred to in place of the left argument will be rounded off to its nearest multiple of 10.

So, after applying the formula 3.1416 has become 0. Because 3.1416 lies in between two multiples of 10; which are 0 and 10. Here, 0 is closer to 3.1416 than 10. So the formula returns 0.

Like this trend, 15.37151 has become 20, 28.77 has become 30, and so on.

Round Off a Number to the Nearest 100

In this method, you will learn to round off fraction numbers to the nearest multiples of 100. So let’s get started:

First, click on a cell.
Type this formula in a cell to get the rounded-off number: =ROUND(B5,-2)

Then press the ENTER button.

In this formula, B5 refers to the number 3.1416, and -2 tells us to round off the number in cell B5 to its nearest multiple of 100. So, this formula has returned 0. The other numbers such as 115.37151 have become 100, 228.77 have become 200, and so on.

Round Off a Number to the Nearest 1000

To round off a number to the nearest multiple of 1000, use the following formula: =ROUND(B5,-3)

In this formula, B5 refers to the cell address of the decimal fraction number to round off. The second argument which is -3 refers to rounding off the number referred to in cell B5 to its nearest multiple of 1000.

Round Off a Negative Number to the Closest Integer

To round off a negative number to its nearest integer value, use the following formula of the ROUND function: =ROUND(B5,0)

In this formula, B5 refers to the negative number to round off. Then, 0 tells that the negative number will be turned into its nearest integer value.

So, you can directly input any negative number in place of B5 in the formula. Or, you can insert any cell address that actually refers to a negative number, and then you are ready to go.

Round Off a Negative Number to Two Decimal Place

To round off (two places after the point) a negative number follow the same method as you did for a positive fraction number. Anyways, here are the steps to follow:

First of all, select any cell.
Insert the formula: =ROUND(B5,2)

Then, hit the ENTER button.

In this formula, B5 refers to the cell address of a negative decimal fraction number which is -53.1416. Then the number 2 specifies that after the decimal point, there will be only 2 digits available. So, after executing the formula, -53.1416 has become -53.14.

Method 3: Using the ROUNDUP Function

The ROUNDUP function takes numbers away from zero to round them off.

ROUNDUP Function Syntax

ROUNDUP(number, num_digits)

Arguments of the ROUNDUP function:

number: This is a mandatory field. Here you enter a number or a cell address to round off.
num_digits: This field is also mandatory. After the decimal point of a decimal fraction number, this indicates the digit numbers you want to have.

The ROUNDUP function is seen in operation in the image above. It moves a number away from zero by rounding it up. To round off 3.1316, we used the formula below: =ROUNDUP(B5,2)

In the above formula, B5 is the cell address that contains the number 3.1416. Then 2 specifies that only two digits are allowed after the point. So 3.1416 becomes 3.14 after applying the ROUNDUP function.

Using ROUNDDOWN Function

The ROUNDDOWN function usually rounds a number down, to zero.

ROUNDDOWN Function Syntax

ROUNDDOWN(number, num_digits)

The ROUNDDOWN function has the following two arguments,

number: This is a mandatory field. Here you insert a number or a cell address to round down, toward the 0 (zero).
num_digits: This field is also mandatory. Here you insert the digit according to which, digits after a decimal point will appear after applying the formula.

We can see the application of the ROUNDDOWN function in the above picture. We have used the following formula to round down 3.1416: =ROUNDDOWN(B5,2)

Within the above formula, B5 refers to the cell address where the number 3.1416 is. Then the number 2 separated by a comma tells that after the rounded-up version of 3.1416, there will be only two digits after the decimal point.

So after applying the above formula of the ROUNDDOWN function, the number, 3.1416 eventually becomes 3.14.

Using the MROUND Function

The MROUND function rounds off a number to the desired multiple.

MROUND Function Syntax

MROUND(number, multiple)

The MROUND function has only two arguments:

number: This is a mandatory field. Here you will insert the number that is to round to your desired multiple.
multiple: This field is also mandatory. Here you insert the multiple to round off your number.

We can see the usage of the MROUND function in the above screenshot. We have applied the following formula of the MROUND function, to round off numbers to any multiple as we like.

=MROUND(B5,2)

In the above formula, B5 holds the number 3.1416 that we want to round off. The number 2 is followed by a comma, make sure that the number 3.1416 turns into the nearest multiple of 2.

So after applying the above formula, 3.1416 becomes 4. Because the multiples of 2 are 0,2,4,6, etc. Among them, the one nearest to 3.1416 is 4. So 3.1416 eventually becomes 4.

Using the FLOOR Function

The FLOOR function rounds a number down, towards zero, to the nearest multiple of the significance.

FLOOR Function Syntax

FLOOR(number, significance)

The FLOOR function has two arguments only,

number: This is a mandatory field. The number to round off.
significance: This field is mandatory too. Here type the multiple to which you wish to round your number off.

In the picture above, we can see the FLOOR function in operation. Here’s the formula that we have applied: =FLOOR(B5,3)

We use the FLOOR function to round down a number to its nearest multiple of significance. Here are intended number to round down is 3.1416 which is referred to as B5. The number 3 is our significance.

The multiples of 3 are 0,3,6,9, etc. So the nearest multiple of 3.1416 is 3. As a result, after applying the formula of the FLOOR function, the number 3.1416 becomes 3.

Using the CEILING Function

The CEILING function rounds up a number, to the significance’s closest multiple.

CEILING Function Syntax

 CEILING(number, significance)

The CEILING function has only two arguments,

number: This is a mandatory field. The number you wish to round up to the next multiple of significance.
significance: This field is also mandatory. It refers to the multiple to round off your number to.

In the picture above, we can see the application of the CEILING function. Here is the formula of the CEILING function that we have used: =CEILING(B5,3)

The CEILING function rounds up a number, away from zero, to the closest multiple of the significance. In the above formula, we’ve used significance, 3.

The multiples of 3 are 0, 3, 6, 9, etc. The nearest among all of these that is greater than 3.1416 is 6. So, after applying the formula 3.1416 becomes 6.

Using the INT Function

The INT function turns a number down to the nearest integer value.

INT Function Syntax

INT(number)

The INT function has only one argument,

number: This is a mandatory field. A number you wish to round down to the nearest integer value.

We see the INT function in operation in the picture above. What the INT function does is round down a number to the nearest integer value. Here’s the formula that we’ve used above: =INT(B5)

In the above formula, B5 refers to the cell address where the number 3.1416 is. The nearest integer value of 3.1416 is 3. So after applying the formula 3.1416 becomes 3.

Using the TRUNC Function

The TRUNC function turns a decimal fraction number into the root integer number by removing the fractional part after the decimal point.

TRUNC Function Syntax

 TRUNC(number, [num_digits])

The TRUNC function has two arguments,

number: This is a mandatory field. Here you insert the decimal fraction number that you want to trim off to the basic integer value.
num_digits: This field is optional. Here you specify the number to keep after the decimal point. By default, its value is zero (0).

$Usage of TRUNC Function to trim fractional part of decimal fraction numbers in Excel$

In the above picture, we can see the TRUNC function in operation. Here is the formula of the TRUNC function, that we have used above: =TRUNC(B5,0)

We use the TRUNC function to delete the part after the point from a decimal fraction number. On top of that, we can also control the number of digits that we want to truncate after the decimal point.

For example, 0 means no fractional part at all, 1 means only one decimal place after the decimal point, 2 means only two decimal places are allowed after the decimal point, and so on.

Within the above formula of the TRUNC function, B5 refers to the decimal fraction number, 3.1416. Then the number, 0 indicates that there will be no fractional part at all after the decimal point of the number, 3.1416. So, after applying the formula of the TRUNC function, 3.1416 becomes only 3 without .1416.

Conclusion

In the article, we’ve discussed 9 distinct methods to round off numbers in Excel, each with their microscopic breakdown. Hope you’ve learned everything you need to know in this regard. You can share any other tricks, recommendations, or suggestions with us in the comment box below. And don’t forget to visit our site Excelgraduate to get more articles related to Excel. Thanks.

Frequently Asked Questions

What is the round off?

Rounding off is the process of approximating a numerical value to a more manageable or simplified form. In the context of numbers, including time, rounding involves adjusting the value to a specified degree of precision. For instance, rounding off time can be done to the nearest minute, hour, or any desired unit.

To round off a number, determine the precision level, identify the target unit (e.g., minutes, hours), and apply standard rounding rules. If the part to be rounded is equal to or greater than half of the target unit, round up; otherwise, round down.

In the case of time, rounding off helps streamline values for practical use, making them more convenient for everyday applications. Keep in mind that rounding introduces a slight degree of approximation, so choosing an appropriate precision level is essential for accuracy in specific contexts.

Can I round off mean?

Yes, the mean (average) of a set of values can be rounded off. Rounding off the mean involves approximating the calculated average to a specified level of precision. This is commonly done to simplify and present the mean in a more digestible form.

To round off the mean:

Determine Precision: Choose the desired level of precision (e.g., rounding to the nearest whole number, decimal place, etc.).
Apply Standard Rounding Rules: Use typical rounding rules. If the decimal portion is 0.5 or greater, round up; otherwise, round down.
For example: If the mean is 4.56 and rounding to the nearest tenth, it becomes 4.6. If the mean is 7.84 and rounding to the nearest whole number, it becomes 8.

Rounding off the mean is a common practice to present summary statistics in a more user-friendly format. It’s important to consider the context and intended audience when deciding on the level of precision to use.

Can we round off time?

Yes, time can be rounded off for various practical purposes. Rounding off time involves simplifying or approximating the exact time to a more manageable or convenient value. For example, you might round off to the nearest hour, 15 minutes, or 5 minutes.

To round off time, follow these general steps:

Determine the Precision: Decide the level of precision to which you want to round off the time. Common choices include rounding to the nearest minute, 5 minutes, 15 minutes, or hour.
Identify the Target Unit: Understand the unit to which you are rounding off (e.g., minutes or hours).
Apply Rounding Rules: Use standard rounding rules. If the minutes are 30 or more, round up; if less than 30, round down.
For example: If the time is 3:28 and rounding to the nearest 5 minutes, it becomes 3:30. If the time is 3:22 and rounding to the nearest 15 minutes, it becomes 3:15.

Remember that rounding off time may introduce slight inaccuracies, so consider the specific needs of your task or situation.

Related Resources

Rate this post