If Excel shows a formula instead of its answer, turn off Formulas > Show Formulas or press Ctrl+` on Windows. If the formula is visible in the cell but its result is stale, set Formulas > Calculation Options > Automatic; if only one cell behaves this way, check whether it is formatted as Text or begins with an apostrophe.
Why Excel shows a formula instead of a result
Excel normally displays a formula’s calculated result in the worksheet and the formula itself in the formula bar when you select the cell. For example, a cell containing a sum usually shows a number in the grid, while the formula bar shows the expression used to produce it. Two different problems can therefore look similar: Excel may be displaying formulas across the sheet, or a particular entry may be stored as text rather than treated as a formula.
There is a third possibility: Excel has recognized the entry as a formula but has not recalculated it, leaving an old result on screen. Start by looking at the scope of the problem:
- Many cells show their formulas: check the Show Formulas display setting.
- One or a few cells show formula text: check their number format, leading characters, and how the formula was entered.
- The cell shows a number, but it is out of date: check calculation mode and formula dependencies.
Identifying which case applies saves time. Changing calculation settings will not turn text into a formula, and changing a cell’s number format will not switch off sheet-wide formula display.
Turn off Show Formulas
When Show Formulas is on, Excel displays formulas in worksheet cells instead of their calculated values. It is a viewing option, not a change to the formulas themselves. The setting can make the sheet appear to stop calculating, even though the formulas and their results are still present.
- Open the Formulas tab.
- In the Formula Auditing group, select Show Formulas to switch it off.
- Alternatively, on Windows, press Ctrl+`. The grave accent is often on the key shared with the tilde, though its location varies by keyboard.
On Mac, use Formulas > Show Formulas; the keyboard shortcut can depend on the keyboard layout. If every formula becomes visible or hidden at once, this was likely the cause. Excel may also widen columns when the display changes, so columns can look unexpectedly large afterward. That is a layout effect, not evidence that the formulas changed.
Set calculation to Automatic
A workbook in Manual calculation mode can retain previously calculated results after you edit input cells. The formula is there, but Excel waits for a recalculation command. This is common in workbooks that were saved or edited while calculation was set to Manual, and can be confusing when a workbook is opened on another computer.
On Excel for Microsoft 365 for Windows, go to Formulas > Calculation Options > Automatic. If you want to recalculate immediately, press F9 to calculate open workbooks, or Shift+F9 to calculate the active worksheet. On some keyboards, you may need to use the Fn key with a function key. On Mac, open Excel > Settings > Calculation and choose Automatic; available function-key shortcuts can vary with macOS and keyboard settings.
The Calculation Options menu also includes Automatic Except for Data Tables and Manual. Automatic is the usual choice for an everyday workbook. Manual can be useful in a very large model when recalculating after every change would be slow, but it means users must remember to recalculate. If Manual is intentional, press F9 after changing inputs and before relying on displayed results.
Calculation mode can affect other workbooks open in the same Excel session, so be cautious when switching it to troubleshoot one file. A workbook may also open with an unexpected mode if it was saved that way. After changing the setting, edit a simple input that a formula depends on and confirm the displayed result updates.
Check whether the cell is formatted as Text
Excel treats an entry typed into a cell formatted as Text as text. That can cause an expression beginning with an equals sign to remain visible rather than calculate. This often happens when a column was formatted as Text before data was pasted or typed into it. Changing the cell to General afterward does not necessarily convert an existing text entry into a working formula; you must make Excel enter the expression again.
- Select the affected cell or range.
- On the Home tab, open the Number Format list and choose General.
- Re-enter the formula: select the cell, press F2, then press Enter. If that does not work, delete the entry and type it again.
Look for an apostrophe before the equals sign, too. An apostrophe is a common way to force an entry to be text. Remove it if you want a formula, then enter the expression again. Also check for an accidental leading space. For instance, a formula should begin with an equals sign as its first meaningful character, not with a space or a text marker.
For a concrete check, suppose B2, B3, and B4 contain 10, 20, and 30. In a General-formatted cell, entering =SUM(B2:B4) should display 60. If the cell instead displays the expression, check Show Formulas first, then inspect the cell format and re-enter the formula.
Make sure Excel recognizes the entry as a formula
A formula needs to start with an equals sign. An entry such as SUM(B2:B4) without the equals sign is ordinary text, not a calculation request. A mistyped function name, missing parenthesis, or invalid reference generally produces an error message rather than a silent calculation, but it is still worth checking the formula bar for accidental characters and balanced parentheses.
Some Excel installations use regional settings that expect semicolons rather than commas between function arguments. If a formula copied from another locale is rejected, check the argument separator used by your Excel installation. The range notation in the example above has no argument separator, so it works as a simple test. Avoid inserting an apostrophe or quotation mark around the entire formula; that makes the entry text.
If the cell shows a value such as #NAME? or #VALUE!, Excel is attempting to calculate but has encountered a problem. That is different from displaying the formula as text. Read the error and inspect the function name, referenced cells, and input types rather than changing Show Formulas.
When a formula calculates but the answer is still wrong
A displayed number does not guarantee that Excel has recalculated the workbook or that the formula refers to the intended cells. If Automatic is selected but a result still seems stale, test the dependency chain: change a referenced input slightly, press Enter, and see whether the result updates. Then inspect the formula bar and use Formulas > Trace Precedents to see which cells feed the selected formula.
Other situations that can complicate a result include:
- External links: a formula may refer to another workbook that is closed, unavailable, or awaiting an update. Check the link prompt and the source path before assuming the formula itself is broken.
- Circular references: a formula that ultimately refers back to itself can produce a warning or unexpected results. Find the loop and decide whether the model is intended to use iterative calculation.
- Calculation-heavy workbooks: large ranges, data tables, and complex formulas may take time to update. Wait for calculation to finish before judging the displayed value.
- Errors or invalid inputs: a formula can recalculate correctly and still return an error because a referenced value is missing or has the wrong type.
For a full recalculation on Windows, Ctrl+Alt+F9 forces calculation of all formulas in open workbooks. Ctrl+Alt+Shift+F9 rebuilds the dependency information and recalculates. These are stronger troubleshooting steps, not replacements for correcting a broken reference or an unintended Manual setting.
A quick troubleshooting sequence
Use this order to narrow down the cause without changing unrelated workbook settings:
- Check whether formulas appear throughout the worksheet. If they do, turn off Formulas > Show Formulas.
- If just one cell displays formula text, check whether its format is Text, whether it has a leading apostrophe or space, and whether the formula starts with an equals sign.
- Set the cell to General if needed, then press F2 and Enter to make Excel parse the entry again.
- If the cell displays a result that is not current, choose Formulas > Calculation Options > Automatic and recalculate.
- If the result remains wrong, inspect references, external links, circular references, and any error value shown.
Change one thing at a time and check the result. This helps distinguish a display setting from an entry problem and makes it easier to undo an unnecessary change.
Prevent formula-display problems
Before pasting formulas into a prepared range, check that the destination cells are not formatted as Text. If a workbook needs Manual calculation for performance, make that choice clear to other users and recalculate before sharing or reporting results. When opening a file from someone else, verify both the displayed formula and the calculation setting rather than assuming the visible number is current.
It is also useful to test a small representative formula in a blank General-formatted cell. If it calculates there, the issue is likely specific to the original cells or workbook. If it remains text in the test cell, inspect the entry and regional settings. This simple comparison can separate a workbook-wide problem from a cell-formatting problem without rebuilding the sheet.
Frequently Asked Questions
Why is Excel showing formulas in every cell?
Show Formulas is probably enabled. Go to Formulas > Show Formulas and turn it off, or press Ctrl+` on Windows. This changes what the worksheet displays, not the formulas’ stored results.
Why does only one Excel cell show the formula?
The cell may be formatted as Text, or its entry may start with an apostrophe or space. Change the format to General, remove any text marker, and re-enter the formula with an equals sign.
Why does my Excel formula not update when I change a value?
Check whether calculation is set to Manual. Choose Formulas > Calculation Options > Automatic, then recalculate. If the result remains unchanged, verify that the formula actually refers to the cell you edited.
Will changing Text to General fix a formula automatically?
Not always. Changing the format affects how future entries are interpreted, but an existing text entry may remain text. After choosing General, press F2 and Enter or delete and re-enter the formula.
Why does Excel show an error instead of calculating?
An error such as #NAME? or #VALUE! means Excel is evaluating the formula but cannot return the expected result. Check function spelling, cell references, and the values supplied to the formula; Show Formulas is not the cause of an error value.
Related
- Excel Protect Sheet: Can You Edit Objects?
- Excel Protect Sheet Edit Scenarios Explained
- Excel Protect Sheet: How to Change a Password
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Link Excel Data Validation to a Table the Right WayYes. In Excel for Microsoft 365 on Windows, the reliable way to link a dropdown list to a…
- Conditional Formatting Only If a Cell Has DataYes. In Excel for Microsoft 365, select the cells to format, choose Home > Conditional Formatting > New…
- Excel GROUPBY With Multiple Criteria: Filter ArraysTo group Excel data while applying multiple row-level criteria, use the filter_array argument in GROUPBY and combine AND…
- Attendance Counting Formula in Excel: Count Days PresentIf each student’s daily attendance marks are in B2:AF2 and P means present, use =COUNTIF(B2:AF2,"P") to count days…
- Percent Change Calculator Excel Formula and ExamplesIn Excel, calculate percent change as (new value − old value) ÷ old value. If the old value…
- Excel IFERROR vs ISERROR: Which Should You Use?Use IFERROR when you want to replace an error with a value such as a message or blank-looking…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.