If you need to Round values and numbers in Excel, perhaps round a value to the Nearest 100 or 1000 or if you want to round smaller values to a specific decimal place this guide will explain how. Learn to use the ROUND function in Excel and round your numbers up or down…
The Round Function in Excel
The Round Function in Excel is great function to learn, especially if you are a data analyst or someone who produces Excel reports and Dashboards.
There are many times where Rounding to the nearest 100 in Excel or Rounding values to a whole number should be used to improve the look and clarity of your Excel workbook.
Of course there are times where rounding values is off-limits as the level of accuracy required dictates they need to be displayed but for all those other situations the Round Function in Excel will be your best friend!
How to use the Round Function in Excel
The Round Function in Excel takes the format:
This is where Number is the cell containing the value you want to Round and Num_Digits expresses the type of rounding you want to perform, for example if you Num_Digits is set to 1 the Round Function would round the value to 1 Decimal Place.
To understand how to change the Num_Digits section of the formula and get your desired result the below table will provide a guide:
What often confuses people initially is that the Num_Digits value can be both positive and negative. All this basically means is that you can use the Round Function in Excel to round your values to the decimal place using positive values for Num_Digits and if you want to go the other way and round your value to the nearest ten, hundredth or thousandth you would use negative values for Num_Digits.
For example if you want to round 775 to the nearest 100 in Excel then your formula would be:
That would produce a result of 800.
Rounding Decimal Places without changing the underlying value
If you need to retain the original value and just change the appearance of the value, i.e. take 1063.46 and display it as 1063, the best way is to change the format of the cell rather than apply any functions or formulas to it.
To change the appearance of the cell value right click on the cell, or select and right click on a range of cells if applying the format to more than one, and select Format Cells from the option box.
In the option box make sure the Tab is on Number and then in the Category box make sure Number is selected (or another numerical field like Currency or Accounting). Next all you have to do is adjust the number of decimal places up or down until you get your desired result. The box will show you a sample of what the cell will now look like so you can make sure everything is perfect before you click OK:
- The Round Function in Excel can be used to round a cell value.
- Rounding can be applied to decimal places or whole numbers, i.e. round a value to the nearest 100 in Excel.
- Positive values for Num_Digits will round after the decimal place.
- Negative values for Num_Digits will round before the decimal place.
- If you want to retain the cells original value but just change the appearance use Format Cells instead of the Round Function.
Don’t forget to follow me on Twitter to get informed when new guides and tools are added to the site. A few minutes here and there and you will be surprised how much your Excel knowledge can grow.