Microsoft Excel provides the option to Set Rounding Precision, which can be used to fix the issue of rounding or floating point errors in a Microsoft Excel spreadsheet.
The reason for Rounding or Floating Point errors in Microsoft Excel is due to the way Microsoft Excel displays rounded numbers (say 4.56), but makes use of the actual numbers (say 4.557321) in all its calculations.
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.
In such cases, you can make Microsoft Excel to use “displayed” numbers in calculations, instead of using “actual numbers” by making use of the “Rounding Precision” feature as available under “Advanced options” in Microsoft Excel.
Set Rounding Precision in Excel
Before going ahead with the steps to set Rounding Precision in an Excel Spreadheet, 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 Microsoft 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.