If your Excel formulas are not calculating, the most common cause is that calculation is set to Manual, and the fix is to press F9 to recalculate or switch back to Automatic via Formulas > Calculation Options. Other frequent culprits include cells formatted as text, accidentally entered formulas as text, or a circular reference that halts calculation.
Why Excel Stops Calculating Automatically
Excel’s default calculation mode is Automatic, meaning it recalculates whenever you change a value or formula. However, several settings and conditions can break this behavior:
- Manual calculation mode – Often triggered accidentally by pressing Ctrl+` (the grave accent key, which toggles formula view) or by a previous workbook that was saved with manual mode.
- Formulas entered as text – If a formula starts with a space, apostrophe, or the cell is pre-formatted as Text, Excel treats it as a string, not a calculation.
- Circular references – When a formula refers to its own cell (directly or indirectly), Excel stops calculating that cell and shows a warning.
- Calculation settings per workbook – The calculation mode is stored with each workbook, so one file can be manual while others are automatic.
How to Force a Recalculation
If you just need the current values updated quickly, use these shortcuts:
- F9 – Recalculates all open workbooks.
- Shift+F9 – Recalculates only the active sheet.
- Ctrl+Alt+F9 – Forces a full recalculation, ignoring any cached values (Windows). On Mac, use Cmd+Option+F9.
These are especially useful when you’ve set calculation to manual intentionally (for large models) and want to update on demand.
Switching Back to Automatic Calculation
To restore automatic calculation permanently for the current workbook:
- Go to the Formulas tab on the ribbon.
- Click Calculation Options (in the Calculation group).
- Select Automatic.
On Mac, the path is Formulas > Calculation Options as well, but the menu is under the same tab. This setting applies to the workbook you are in; if you want it as the default for all new workbooks, go to File > Options > Formulas (Windows) and set Calculation options to Automatic. On Mac, it’s Excel > Preferences > Calculation.
Common Problems and Their Fixes
Beyond calculation mode, here are the typical reasons formulas appear not to calculate, with solutions:
- Cell formatted as Text – Select the cell, go to Home > Number Format, choose General, then press F2 and Enter to re-enter the formula.
- Formula shows as text (e.g., =A1+B1 displayed literally) – Check for a leading apostrophe or space. Remove it, then re-enter the formula. Also, ensure the cell isn’t formatted as Text before typing.
- Circular reference – Look at the status bar (bottom left) for “Circular References” and a cell address. Trace precedents with Formulas > Trace Precedents to find the loop, then correct it.
- Calculation set to Manual for that workbook – As above, press F9 or change the mode.
- Formulas not updating when dragging fill handle – If you drag a formula down and the results don’t change, check that calculation is automatic. If it is, ensure the referenced cells are correct (relative vs absolute references).
How to Check if a Formula is Being Calculated
To diagnose whether Excel is actually calculating, try this:
- Press Ctrl+` (grave accent) to toggle between showing formulas and showing results. If you see formulas, that’s just a view mode, not a calculation issue.
- Look at the status bar. If it says “Calculate” on the left, Excel is in manual mode and needs F9.
- Enter a simple formula in a blank cell, like
=1+1. If it returns 2, calculation works. If it shows 0 or the formula text, the issue is formatting or text entry.
Excel vs. Google Sheets: Calculation Differences
Google Sheets always calculates automatically; there is no manual mode. If a formula in Sheets doesn’t update, it’s usually because the cell is formatted as plain text (Format > Number > Plain text) or the formula was pasted with a leading apostrophe. In Excel, manual calculation is a powerful feature for large workbooks, but it’s also the #1 reason for “not calculating” complaints. If you switch between the two, be aware that a workbook saved in Excel with manual mode will open in Sheets with automatic calculation, which can cause performance issues on large files.
Frequently Asked Questions
Why is my Excel formula not calculating when I change a cell?
Most likely, calculation is set to Manual. Press F9 to recalculate, or go to Formulas > Calculation Options > Automatic. Also check that the changed cell is actually referenced in the formula—if it’s not, the formula won’t update.
How do I fix a formula that shows as text?
Select the cell, ensure the number format is General (Home > Number Format > General), then click into the formula bar and press Enter. If there’s a leading apostrophe, delete it. If the cell was formatted as Text before you typed, you must clear the format and re-enter the formula.
What does a circular reference mean and how do I fix it?
A circular reference occurs when a formula refers to its own cell, either directly (e.g., =A1+1 in cell A1) or indirectly (A1 refers to B1, B1 refers to A1). Excel stops calculating that cell to avoid infinite loops. Check the status bar for “Circular References” and the cell address, then edit the formula to remove the self-reference.
Why do my formulas work in one workbook but not another?
Calculation mode is saved with each workbook. One file may have been set to Manual, while another is Automatic. Open the problematic workbook and check Formulas > Calculation Options. Also, check if the workbook has any circular references that were ignored.
Can I set Excel to always calculate automatically?
Yes. On Windows, go to File > Options > Formulas and select Automatic under Calculation options. On Mac, go to Excel > Preferences > Calculation and choose Automatic. This sets the default for new workbooks, but existing workbooks may still have their own setting.
What is the difference between F9 and Shift+F9?
F9 recalculates all open workbooks, while Shift+F9 recalculates only the active sheet. Use Shift+F9 if you have a huge workbook and want to save time by updating just the current sheet.
Preventing Future Calculation Issues
To avoid surprises, adopt these habits:
- Keep calculation mode on Automatic unless you deliberately need manual for performance.
- Always format cells as General or Number before entering formulas, not Text.
- Use the Trace Precedents and Trace Dependents tools (Formulas tab) to understand formula dependencies and spot errors.
- If you receive a workbook from someone else, check its calculation mode first (Formulas > Calculation Options).
If you’ve tried all these steps and formulas still don’t calculate, consider whether the workbook is corrupted. Copy the data to a new workbook (Edit > Select All, Copy, then Paste into a new file) and test. Most calculation issues, however, are resolved by switching to Automatic and re-entering formulas that were accidentally treated as text.
Related
- How to Rename a Cell in Excel: A Complete Guide
- Excel Replace Only in Selected Cells: 2 Ways
- Excel How to Get to Data Validation: Full Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- PowerPoint Poster Slide Size: Set the Right DimensionsTo change a PowerPoint slide to poster size in Microsoft 365 for Windows, select Design > Slide Size…
- Sort in Excel Smallest to Largest: Step-by-Step GuideTo sort values in Excel from smallest to largest, click a cell in the number column, then select…
- INDIRECT in Excel: How to Drag Formulas to New CellsIf you want an INDIRECT formula to follow different cells as you drag it, build its address from…
- Excel Formula for Checking if Cell Is BlankThe simplest Excel formula for checking if a cell is blank is =ISBLANK(A1), which returns TRUE if A1…
- DATEDIF Excel Function: Examples and Common IssuesExcel’s DATEDIF function calculates the time between two dates in years, months, or days. For example, with a…
- COUNTIF Date in Excel: Count Dates, Days, and RangesTo count cells that contain a specific date in Excel, use COUNTIF with a date value as its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.