How to Fix Excel Formulas Not Calculating, Working, or Updating

Last updated: August 18, 2026

It is frustrating when Excel formulas stop calculating. You type a formula, press Enter, and the cell shows the formula text, a stale number, or nothing useful at all.

Almost every case comes down to a setting or a small formatting problem, not a broken copy of Excel. This guide gives you a diagnostic table first, so you can match what you are seeing on screen to the cause in a few seconds, then walks through each fix in order.

The diagnostic table: match your symptom to the cause

Start here. Find the row that matches what your screen is actually doing, then jump to the fix. Nine times out of ten, the first three rows solve it.

Rule of thumb: if the whole sheet is affected, it is a setting. If one cell is affected, it is that cell's format or content.

What is the difference between not calculating, not working, and not updating?

These three phrases describe different symptoms, and telling them apart narrows the cause immediately.

Why is Excel not calculating automatically?

This is the single most common cause. If the workbook is set to Manual calculation, Excel keeps the last result on screen and does not recalculate when you change an input. Nothing looks broken, which is what makes it so confusing.

Manual mode is sticky. Excel picks up the calculation setting from the first workbook opened in a session, so one file saved in Manual mode can silently put every other file you open into Manual mode too.

How to fix it:

1. Go to the Formulas tab in the ribbon.
2. Click Calculation Options in the Calculation group.
3. Select Automatic.
4. Press F9 to recalculate the workbook right away.

There is a third option, Automatic Except for Data Tables. Use it only when a huge Data Table is slowing the file down. It behaves like Automatic for everything else.

Rule of thumb: if you fix one file and the problem comes back tomorrow, the culprit is the first workbook you open each morning. Set that one to Automatic and save it.

Microsoft documents the full set of calculation options, including precision and iteration, in Change formula recalculation, iteration, or precision in Excel.

Why does my cell show the formula instead of the result?

If a cell was formatted as Text before you typed the formula, Excel stores the whole thing as a string. It never evaluates it. The giveaway is that the formula sits left aligned in the cell, the same way ordinary text does.

How to fix it:

1. Select the affected cell or cells.
2. Right-click and choose Format Cells.
3. 
4. Set the category to General or Number, then click OK.
5. Double-click the cell, or press F2, then press Enter. This step is required. Changing the format alone does not wake the formula up.

For a whole column, change the format, select the range, then use Data > Text to Columns > Finish. That forces Excel to re-parse every cell in one pass instead of you pressing F2 hundreds of times.

Rule of thumb: a formula that appears as text and sits on the left of the cell was entered into a Text formatted cell. Reformatting is only half the fix, you always have to re-enter it.

Why does a leading apostrophe stop a formula?

A single apostrophe at the start of a cell tells Excel to treat everything that follows as literal text. The apostrophe itself does not display in the cell, only in the formula bar, so the cell looks like it contains a normal formula that has simply refused to run.

Apostrophes usually arrive when data is pasted in from a website, a CSV export, or another system that used them to protect leading zeros.

How to fix it:

• Click the cell and look at the formula bar. If you see '=SUM(A1:A5), delete the apostrophe and press Enter.
• For many cells at once, press Ctrl + H, type an apostrophe in the Find box, leave Replace empty, and click Replace All.
• A leading space does exactly the same thing. Check for one before the equals sign.

Rule of thumb: if the cell looks fine but the formula bar starts with an apostrophe or a space, that character is the entire problem.

Is Show Formulas switched on?

Show Formulas is a display mode that replaces every result on the sheet with the underlying formula. It is easy to trigger by accident because the shortcut sits right next to the Tab key.

Two signs tell you this is what happened rather than a formatting problem. First, every formula on the sheet is affected, not just one. Second, Excel roughly doubles the column widths to make room for the formula text.

