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.

Quick Solution

To round off numbers,

  • 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.

Round off numbers in Excel using the ROUND function

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.

Overview

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.

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:

Introduction to the Decimal Fraction Number Structure

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.

Method 1: 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.

Method 1.1: 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,

❶ 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.

Usage of Increase Decimal Command to Round off Numbers in Excel

Method 1.2: 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.

❶ 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.

Usage of Decrease Decimal Command to Round off Numbers in Excel

Method 2: Using the ROUND Function

A Brief Intro to 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.

Method 2.1: 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,

❶ First insert the formula of the ROUND function in cell C5.

=ROUND(B5,0)

❷ After that press the ENTER button.

Round Off a Number to the Nearest Integer in Excel

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:

Method 2.2: 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,

❶ Insert the following formula in cell C5.

=ROUND(B5,1)

❷ Hit the ENTER button.

❸ Drag the Fill Handle icon to the end of the Output Numbers column.

That’s it.

Round Off a Number to One Decimal Place in Excel

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.

Method 2.3: 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,

❶ Type the following formula in cell C5.

=ROUND(B5,2)

❷ Hit ENTER button.

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

Round Off a Number up to Two Decimal Places in Excel

Method 2.4: Round Off a Number to Three Decimal Place

To round off a decimal fraction number to the three decimal place, 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)

Method 2.5: 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 in place of the left argument will be rounded off to its nearest multiple of 10.

Round Off a Number to the Nearest 10 in Excel

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.

Method 2.6: 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 type this formula in a cell to get the rounded-off number.

=ROUND(B5,-2)

❷ Then press the ENTER button.

Round Off a Number to the Nearest 100 in Excel

In this formula, B5 refers to the number 3.1416 and -2 tells 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.

Method 2.7: 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 in cell B5 to its nearest multiple of 1000.

Method 2.8: 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 the Nearest Integer in Excel

Method 2.9: 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, insert the formula in cell C5.

=ROUND(B5,2)

❷ Then hit the ENTER button.

Round Off a Negative Number to Two Decimal Place in Excel

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

A Brief Intro to 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.

ROUNDUP Function in Excel

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.

Method 4: Using ROUNDDOWN Function

A Brief Intro to the ROUNDDOWN Function

The ROUNDDOWN function usually rounds a number down, towards 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, towards 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.

ROUNDDOWN Function in Excel

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.

Method 5: Using the MROUND Function

A Brief Intro to 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.

A Brief Intro to the MROUND Function

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 the 3.1416 is 4. So 3.1416 eventually becomes 4.

Method 6: Using the FLOOR Function

A Brief Intro to 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.

FLOOR Function in Excel

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 our intended number to round down is 3.1416 which is referred to by 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.

Method 7: Using the CEILING Function

A Brief Intro to 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.

Usage of CEILING function to round off numbers in Excel

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.

So 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.

Method 8: Using the INT Function

A Brief Intro to 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.

Usage of INT Function in Excel

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.

Method 9: Using the TRUNC Function

A Brief Intro to 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 throughout the comment box below. And don’t forget to visit our site Excelgraduate to get more articles related to Excel. Thanks.

(Visited 1,082 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *