'%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 Fix Excel Showing a Formula Instead of the Value
Question details
The user needs to correct the worksheet display so that it evaluates and shows the result of entered formulas rather than displaying the raw formula syntax.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering a formula or referencing another worksheet to calculate data.
- Observed behavior
- The worksheet displays the literal formula text, such as ='Sheet Name'!C6, rather than the expected referenced text or numerical value.
Before tweaking any settings, check if you accidentally pressed the keyboard shortcut Ctrl + ` (grave accent), which toggles the formula auditing mode on and off.
Change Cell Formatting from Text to General
Cells formatted as Text will treat any entered formula as a standard text string. Changing the format to General allows the application to recognize and evaluate the formula.
This is the most common cause for formulas displaying as text. Simply changing the format is not enough; you must also force the cell to recalculate after the format change.
Click on the cell or highlight the range of cells that are displaying the formula instead of the value.
Go to the Home tab on the top ribbon. Locate the Number group, click the dropdown menu (which likely says 'Text'), and select 'General'.
Double-click the cell to enter edit mode, or click inside the formula bar at the top, and press the Enter key. The cell will now calculate and display the referenced value.
Disable the Show Formulas Feature
The 'Show Formulas' auditing tool forces all cells in a worksheet to display their underlying formulas instead of calculated results.
Set Calculation Options to Automatic
If manual calculation is enabled, formulas will not update or resolve newly referenced values until you explicitly tell the application to calculate.
Calculate and Format Data Easily with WPS Spreadsheet
WPS Office offers a fully featured spreadsheet application that handles formulas, cross-sheet references, and complex calculations seamlessly. If you regularly encounter formatting or calculation issues, WPS Spreadsheet provides an intuitive interface to easily manage and troubleshoot your data.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document where the formulas are located.
- 2. Format cells correctly: Highlight the affected cells, navigate to the Home tab, and select General from the number format dropdown.
- 3. Re-evaluate the formulas: Double-click the formula cell or click into the formula bar, then press Enter to trigger the calculation.
- 4. Check formula visibility settings: Go to the Formulas tab and ensure the Show Formulas option is unchecked so your final values display accurately.
Frequently Asked Questions
Why does my spreadsheet formula just show text instead of calculating?
This usually happens because the cell's format was set to 'Text' before you typed the formula. In Text format, the application interprets the equals sign and the subsequent characters as a literal text string rather than a mathematical operation. Changing the format to 'General' and pressing Enter will resolve it.
What is the keyboard shortcut to hide formulas and show values?
You can toggle between displaying underlying formulas and their calculated values by pressing Ctrl + ` (the grave accent key, which is typically located right below the Escape key on your keyboard).
Why aren't my formulas updating when I change reference cells?
Your workbook's Calculation Options might be set to Manual, which prevents automatic updates to save system resources on large files. To fix this, go to the Formulas tab, click the Calculation Options dropdown, and select Automatic.
