When an Excel formula is not working, the problem is often calculation mode, text formatting, hidden formulas, stale links, or a blocked connection. Start with the quick checks below, then move into references, add-ins, and repair steps until Excel returns the right result.
1. Switch Calculation Back To Automatic
Start here when formulas look right but refuse to update after you change a value.
In Excel for Windows, go to File > Options > Formulas > Calculation options > Workbook Calculation, then choose Automatic. You can also use Formulas > Calculation > Calculation Options > Automatic.
This desktop setting affects all open workbooks, so fixing it once can wake up more than one file.
2. Recalculate The Workbook Now
- 1.Select Formulas.
- 2.Choose Calculate Now to calculate all open worksheets.
- 3.Choose Calculate Sheet to calculate only the active worksheet.
If the workbook is set to manual calculation, this forces Excel to refresh the results. Shortcuts work too: press F9 to calculate all worksheets in all open workbooks, Shift + F9 for the active worksheet, Ctrl + Alt + F9 to force all worksheets, or Ctrl + Alt + Shift + F9 to recheck dependent formulas and calculate all cells.
In Excel for the web, recalculate with Formulas > Calculation Options > Calculate Workbook. Older browser-based workbooks use Data > Calculate Workbook. F9 also works.
3. Turn Off Show Formulas
If cells display =SUM(A1:A10) instead of the answer, Excel is showing the formula text. Select Formulas > Show Formulas to display results again.
On Windows, press Ctrl + ` for the same switch. In Excel for the web, use Formulas > Show Formulas.
4. Re Enter Formulas Stored As Text
- A working Excel formula begins with =. Select the cell or formula bar, make the entry start with =, then press Enter. Use =SUM(A1:A10), not SUM(A1:A10).
- For a copied apostrophe, select the cell, press F2, delete the apostrophe before the formula, then press Enter.
- For cells formatted as text, select the affected cells, open Home, choose the Number Format drop-down, select General, then edit each formula cell and press Enter.
- On Excel for Android, tap the cell or formula bar, edit the formula so it begins with =, then tap the check mark.
5. Convert Numbers Stored As Text
Sometimes the formula is fine and the referenced values are the problem.
If Excel marks a number with the green error indicator, use that indicator to convert the value. For imported data with hidden spaces or nonprinting characters, clean the text with TRIM or CLEAN.
Keep Excel warning you about this through File > Options > Formulas > Error checking rules, with Numbers formatted as text or preceded by an apostrophe enabled.
6. Repair Formula Errors And References
- Use Formulas > Insert Function on the problem cell to check the function name and syntax.
- While typing, use Excel autocomplete and syntax help to fix misspelled functions, missing quotes, missing range separators, and undefined names.
- For #NAME? from an undefined name, select the intended cells and go to Formulas > Defined Names > Define Name > Define Name. Enter the name details, then choose OK.
- To insert an existing name, use Formulas > Defined Names > Use in Formula.
- For circular references, open Formulas > Error Checking > Circular References, select each listed address, then edit the formula so it no longer points back to itself.
- For #REF!, undo the deleted or pasted-over referenced cells, or edit the formula so it points to valid cells and ranges.
- For #N/A in lookup formulas, verify that the lookup value exists and that XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, or MATCH points to the correct range and match mode.
7. Match Separators And Add Ins
When Excel cannot parse a formula or recognize a function, check regional separators and required add-ins.
- 1.For decimal and thousands separators in Windows Excel, go to File > Options > Advanced > Editing options, clear Use system separators, enter the separators, then choose OK.
- 2.For the Windows list separator, open Region Settings, then go to Administrative language settings > Formats > Additional settings > Numbers > List separator and choose OK twice.
- 3.In Excel for the web, use File > Options > Regional Settings, choose the region, then select Change. This works only when the workbook is stored on a Microsoft host and third-party cookies are enabled; for an organization account, the default comes from the SharePoint site's regional settings.
- 4.For missing add-in functions, use File > Options > Add-ins > Manage > Excel Add-ins > Go, check the required add-in, then choose OK.
- 5.For the Analysis ToolPak on Windows, go to File > Options > Add-Ins > Manage > Excel Add-ins > Go > Analysis ToolPak > OK. On Mac, use Tools > Excel Add-ins > Analysis ToolPak > OK.
8. Refresh Links And Unblock Content
- Refresh external data with Data > Refresh All. For selected data, open the drop-down next to Refresh All and choose Refresh.
- Refresh workbook links through Data > Queries and Connections > Workbook Links > Refresh All.
- Repair a broken workbook source through Data > Queries and Connections > Workbook Links, open the link options, choose Change source, then select the correct source file.
- For a trusted workbook with a security warning, choose Enable Content. If Excel asks to make it a trusted document, choose Yes only for a file and source you trust.
- For #BLOCKED!, read the specific message first, because causes vary: you may need to sign in, get the required license, or use a supported region or version. For blocked connections and linked data, verify file permission, then check File > Options > Trust Center > Trust Center Settings for external content or privacy settings.
Formulas that depend on workbook links, external data, add-ins, or custom functions need the source content available and trusted.
On a work or school device, organization policy can block add-ins, links, macros, connected experiences, or Office Store access. When Excel says a setting is managed by your organization, contact your IT admin.
For a trusted XLL add-in on Windows, open File Explorer, go to the XLL file, right-click it, choose Properties > General > Unblock, then choose OK.
9. Repair Excel Or The Workbook
Use these when the same formulas work in other files, Excel behaves differently in Safe Mode, or the workbook itself is damaged.
- 1.Open Excel in Safe Mode by holding Ctrl and double-clicking the Excel shortcut, then choosing Yes. You can also open Run, type excel /safe, and choose OK.
- 2.If Safe Mode fixes the problem, disable COM add-ins through File > Options > Add-ins > Manage > COM Add-ins > Go. Clear the check boxes, choose OK, restart Excel normally, then re-enable add-ins one at a time.
- 3.For a corrupted workbook, use File > Open, select the workbook, choose the arrow next to Open, then select Open and Repair > Repair. If repair fails, choose Extract Data to extract values and formulas.
- 4.Update Excel for Windows by opening a Microsoft 365 app, creating a new document, then choosing File > Account or Office Account > Product Information > Update Options > Update Now. Choose Enable Updates first if that option appears.
- 5.Update Excel for Mac (Microsoft 365 or Office 2021 on macOS 12 or later) through Help > Check for Updates, then choose Update or Update All in Microsoft AutoUpdate and restart Excel. Office 2019 for Mac reached end of support on October 10, 2023 and no longer receives updates, so updating cannot restore its functionality.
- 6.Repair Microsoft 365 or Office on Windows by finding Microsoft 365 or Office in installed apps or programs, choosing Modify, running Quick Repair, then running Online Repair if Quick Repair does not fix Excel.
Frequently Asked Questions
Why does Excel calculate only after I press F9?
Workbook Calculation is set to Manual. Change it to Automatic through File > Options > Formulas > Calculation options > Workbook Calculation, or use Formulas > Calculation Options > Automatic.
Why does Excel show #BLOCKED! in a formula cell?
Excel is blocking a connection, linked file, add-in, privacy setting, or permission that the formula needs. Use Enable Content only for trusted files and check Trust Center settings.
How do I fix formulas after changing a cell from Text to General?
After switching the cell to General, edit the affected formula cell and press Enter, or re-enter the formula so Excel parses it again.
What should I do when Excel formulas depend on another workbook?
Refresh or repair the source through Data > Queries and Connections > Workbook Links. Use Change source when Excel points to the wrong file.