How to fix it: press Ctrl + ` (the grave accent, above the Tab key on a US keyboard). You can also go to Formulas > Formula Auditing > Show Formulas and click the button so it is no longer highlighted.

The setting is per worksheet, so you may need to toggle it off on each affected tab. Full detail on the display mode is in our guide to how to show formulas in Excel.

Rule of thumb: whole sheet plus wide columns equals Show Formulas. A single cell plus normal column widths equals a text format problem.

Why does a formula stop working after I change the cell format?

Excel caches the displayed result of a formula. If you change the number format of a cell that already holds a formula, the format changes but the cached result does not always refresh, especially when the change is between Text and General.

The reverse case is the more common one. You format a range as Text, then type formulas into it, and none of them run. Excel decided the cell was a text cell at the moment of entry.

How to fix it:

• Select the cell, press F2 to enter edit mode, then press Enter. This re-commits the formula under the current format.
• For a range, use Data > Text to Columns > Finish, which re-commits every cell at once.
• If the sheet is large, press Ctrl + Alt + F9 to force Excel to rebuild every formula regardless of whether it thinks anything changed.

Rule of thumb: change the format first, enter the formula second. Doing it the other way round is what creates this problem.

How do I find and fix a circular reference?

A circular reference happens when a formula depends, directly or through a chain of other cells, on its own result. Excel cannot settle on an answer, so it stops calculating and usually shows 0.

The tell is in the status bar at the bottom left of the window. Excel prints Circular References followed by a cell address there whenever one exists in the open workbook.

How to fix it:

1. Go to Formulas > Error Checking > Circular References. Excel lists the offending cells.
2. Click a listed cell to jump to it, then trace what it depends on with Trace Precedents.
3. Break the loop. Move the intermediate step into a helper cell, or rewrite the logic so the formula never reads its own cell.

Rule of thumb: one circular reference freezes calculation for the entire workbook, not just that sheet. Clear it before you troubleshoot anything else.

What does iterative calculation do to my formulas?

Iterative calculation is the setting that tells Excel to tolerate circular references and repeat the calculation a fixed number of times instead of stopping. It is genuinely useful for a small number of engineering and finance models. It is a problem for everyone else, because it hides circular reference warnings and lets numbers settle on an approximate value that shifts each time the file recalculates.

If your results wobble slightly every time you press F9, this is almost certainly why.

How to check and fix it:

1. Go to File > Options > Formulas.
2. Look at Enable iterative calculation in the Calculation options group.
3. Clear the checkbox unless you deliberately built a model that needs it.
4. Excel will now flag any circular reference instead of silently approximating around it.

Rule of thumb: if you did not turn iterative calculation on yourself, turn it off. It converts a loud error into a quiet wrong answer.

Why do NOW, TODAY, and RAND not refresh?

Volatile functions recalculate whenever anything in the workbook changes, but in Manual mode nothing triggers them at all. The main volatile functions are NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, CELL, and INFO.

Two things trip people up here. In Automatic mode, TODAY only rolls over when the workbook recalculates or is reopened, not at midnight while the file sits open. And a volatile function inside a huge model is a common reason a workbook feels slow, which is often why someone set it to Manual in the first place.

How to fix it: press F9 to recalculate, or Ctrl + Alt + F9 if a stale value is still stuck.

Rule of thumb: a date that will not roll over is not a broken formula, it is a workbook that has not recalculated since yesterday.

F9, Shift + F9, Ctrl + Alt + F9: which one should I press?

Excel has four recalculation commands and they do noticeably different things. Using the wrong one is a common reason people conclude a formula is broken when it is just not being asked to run.

Rule of thumb: F9 is what you press day to day. Ctrl + Alt + F9 is what you press when you are certain the formula is correct and Excel disagrees.

Is the workbook stuck in Compatibility Mode?

If the title bar says Compatibility Mode, the file is a legacy .xls workbook. Modern functions such as XLOOKUP, LET, TEXTSPLIT, and the dynamic array functions either return #NAME? or are silently converted to a static result.

The same symptom appears when a file created in Microsoft 365 is opened in Excel 2016 or 2019. The formula is intact, but that version of Excel does not know the function name.

How to fix it:

• Go to File > Info > Convert to upgrade the file to .xlsx, then save and reopen it.
• If you cannot upgrade the file, swap the unsupported function for one that exists in your version. XLOOKUP can usually be replaced with INDEX and MATCH. Microsoft confirms that XLOOKUP is unavailable in Excel 2016 and Excel 2019.
• Check your regional list separator too. In some locales Excel expects semicolons between arguments rather than commas. Confirm under File > Options > Language.

Rule of thumb: #NAME? on a function you know exists means your Excel does not have it, not that you typed it wrong.

Excel is calculating, but the answer is wrong. Why?

This is a different problem from a formula that will not run, and it has its own short list of causes.

• Numbers stored as text. SUM ignores text, so a column that looks numeric can total to 0. Copy an empty cell, select the range, then use Paste Special > Multiply to convert everything to real numbers in one step. The VALUE function does the same job in a helper column.
• Hidden rows and SUBTOTAL. SUBTOTAL with a function number in the 101 to 111 range deliberately excludes hidden rows, so filtering changes the answer. SUM does not. If a total shifts when you filter, check which one is in the cell.
• Display rounding. Excel stores full precision and shows you a rounded display. Two cells showing 1.5 might hold 1.45 and 1.54, so the sum looks wrong. Increase the decimals to see the real values, or wrap the calculation in ROUND.
• Precision as displayed. Under File > Options > Advanced there is a setting called Set precision as displayed. It permanently truncates stored values to what is on screen. Leave it off.
• Hidden characters. Non-breaking spaces and control characters from web or PDF pastes break comparisons and lookups. Clean them with =TRIM(CLEAN(A1)), then paste the result back as values.
• Stale external links. If the formula points at a closed workbook, Excel serves the last cached value. Go to Data > Edit Links and update or repair the source. Our guide to finding external links in Excel covers how to track them all down.

Rule of thumb: a total that changes when you filter is SUBTOTAL. A total of zero on a column of numbers is text. A total that is off by pennies is display rounding.

How do I fix syntax, reference, and data type errors?

If Excel returns an error value rather than the formula text, the formula is running and failing. Work through these in order.

• Mismatched parentheses. Excel highlights matching pairs in colour as you edit. An unmatched one is the most common typo.
• Wrong separators. Ranges use a colon, as in =SUM(A1:A5). Arguments use a comma in US locales, =SUM(A1,B1).
• Broken references. #REF! means a cell the formula pointed at was deleted. Rebuild the reference, do not just retype the formula around it.
• Wrong data type. #VALUE! usually means text is being fed into a maths operation. Check the inputs before you touch the formula.
• Step through it. Select the cell and use Formulas > Evaluate Formula. Excel resolves the expression one piece at a time so you can see exactly which argument fails.

Rule of thumb: Evaluate Formula finds the broken argument faster than reading the formula ever will.

Could an add-in be blocking recalculation?

Add-ins can hook into Excel's calculation engine. A badly behaved one, or two that conflict, can suppress recalculation entirely. The signal is timing. If formulas stopped working right after you installed something, start here.

1. Go to File > Options > Add-ins.
2. Set the Manage dropdown to Excel Add-ins and click Go.
3. Clear every checkbox, click OK, and restart Excel.
4. If formulas now calculate, re-enable the add-ins one at a time to find the offender.
5. Repeat with the COM Add-ins option in the same dropdown, which is where most third party tools install.

If that does not help, open Excel in Safe Mode. Hold Ctrl while launching Excel, or run excel /safe. Safe Mode loads no add-ins and no customisations, so if formulas calculate there, the problem is definitely an add-in.

Rule of thumb: Safe Mode is the fastest way to prove whether the problem is Excel itself or something bolted onto it.

How AI Can Help Fix Excel Formula Issues

AI-powered tools are changing how we troubleshoot and extend Excel functionality. Here is how AI can help:

1. Formula correction tools. AI tools like ChatGPT and Excel add-ons can analyze formulas, pinpoint errors, and suggest corrections in seconds.
2. Predictive text and formula suggestions. Excel's built-in AI and external tools like Microsoft Copilot can auto-complete formulas and provide intelligent suggestions based on patterns in your data.
3. Automated error checking. AI is good at data validation, highlighting potential errors before they disrupt your workflow. This makes sure formulas and their inputs are accurate, so you spend less time auditing formulas.
4. Learning user behavior. AI tools can learn from your habits, so they can offer customized recommendations and preemptively address common mistakes you make in your workbook calculation.

One caution. AI will happily rewrite a formula that was never the problem. Run through the diagnostic table above first, because a Manual calculation setting or a Text formatted cell is invisible to any tool that only sees the formula string.

Final Thoughts

Excel formulas that will not calculate almost always trace back to a setting rather than a fault. Work down the diagnostic table from the top, and check calculation mode, cell format, and Show Formulas before you rewrite anything. Those three account for the large majority of cases we see.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

Frequently Asked Questions

Why is my Excel formula not calculating automatically?

The workbook is almost certainly set to Manual calculation. Go to Formulas > Calculation Options and select Automatic, then press F9. Excel inherits the calculation setting from the first workbook you open in a session, so if the problem returns, fix and save that file too.

How do I fix a formula showing as text instead of calculating?

The cell was formatted as Text before the formula was entered. Change the format to General or Number, then press F2 and Enter on the cell to re-commit it. Reformatting alone will not work. For a whole column, use Data > Text to Columns > Finish.

What is the difference between F9 and Ctrl + Alt + F9?

F9 recalculates only the formulas Excel thinks have changed. Ctrl + Alt + F9 forces every formula in every open workbook to recalculate whether Excel thinks it needs to or not. Use Ctrl + Alt + F9 when a result is stuck and F9 has not moved it.

Why does my Excel formula return 0 instead of a result?

The two usual causes are a circular reference, which you can confirm in the status bar and locate under Formulas > Error Checking > Circular References, and numbers stored as text, which SUM ignores. Convert text to numbers with Paste Special > Multiply by 1.

Why did my formulas stop working after opening an old file?

The file is probably in Compatibility Mode as a legacy .xls workbook, so newer functions such as XLOOKUP return #NAME?. Use File > Info > Convert to upgrade it to .xlsx, or replace the unsupported function with INDEX and MATCH.

Why do my numbers change slightly every time I recalculate?

Iterative calculation is enabled. It lets Excel approximate its way around circular references instead of flagging them. Turn it off under File > Options > Formulas by clearing Enable iterative calculation, then fix the underlying circular reference.

Can AI tools really help with Excel formula issues?

Yes, tools like ChatGPT and Copilot can analyze a formula, suggest corrections, and explain errors quickly. They cannot see your workbook settings though, so rule out Manual calculation mode and Text formatted cells first.

Related Articles

How to Show Formulas in Excel

How to Find a Circular Reference in Excel

Everything You Need to Know About Excel Formula Errors

How to Insert a New Line in an Excel Cell

How to Rotate Text in Excel Using AI

How to Extract Text from a Cell in Excel