This quick and easy guide will explain how to use the ROUND formula in Excel 2010 and the various ways you can implement a rounding of Excel values…
Round Formula in Excel 2010
In Excel reports and especially in Excel dashboards where space is usually at a premium, you should get into the habit of rounding numbers but there are a couple of ways that you can achieve this.
When you don’t want the actual value to change, you just want to round up the decimal places
If you want to retain the original value but just change the look of the value, i.e. take 1063.46 and round it to 1063, then the best way is to just change the format of the cell.
To do so right-click on your cell, or on the range of cells you want to change and select “Format Cells”. In the option box that appears make sure the category selected is a numerical one then adjust the number of decimal places accordingly:
When you want the actual value to change as a result of rounding
The alternative option is to change the value to a new one, based on the new rounding criteria.
There are a few reasons for doing the rounding this way but I find the main one is when you need to implement some conditional formatting or you are using additional formula that relies on the value having already been rounded, for example a BETWEEN statement.
How to change the value when rounding
The answer is with the ROUND function. The ROUND function takes the format:
This is where “Number” is the cell which you are performing the manipulation on and “Num_Digits” expresses the type of rounding you want to perform, i.e. if you use 1 it would round to 1 decimal place.
To understand how to change the “Num_Digits” section of the formula to get the desired result see the guide table below:
As you can see from the guide table the “Num_Digits” can be a positive or a negative number so not only can you use this formula to remove decimal places you can also round data to the nearest ten, hundredth, thousandth and so on.
There are 2 main ways to round a value in Excel, either by changing the format of the value, or the “appearance” or by changing the actual value itself.
If the aim is just for appearance then use the Format cells method, however if you are using the result of your rounded value in additional calculations or for conditional formatting you are better off calculating a new value.
The ROUND() function is used to calculate a rounded version of a value. You can round decimals and whole numbers to different places using the “Num_Digits” option.
I hope that helps some of you out that have been interested in this topic, as always it’s pretty simple, but only once you know how!