'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Stop Excel from Rounding Numbers in the Formula Bar
Question details
The user wants to prevent Excel from automatically altering or rounding the exact underlying decimal values of numbers shown in the formula bar.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering exact decimal numbers into cells and needing the underlying stored value to remain completely accurate regardless of visual cell formatting.
- Observed behavior
- Excel automatically rounds both the displayed values in the cell and the underlying constants stored in the formula bar, leading to a loss of exact decimal precision.
Check if your numbers are only visually rounded due to cell formatting, or if the actual underlying values in the formula bar have been permanently changed. Note that disabling workbook precision settings will not restore numbers that were already permanently rounded.
Disable the 'Set Precision as Displayed' Option
Turn off this advanced workbook calculation setting to ensure Excel retains the exact underlying values you enter, rather than permanently rounding them to match the cell's visual formatting.
When 'Set precision as displayed' is enabled, Excel permanently changes the underlying stored value to match whatever is visually displayed in the cell. If a cell is formatted to show zero decimal places, a number like 2117.51 will be permanently rounded to 2118, and the exact decimals will be lost.
Click on 'File' in the top ribbon, then select 'Options' located at the bottom of the left-hand menu.
In the Excel Options dialog box, click on 'Advanced' in the left pane.
Scroll down to the 'When calculating this workbook' section. Clear the checkbox next to 'Set precision as displayed', then click 'OK'.
Check your data. Any constants that were previously rounded by this setting have lost their original decimals and must be typed into the cells again manually.
Enter the Value as an Exact Formula
Force Excel to retain the exact decimal value by inputting the number as a calculation formula instead of a static constant.
Prevent Number Rounding Issues Easily in WPS Spreadsheet
WPS Office offers a highly compatible and intuitive spreadsheet tool that safely handles decimal precision and advanced cell formatting. You can easily manage how your data is calculated and displayed without risking unexpected permanent rounding to your underlying numbers.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet file.
- 2. Access Options: Click on 'Menu' at the top left corner and select 'Options' from the drop-down list.
- 3. Adjust Calculation Settings: Navigate to the 'Calculation' tab located in the Options dialog box.
- 4. Disable Precision Setting: Ensure the 'Precision as displayed' checkbox is cleared, then click 'OK' to safely preserve your exact decimal values.
Frequently Asked Questions
Why does Excel change the number in the formula bar instead of just the cell display?
Excel alters the underlying value in the formula bar if the 'Set precision as displayed' option is turned on. This setting forces the actual stored number to permanently match the rounded value shown visually on the screen.
Can I recover numbers that were already rounded by the precision setting?
Unfortunately, once 'Set precision as displayed' is applied and the file is saved or recalculated, the original decimal numbers are permanently lost. You must disable the setting and manually re-enter the original unrounded constants.
How do I round the visual display without changing the exact underlying value?
Select the cell, right-click, and choose 'Format Cells'. Under the 'Number' tab, adjust the 'Decimal places' counter to your desired rounding level. This changes the visual display only, keeping the exact value intact in the formula bar as long as 'Set precision as displayed' is disabled.
