Fix Excel Show Formulas Setting Not Persisting After Closing
Question details
The user needs a way to keep formulas visible in an Excel workbook, as the 'Show Formulas' setting unexpectedly resets to displaying calculated results after saving and reopening certain files.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening previously saved XLSX workbooks that were originally created in an older version of Excel.
- Observed behavior
- The 'Show Formulas' display mode is active when the file is saved, but upon reopening, the workbook reverts to showing calculated values instead of the formulas in some files.
Ensure your worksheets are not protected, as certain protections can lock display settings. Keep in mind that 'Show Formulas' is primarily designed as a temporary auditing tool rather than a permanent document state.
Toggle Formula Visibility Using Keyboard Shortcuts
Use a quick keyboard shortcut to toggle formula visibility on and off whenever you open the workbook, bypassing the need for the setting to persist.
Because 'Show Formulas' is a workbook-level display preference, Excel may occasionally revert to the default view upon reopening, especially with files originating from older software versions.
Launch Excel and open the specific workbook where you want to view the underlying formulas.
Press the Ctrl + ` (grave accent/backtick) keys simultaneously. This key is typically located above the Tab key in the upper-left corner of an English keyboard.
To switch back to displaying the calculated results, simply press the Ctrl + ` combination again.
Use the FORMULATEXT Function for Permanent Display
If you need a specific formula to always be visible as text next to its calculated result, use a dedicated Excel function that will save and persist permanently.
Easily Manage and Audit Formulas with WPS Spreadsheet
WPS Spreadsheet provides seamless compatibility with Excel files and offers highly intuitive tools to manage, audit, and display your formulas without struggling with unexpected display resets.
- 1. Open your workbook: Launch WPS Office and open your existing XLSX workbook in the Spreadsheet module.
- 2. Navigate to Formulas: Click on the 'Formulas' tab located in the top ribbon menu.
- 3. Activate Show Formulas: Click the 'Show Formulas' button to instantly reveal all underlying formulas across your worksheet.
- 4. Use the shortcut: Alternatively, utilize the Ctrl + ` keyboard shortcut for an even faster toggle between formulas and values.
Frequently Asked Questions
Why does Excel turn off 'Show Formulas' automatically?
The 'Show Formulas' feature is generally treated as a temporary auditing view to help users troubleshoot errors. Depending on the Excel version and file format history, the application often defaults back to showing calculated results upon reopening to ensure the spreadsheet is ready for standard data viewing and presentation.
Can I print a spreadsheet with the formulas showing?
Yes. Once you enable the 'Show Formulas' mode using Ctrl+` or the ribbon button, simply navigate to File > Print. The spreadsheet will print exactly as it currently appears on your screen, displaying the formulas in the cells instead of their calculated values.
Does the FORMULATEXT function update if I change the original formula?
Yes, the FORMULATEXT function is entirely dynamic. If you modify the underlying calculation in the referenced cell, the cell containing the FORMULATEXT function will automatically update to display the newly modified formula text.
