Excel formula not calculating or showing as text: the fixes

The symptom

You type a formula and the cell keeps the text you typed instead of a value:

Cell A11 displays:   =SUM(A1:A10)
Expected:            the total of A1:A10

Or the formula did calculate once, but the value no longer follows its inputs:

A1 changed from 10 to 50
=A1*2 still displays 20 instead of 100

These are two different problems. The first is about how Excel reads what you typed; the second is about when Excel recalculates. Both are covered below.

Why it happens

Microsoft's troubleshooting page How to avoid broken formulas in Excel lists the causes of a formula that "shows syntax instead of value" and of a formula that "does not calculate":

• Show Formulas is on. This is a display switch. According to Display or hide formulas, Formulas > Show Formulas and the shortcut Ctrl + ` (grave accent) switch between displaying formulas and their results. When it is on, your formulas are intact; Excel simply shows their text.
• The cell is formatted as Text. Microsoft's page says that if Show Formulas is not the cause, "it's possible the cell is formatted as text". Microsoft's own example: "if you enter a simple formula =2+3 in a cell that's formatted as text, Excel can't calculate the data you entered. All you'll see in the cell is =2+3." Microsoft's documented fix therefore includes re-entering the cell after changing its format.
• The entry does not start with an equal sign. The same troubleshooting page states: "If your entry doesn't start with an equal sign, it isn't a formula, and won't be calculated." Typing SUM(A1:A10) gives you the text SUM(A1:A10). A leading space breaks this rule too: the first character is then a space, not =. Microsoft documents elsewhere that a space typed before an entry "remains in the cell after you press Enter" (Stop automatically changing numbers to dates).
• An apostrophe sits in front of it. Microsoft documents the apostrophe as a way to force text: "You can type an apostrophe ( ' ) in front of the number, and Excel will treat it as text" (Keeping leading zeros and large numbers). The apostrophe "isn't displayed in the cell after you press Enter" (Stop automatically changing numbers to dates), so the cell can look like a formula while the entry is text.
• Calculation is set to Manual. In Change formula recalculation, iteration, or precision, Microsoft explains that Automatic is the default and that Manual turns off automatic recalculation, so formulas update "only when you manually recalculate (for example, by pressing F9)". The troubleshooting page puts it plainly: "Formulas won't calculate if manual calculation is enabled."

How to fix it

Work through these in order, which follows the order of Microsoft's troubleshooting page.

1. Turn Show Formulas off. Press Ctrl + ` (the key above Tab), or select the Formulas tab and, in the Formula Auditing group, select Show Formulas so it is no longer highlighted. If every formula cell now shows a value, you are done.

2. Change a Text cell to General and re-enter the formula. Right-click the cell, select Format Cells (or press Ctrl + 1), choose General, select OK, then press F2 and Enter. Microsoft gives exactly this sequence: "Format Cells > General (or Ctrl + 1), then press F2 > Enter". Do not skip the F2 and Enter step: it re-enters the formula in the newly formatted cell.

3. Fix a whole column of Text cells at once. For a large range, Microsoft's route is to select the range, apply the number format you want, and then use:

   Data > Text to Column > Finish
   

   Microsoft gives this as the route for a large range, instead of re-entering each cell with F2 and Enter.

4. Remove a leading apostrophe or space. Select the cell, press F2, delete the apostrophe or space in front of the =, and press Enter. The entry must begin with the equal sign:

   Wrong:  '=SUM(A1:A10)
   Wrong:   =SUM(A1:A10)     (space before =)
   Right:  =SUM(A1:A10)
   

   If the cell is also formatted as Text, do step 2 first, or the corrected formula will still be stored as text.

5. Add the missing equal sign. If the entry reads SUM(A1:A10) or 11/2, put = in front. Microsoft notes that 11/2 without it is read as a date, not as a division:

   =SUM(A1:A10)
   =11/2
   

6. Switch calculation back to Automatic. In Excel for Windows, select File > Options > Formulas, and in the Calculation options section, under Workbook Calculation, select Automatic. The quicker route is on the ribbon: Formulas tab, Calculation group, Calculation Options > Automatic. Microsoft warns that in the desktop apps "changing these options affects all open workbooks".

7. Recalculate straight away if you want to stay in Manual. Microsoft describes Manual as a way to control when calculation happens in workbooks that take a long time to recalculate. If you keep it for that reason, recalculate with the documented keys:

   F9                  recalculate changed formulas in all open workbooks
   Shift+F9            recalculate changed formulas in the active worksheet
   Ctrl+Alt+F9         recalculate all formulas in all open workbooks
   Ctrl+Shift+Alt+F9   check dependent formulas, then recalculate all
   

   Formulas > Calculate Now and Formulas > Calculate Sheet do the same from the ribbon.

Check that it worked

• With Show Formulas off, the cell shows a value and the formula bar shows the formula starting with =, with no apostrophe or space before it.
• Home > Number Format for the cell reads General (or the number format you chose), not Text.
• Change one of the input cells. With Automatic calculation, the result updates as soon as you press Enter, without F9.
• Formulas > Calculation Options has Automatic selected.

If the formula now calculates but returns an error, that is a separate problem. A lookup that returns #N/A although the value is visibly there is covered in XLOOKUP returns #N/A when the value exists, and the broader VLOOKUP failures (wrong match type, key not in the first column, text against numbers) in VLOOKUP not working. A formula that calculates but shows #SPILL! cannot place its multiple results in the grid; see #SPILL! error in Excel.

Numbers stored as text in the cells the formula reads

Sometimes the formula itself is fine but its inputs are text. Microsoft's page Convert numbers stored as text to numbers in Excel says that "Numbers that are stored as text can cause unexpected results, like an uncalculated formula showing instead of a result." Excel usually flags these with an alert next to the cell. To fix them:

1. Select the cells, then select the error indicator in the top-left corner (or press Alt + Shift + F10).
2. Select Convert to Number.

If no alert appears, turn on File > Options > Formulas > Error Checking > Enable background error checking. For a column you cannot convert in place, add a helper column with the VALUE function and copy the results back as values:

=VALUE(E23)

Then select the new cells, press Ctrl + C, select the first cell of the original column, and use Home > Paste > Paste Special > Values (or Ctrl + Shift + V). The same text-versus-number mismatch is also why lookups fail on IDs imported as text, which the VLOOKUP troubleshooting page covers.

Excel for the web and circular references

In Excel for the web, the setting lives in the same place: Formulas > Calculation Options, with Automatic, Automatic Except for Data Tables and Manual, plus Calculate Workbook to recalculate by hand. Unlike the desktop app, Microsoft notes that the change "will affect the current workbook only, and not any other open workbooks in the browser".

A formula can also refuse to calculate because it refers to its own cell. Microsoft states that "by default, Excel cannot calculate a formula that refers to its own cell—either directly or indirectly". The fix is to move the formula to another cell or rewrite it so that it no longer refers back to itself. Only enable iterative calculation (File > Options > Formulas > Enable iterative calculation) if you actually need the repetition.

A formula that shows as text and a formula that does not recalculate are separate faults with separate switches. Check Show Formulas with Ctrl + ` first, then the cell format, then the first character of the entry, and finally the calculation mode. Bear in mind that in the desktop app the calculation option applies to every workbook you have open, so check it even if you did not change it in this file.