Fixing Excel: Formulas Displayed Instead of Results. Encountering a scenario where Excel shows formulas rather than the desired results is a common issue. In this article, we delve into potential reasons for this occurrence and provide solutions to rectify it.
Fixing Excel: Formulas Displayed Instead of Results
Reason 1: Show Formulas Option is Enabled
Excel incorporates a ‘Show Formulas’ option that reveals formulas in cells instead of their outcomes. This often leads to the display of formulas instead of results.
How to Address this:
- Navigate to the Formulas tab in the ribbon.
- Access the Show Formulas option in the Formula Auditing group.
- Click the Show Formulas button to toggle the option.
Note: The active state of the Show Formulas option is indicated by a slightly darker shade in the ribbon. It functions as a toggle, becoming enabled or disabled upon clicking. Alternatively, use the keyboard shortcut Control + ~, holding the central key while pressing ~.
When enabled, the Show Formulas option affects the entire worksheet, displaying all formulas instead of their results. If this solution proves ineffective, explore alternative methods below.
Pro Tip: Verify that the Show Formulas option is disabled by checking if it appears in a lighter shade in the ribbon.
Reason 2: Cells Formatted as Text Instead of General
In Excel, the formatting of cells plays a crucial role in determining how values are displayed within those cells. Various formats cater to numbers, dates, text, currency, and more.
When cells are formatted as text and a formula is entered, Excel showcases the formula itself instead of presenting the expected result. This discrepancy arises because the cell’s format anticipates a text value rather than a formula.
In the illustration below, column D contains formulas, yet the cell format is set to Text, resulting in the display of the formula instead of the result.
How to Rectify this Issue:
- Change Cell Format:
- Select the desired cells.
- Navigate to the Home tab in the ribbon.
- In the Number group, access the Formatting drop-down.
- Choose the General option.
This adjustment ensures that, moving forward, formulas entered in these cells will display their results.
- Handle Existing Formulas:
- For cells with pre-existing formulas displaying as text:
- Select the specific cell showing the formula.
- Press the F2 key to enter edit mode.
- Press Enter to recalculate and showcase the result.
Alternatively:
- Select all cells with formulas displaying as text.
- Press F2 to enter edit mode.
- Hold the Control key and press Enter to swiftly convert all formulas into their respective results.
- For cells with pre-existing formulas displaying as text:
Reason 3: Absence of Equal-To Sign in the Formula
A frequently encountered issue is the omission of the equal-to sign (=) at the formula’s outset.
In Excel, every formula must commence with the equal-to sign, signaling Excel to interpret the subsequent content as a formula. If this crucial sign is absent, Excel treats the formula as regular text, leading to the display of the formula itself without performing any calculations.
How to Resolve this:
- Select the cell where the formula is displayed as text.
- Access the formula bar by clicking into it or double-clicking directly on the cell to initiate editing.
- Introduce the equal-to sign (=) at the formula’s start, ensuring no spaces or other characters precede it.
- After adding the equal-to sign, press Enter. Excel should now recognize the formula, revealing the calculated result instead of the formula text.
Pro Tip: Verify that the formula begins precisely with the equal-to sign to prevent any misinterpretation.
Reason 4: Presence of Apostrophe or Space Before the Formula
In Excel, appending an apostrophe (‘) or a leading space at the beginning of a cell’s content designates it as text. While this is useful for displaying numbers or formulas as literals, an inadvertent inclusion of an apostrophe or space before a formula can lead Excel to treat it as a text string, displaying the formula text as is.
How to Rectify this:
- Select the cell where the formula appears as text.
- Access the formula bar by clicking into it or double-clicking directly on the cell.
- Remove any leading apostrophe (‘) or space before the equal-to sign (=), ensuring the formula begins directly with the equal-to sign.
- Press Enter after making adjustments. Excel should now exhibit the formula’s calculated result instead of the formula text.
Reason 5: Formula Enclosed in Quotes (or Double Quotes)
Similar to the impact of having an apostrophe before a formula, wrapping an Excel formula in quotes (or double quotes) designates it as a text string. While this might be intentional for instructional purposes, it can occur inadvertently, especially when obtaining Excel files from the web or databases.
In cases where the formula is encapsulated in quotes, Excel interprets it as a text string, displaying the formula text rather than its calculated result. This is visually discernible as extra quotes are visible in the cell.
How to Rectify this:
- Select the cell where the formula appears as text.
- Access the formula bar by clicking into it or double-clicking directly on the cell.
- Remove the quotes (”) or double quotes (“”) surrounding the formula. Ensure the formula commences with an equal-to sign (=) and has no preceding text characters.
- Press Enter after effecting the changes.
Pro Tip: Vigilantly check for and eliminate any unnecessary quotes to ensure accurate formula interpretation.
Reason 6: Use of FORMULATEXT Function
The FORMULATEXT function in Excel is specifically designed to display the formula from a referenced cell as a text string. While advantageous for auditing or documenting formulas, it may lead to the display of the formula text instead of its result.
How to Address this: Formulas Displayed Instead of Results
- Avoid using FORMULATEXT if the goal is to showcase the result of a formula rather than the formula itself.
- Directly reference the cell containing the formula to display the calculated result.
Fixing Excel: Formulas Displayed Instead of Results. By refraining from using FORMULATEXT, you ensure the presentation of formula results instead of the formula text, contributing to enhanced clarity in spreadsheet interpretation.
