The Rounding Precision feature in Excel can be used to fix the issue of rounding or floating point errors in Microsoft Excel. You will find below the steps to set Rounding Precision in Excel.
Set Rounding Precision in Excel
As you must be aware, Microsoft Excel displays rounded numbers (for example 4.56) and not the actual numbers (4.557321) in Excel spreadsheets.
However, when it comes to calculations, Excel uses the actual numbers (which can be up to 15 decimal points) and not the rounded numbers as displayed in the worksheet.
This results in Excel rounding errors, which can lead to cases of clients/customers claiming errors in Excel spreadsheets, based on their manual spot checks using calculators.
A solution to such rounding or floating point errors in Excel is to force Microsoft Excel to use displayed numbers in all calculations, instead of using the actual numbers.
This can be achieved using the “Rounding Precision” feature as available in “Advanced options” in Microsoft Excel.
Dangers of Using Rounding Precision in Excel
Before going ahead with the steps to set Rounding Precision in Excel, you need to be aware that setting Rounding Precision on an already built spreadsheet can permanently impact its accuracy.
Hence, use this feature only if you are trying to match numbers and you are OK with Excel spreadsheet losing its default (15 decimal places) accuracy.
Steps to Set Rounding Precision in Excel
Follow the steps below to Set Rounding Precision in Excel.
1. Open Microsoft Excel and click on the File tab located in the top-left corner of your screen.
2. On the next screen, scroll down to the bottom in side-menu and click on the Options tab.
3. On Excel Options screen, click on the Advanced tab in the left-pane. In the right-pane, scroll all the way down and select Set Precision As Displayed option located under “When Calculating This Workbook” section.
Note: You can select current or different Excel Workbook by using the down arrow located next to “When calculating this workbook” entry.
4. Once you check “Set precision as displayed” box, you will see a warning pop-up. Click on OK to confirm.
5. Click on the OK button in the Advanced tab (see image in step#3) to save the above changes.
6. Next, click on the Home Tab and select your desired number of decimal places (2 or more) for this particular worksheet.
After this, Microsoft Excel will set Rounding precision for this particular worksheet to 2 decimal places.