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:
- 1Select an empty cell where you want the result to display in.
- 2Click on the ‘Formulas’ tab on the toolbar.
- 3From the drop-down menu click on ‘Maths & Trig'
- 4Another drop-down menu will appear. Click on ‘ROUND’
- 5A 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.
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:
- 1Make sure the result cell is next to the cells with the values you wish to round up.
- 2Click on a blank cell.
- 3Click on the ‘Formula’ tab.
- 4Click on ‘Maths & Trig.’
- 5Enter the cell number whose value you wish to round up.
- 6Enter the value you desire in the Numb_digits field.
- 7Click Ok to see the answer in the datasheet.
- 8Once you have your answer, you can easily apply this formula to other cells.
- 9Click the bottom right corner of the result cell and drag it towards the rest of the cells you wish to round up.
- 10When 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.
- 1Click on the cell you wish to see the result in.
- 2Click on the function bar to activate it.
- 3You 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.
- 1To access these functions, you must click on a blank cell.
- 2Then head up to the ‘Formula’ tab on the toolbar.
- 3Click on ‘Maths & trig.’
- 4Scroll down till you see the ROUNDUP option below, which you will see the ROUNDDOWN option.
- 5Click 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:
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:
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:
When using negative numbers like -1, -2, -3, it will round up to the left of the decimal point.
Round up to the nearest whole number to the left of the decimal point
Round up to the nearest 10 to the left of the decimal point
Round up to the nearest 100 to the left of the decimal point
Round up to the nearest 100 to the left of the decimal point
Round up to the nearest 1000 to the left of the decimal point
The entire list of ROUND functions in Excel are as follows:
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.