How to Round Off in Excel (2020)

October 8, 2020 6:00 pm

Edited By Piyush Kashyap, Reviewed By Gulshan

MS Excel is one of those programs that, in today’s world, is used in every office of every company. There is nothing like the data entry program, which is so easy to understand with a very simple user interface.

Be it large companies or small ones, be it businessmen, administrators, or even college students, everyone uses it.

Do not let looks deceive you because it may look easy to use, but it is filled with incredible features that make it a powerhouse for data entry and mathematical solutions.

How do you Round Off Numbers

Have you ever been faced with a data sheet that is filled with decimal values, and you had to round it off. Sure, you can do it yourself, but that will take ages to complete. But luckily, if you’re using MS Excel, the program can do it for you.

Excel comes with the incredibly handy feature of ROUND, ROUNDUP, and ROUNDDOWN. These are known as functions in the Excel program. The ROUND function allows the user to round off a number or numbers with decimal values.

As the name suggests, the ROUNDUP function rounds up numbers with decimal values while the ROUNDDOWN function rounds down those numbers. These functions are designed to save the user time and effort.

ROUND functions in MS Excel

So, how does the ROUND function in Excel work? Well, it depends on the values in the cell. You can configure the ROUND function to round up the value to a certain number of the decimal place.

For example, if you have a value of 9.532, and the number after the decimal is between 0 and 4, the function rounds the value down to 9.53. If the value after the decimal point is between 5 and 9, then the function rounds it up to 9.54.

To apply the ROUND function:

1
Select an empty cell where you want the result to display in.
2
Click on the ‘Formulas’ tab on the toolbar.
3
From the drop-down menu click on ‘Maths & Trig'
4
Another drop-down menu will appear. Click on ‘ROUND’
5
A new dialogue box will appear to display the ROUND function settings.

Here you have two fields to enter data to customize how the roundup will be:

Number: This is where you type the cell number you wish to round up the data in. For example, if your data is in column C and row 7, you will enter the cell number as C7. This is the cell number where the values are that you wish to round up.

Numb_digits: This is where you specify to the program how many digits you want the resulting number to have. You have multiple options to choose from.

Positive integers: You can use numbers such as 1,2,3, and so on, to specify how many digits you would like to have after the decimal point. For example, if you enter the number ‘2’, the result will be round to two places after the decimal point.
Negative integers: If you use negative integers such as -1,-2,-3, then it will round up the value to the left of the decimal point. For example, if you have a value of 428.35 and use the negative integer ‘-1’, then the result will be 430.
Zero: If you enter just ‘0’, it will round up the nearest integer’s value. So if you have 428.35 and enter ‘0’ in the Numb_digits field, the result will be 428.

When you’re done, click on the OK button to see the result appear on the datasheet.

How to apply this formula to multiple cells?

If you have multiple cells with decimal values and apply this formula to all of them, follow these steps:

1
Make sure the result cell is next to the cells with the values you wish to round up.
2
Click on a blank cell.
3
Click on the ‘Formula’ tab.
4
Click on ‘Maths & Trig.’
5
Enter the cell number whose value you wish to round up.
6
Enter the value you desire in the Numb_digits field.
7
Click Ok to see the answer in the datasheet.
8
Once you have your answer, you can easily apply this formula to other cells.
9
Click the bottom right corner of the result cell and drag it towards the rest of the cells you wish to round up.
10
When you let go of the mouse, the values will be rounded up in the cells you selected.

This way, you can do multiple columns without having to enter data each time before the roundup.

How to use the function bar to Round Up numbers?

If you want a simplified method to perform this same function without much clicking, then you can use the function bar.

1
Click on the cell you wish to see the result in.
2
Click on the function bar to activate it.
3
You will have to use the formula =ROUND(number, numb_digits)

Now, you have to type in the number and numb_digits you want. The same way as you type in the cell number in the number field and the value you want in the numb_digits field when you bring up the ROUND function settings, as mentioned above.

For example, if your values are in column C row 9 and you wish it to round it up to two digits after the decimal point, then all you have to do is type in ‘=ROUND(C9,2)’ in the function bar and hit Enter. You will see the new rounded figure in the empty cell.

How to use the ROUNDUP and ROUNDDOWN function?

When talking about the ROUND function of MS Excel, you cannot ignore the ROUNDUP and ROUNDDOWN functions. These two functions are there for those times when you want to decide what the roundup value should be instead of the number after the decimal point deciding it for you.

1
To access these functions, you must click on a blank cell.
2
Then head up to the ‘Formula’ tab on the toolbar.
3
Click on ‘Maths & trig.’
4
Scroll down till you see the ROUNDUP option below, which you will see the ROUNDDOWN option.
5
Click on it to bring up the ROUND function settings.

This will bring up the same function dialog box as you saw before with the ROUND function. Here you have to add in the values of ‘Number’ and ‘Numb_digits’ and then click Ok to continue.

Just like the ROUND function, you can also use the Function bar to enter the values:

Click on the cell you wish to see the result in.
Click on the Function bar to activate it.
Type the following formula: =ROUNDUP(number, numb_digits) or =ROUNDDOWN(number, numb_digits)
Hit enter to activate the formula. Remember to enter the cell number whose value you wish to change. For example =ROUNDUP(B6,1)

Key points to remember:

Always remember the difference when using positive and negative numbers in the ‘numb_digits’ field. When you use a positive number like 1,2,3, this will round up the value .X,.XX,.XXX decimal places.

Here ‘X’ represents the value that is there to the right of the decimal point. When using positive values, it will always round off to the right of the decimal point. For example, if you use positive numbers in the ‘numb_digits’ field:

Value	Digits	Results
5.1234	0	5
5.1234	1	5.1
5.1234	2	5.12
5.1234	3	5.123
5.1234	4	5.1234

When using negative numbers, always remember that it will round off to the left of the decimal point.

For example, when you use negative numbers of:

Value	Digits	Results
1234.5	0	1235
1234.5	-1	1230
1234.5	-2	1200
1234.5	-3	1000
1234.5	-4	0

When using negative numbers like -1, -2, -3, it will round up to the left of the decimal point.

Value	Result
0	Round up to the nearest whole number to the left of the decimal point
-1	Round up to the nearest 10 to the left of the decimal point
-2	Round up to the nearest 100 to the left of the decimal point
-3	Round up to the nearest 100 to the left of the decimal point
-4	Round up to the nearest 1000 to the left of the decimal point

The entire list of ROUND functions in Excel are as follows:

ROUND: This will round out the values normally.
ROUNDUP: This will round up the value to the nearest specified place.
MROUND: This rounds the value to the nearest multiple
FLOOR: this will round down to the nearest specified multiple.
CEILING: This will round up the value to the nearest specified multiple.
INT: This will round down and return an integer only.

This ROUND function is versatile and customizable, but it requires some time to understand fully. Spending some time with the application and testing out each positive and negative number will give you a better understanding of how it works.

These are very helpful and useful functions when you need to change values quickly. These functions can save you a lot of time and manual effort.

Piyush

Follow me here

Share0

Tweet0

Share0

About the Author

Piyush Kashyap is a Ph.D student at Sant Longowal Institute of Engineering and Technology, Sangrur. He is a budding editor/ writer and has been working as a part-time reviewer for online content. He loves to read tech-based articles and has a knack for reviewing such articles He likes to stay updated about the latest trends in technology. He has also been working as a reviewer for many scientific journals. He also writes articles based on science. Know More About Piyush

Learn more