You type a formula, press Enter, and nothing happens. Or the cell shows =SUM(A2:A10) as plain text. Or the answer was right yesterday and is now stuck at 0. Excel is not broken — one of a small number of settings, formats or references is stopping the calculation.
Quick answer: check four things first: Formulas → Calculation Options → Automatic, the cell format (General, not Text), Show Formulas (Ctrl + backtick), and whether the formula starts with =. Those four account for most “formula not calculating” cases.
Quick Diagnosis: Match the Symptom
| What you see | Most likely cause | First fix |
|---|---|---|
| The formula is visible as text, not a result | Cell is formatted as Text, has a leading apostrophe, or Show Formulas is on | Set format to General, remove the apostrophe, press Ctrl + backtick |
| Nothing changes when an input changes | Calculation is set to Manual | Formulas → Calculation Options → Automatic |
| A correct-looking SUM returns 0 | Numbers are stored as text, or the range is wrong | Check ISNUMBER, convert the range, verify the references |
| Excel shows a circular-reference warning | A formula refers to its own cell, directly or through a chain | Formulas → Error Checking → Circular References |
| Only one cell or sheet is stuck | Sheet or workbook protection, or a broken reference | Review → Unprotect Sheet; check for #REF! |
A new function returns #NAME? | The function is not available in your Excel version | Check the version and use a compatible alternative |
1. Calculation Is Set to Manual
This is the most common cause of “my formula is not updating”. Excel can calculate only when you press a key, and the setting is easy to change without noticing.
Go to Formulas → Calculation Options and choose Automatic. On a Mac, the same control is on the Formulas tab. Then force a calculation:
- F9 — recalculate all open workbooks.
- Shift + F9 — recalculate the active sheet only.
- Ctrl + Alt + F9 — recalculate every workbook regardless of whether Excel thinks it changed.
- Ctrl + Shift + Alt + F9 — rebuild the dependency tree and recalculate.
If the workbook is shared, a colleague may have switched it to Manual. Check again after they close it.
2. The Cell Is Formatted as Text
A cell formatted as Text treats everything you type as a label — including a formula. Change the format to General or Number, then re-enter the formula. Changing the format alone often does not recalculate the existing cell.
A fast test: select the cell and look at the Number Format box on the Home tab. If it says Text, that is your answer. Press F2, then Enter, or paste the formula again after changing the format.
3. Show Formulas Is Turned On
Press Ctrl + backtick (the key below Esc, usually sharing the tilde key). This toggles Show Formulas for the whole sheet. It is useful for auditing, but it makes every formula look broken to a student in a hurry.
If the ribbon Formulas tab has Show Formulas highlighted, click it once. The results should return immediately.
4. The Formula Is Missing Its Leading =
Excel needs the equals sign to know that the text is a formula. A space before the equals sign has the same effect: =SUM(A2:A10) is text, not a formula.
Remove the space and retype the formula. If the cell still shows the text, it is probably formatted as Text — fix step 2 as well.
5. A Circular Reference Is Blocking the Chain
A circular reference happens when a formula depends on its own result, directly or through other cells. Excel usually warns you, but the message can be missed. The status bar may show Circular References with a cell address.
Go to Formulas → Error Checking → Circular References and follow the listed cell. Trace the formula back until you find the loop. Iterative Calculation is a deliberate modelling choice, not a general fix — do not switch it on just to hide the warning.
If the assignment intentionally uses iterative calculations, document the setting in your assumptions. Otherwise, break the loop.
6. The Sheet or Workbook Is Protected
Protection can stop you editing a formula, but it can also make a workbook appear frozen when a calculated cell is locked. Go to Review → Unprotect Sheet and Review → Protect Workbook to check the current state.
If the file came from a tutor or a template, the password may be intentional. Do not remove protection from a submission copy without checking the brief.
7. Numbers or Dates Are Stored as Text
This does not stop a formula from calculating, but it makes a correct formula return 0 or the wrong answer. Text numbers are left-aligned by default and =ISNUMBER(A2) returns FALSE. Dates imported from a CSV often arrive as text too.
Fix a whole column with Data → Text to Columns → Finish, or convert with =VALUE(A2) in a helper column. For mixed spaces, punctuation and stray characters, paste the column into the free Data Cleaner and export a clean CSV.
Then re-test the formula. =ISNUMBER(A2) is the quickest check: a real Excel date is a number, while a text date returns FALSE.
8. A Reference Is Broken or External
A formula that points to a deleted column, a renamed sheet or a closed workbook can return #REF! or stop updating. Go to Data → Edit Links and check the source list. Then search the workbook for #REF!.
External links are especially fragile in assignments: the grader will not have the source file. Convert external references to values, or rebuild the formula against a sheet inside the submission workbook. The #REF! error guide has the step-by-step fix.
9. The Function Is Not Supported in Your Excel Version
If the formula returns #NAME? and the spelling is correct, the function may not exist in your version. This is common with dynamic-array functions such as VSTACK, TAKE, CHOOSECOLS and GROUPBY.
Check the version note in the free Excel function reference. For example, VSTACK requires Microsoft 365 or Excel 2024; Excel 2021 never received it. The VSTACK troubleshooting guide explains the alternatives. For dynamic arrays, see the FILTER guide.
10. The Calculation Chain Needs a Rebuild
Large workbooks with many dependent formulas, volatile functions or whole-column references can appear stuck on “Calculating”. Try Ctrl + Shift + Alt + F9 to rebuild the dependency tree. If that fails, close the workbook, reopen it, and recalculate.
Then reduce the load: replace A:A with a bounded range, remove unnecessary INDIRECT, OFFSET and TODAY calls, and check conditional-formatting rules. The free Workbook Health Check flags whole-column references, volatile functions and other risky patterns across up to 200 formulas.
How to Tell a Formula from Text
Use these checks before you change anything:
=ISFORMULA(A2)returns TRUE only when the cell contains a formula.=FORMULATEXT(A2)shows the formula as text; it returns#N/Awhen the cell is a value or text.=ISTEXT(A2)is TRUE for a label, but also for a text number.=ISNUMBER(A2)is TRUE for a real number or date serial, FALSE for text.
If ISFORMULA is FALSE but the cell shows an equals sign, the cell is text. Go back to step 2.
A Five-Minute Diagnostic Sequence
- Press Ctrl + backtick to turn off Show Formulas.
- Check Formulas → Calculation Options → Automatic.
- Select the cell and confirm the format is not Text.
- Press F2 then Enter to re-enter the formula.
- Press Ctrl + Alt + F9 to force a full recalculation.
- Check for a circular-reference warning in the status bar.
- Test the inputs with
ISNUMBERand check for#REF!. - Paste the formula into the Formula Checker for a syntax and structure review.
Before You Submit
A formula that calculates on your machine can still fail on the grader's. Test one normal row, one blank row and one row that should return no match. Save a copy, reopen it, and confirm the values are still there.
Building the formula from scratch? Assemble the syntax in the free Formula Generator, then test it. Paste the distinct formula patterns into the Workbook Health Check, then run the 23-point Assignment Readiness Check for the brief, file name, layout and submission requirements. If the workbook still will not behave, send it to an Excel expert for a fixed-quote review.
Related fixes: #VALUE!, #NAME?, #DIV/0!, COUNTIF and SUMIFS, and the pre-submission audit checklist. For the data behind the most common student faults, see the Excel Error Index 2026.
