Excel Formulas Not Calculating
A Latina woman in her twenties working on a spreadsheet on a large monitor in a quiet home office, notepad and pen on the desk

Excel Formulas Not Calculating

When an Excel formula is not calculating, Calculation Options is usually set to Manual. Go to Formulas > Calculation Options > Automatic, or press F9 to recalculate now. If a cell shows the formula itself instead of a result, press Ctrl+` to turn off Show Formulas, or change the cell from Text to General and re-enter it.

Try these quick fixes first

  1. Select Formulas > Calculation Options > Automatic.
  2. Press F9 to recalculate, or Ctrl+Alt+F9 to recalculate every formula.
  3. If every cell shows a formula instead of a result, press Ctrl+` to turn off Show Formulas.
  4. If one cell shows its formula, change its format from Text to General, then press F2 and Enter.
  5. Check the formula bar for an apostrophe or a space before the equal sign, and delete it.
  6. Look at the status bar for Circular References or Calculate.

Set Calculation Options back to Automatic

In Manual mode, Excel recalculates only when you ask it to, so results don’t change when you edit the numbers they depend on.

  1. Select the Formulas tab. In the Calculation group, select Calculation Options > Automatic.
  2. Or go to File > Options > Formulas, select Automatic under Workbook Calculation, and select OK.
  3. On a Mac, go to Excel > Preferences > Calculation and select Automatic under Calculation Options.
  4. In Excel for the web, select Formulas > Calculation Options > Automatic.

Two clues point to Manual mode: Calculate in the status bar, which means a workbook needs recalculating, and, in Excel for Microsoft 365, cells shown with a strikethrough because their values are out of date.

Why does Excel keep switching back to Manual?

Excel takes the calculation mode from the first workbook you open in a session, uses it for every open workbook, and stores the current mode in each workbook you save.

  1. Close all your workbooks, then open the one you usually open first.
  2. If Formulas > Calculation Options shows Manual, select Automatic.
  3. Save the workbook so it stores Automatic.
  4. Do the same for any template you start new files from, because a new file takes its mode from its template.

Some large workbooks are saved in Manual on purpose because they’re slow to calculate. If a shared file is set up that way, ask its owner before you change it, and press F9 when you need fresh results.

Force a recalculation with F9

Recalculating makes Excel update results right away, even in Manual mode.

  1. Press F9 to recalculate formulas that changed, and the formulas that depend on them, in all open workbooks.
  2. Press Shift+F9 to recalculate only the sheet you’re on.
  3. Press Ctrl+Alt+F9 to recalculate every formula in all open workbooks, whether it changed or not.
  4. If results still look wrong, press Ctrl+Shift+Alt+F9. Excel checks dependent formulas, then recalculates all of them.
  5. To use the ribbon instead, select Formulas > Calculate Now or Calculate Sheet.

On a Mac, press Command+= or F9 to calculate all open workbooks, or Shift+F9 for the active sheet. In Excel for the web, press F9 or select Formulas > Calculation Options > Calculate Workbook.

Turn off Show Formulas

Show Formulas makes every cell on the sheet display its formula instead of the result, so it looks like nothing calculated.

  1. Select the Formulas tab, then select Show Formulas in the Formula Auditing group to switch back to results.
  2. Or press Ctrl+`. The grave accent key (`) is usually next to the 1 key in the upper-left corner of the keyboard.
  3. Show Formulas is set for each worksheet separately, so check any other sheet that shows formulas.

The same Show Formulas button and Ctrl+` shortcut work in Excel for Mac, and Excel for the web has the Show Formulas button too.

Change cells formatted as Text to General

Excel can’t calculate a formula typed into a cell formatted as Text, so it shows the formula exactly as you typed it.

  1. Select the cell. If the Number Format box on the Home tab says Text, that’s the problem.
  2. Press Ctrl+1 (Command+1 on a Mac), select General, and select OK.
  3. Press F2, then Enter, so Excel reads the formula again.
  4. For a whole column of these cells, select the column, apply General, then select Data > Text to Columns > Finish.

If a formula calculates but gives an unexpected result, the numbers it uses may be stored as text. Select those cells, select the error indicator next to them, and choose Convert to Number.

Remove an apostrophe or space before the equal sign

An entry that doesn’t start with an equal sign isn’t a formula, so Excel treats it as text and never calculates it.

  1. Select the cell and look at the formula bar. An apostrophe at the start isn’t displayed in the cell itself.
  2. Select in the formula bar and delete the apostrophe, or any spaces in front of the equal sign.
  3. Press Enter.
  4. If the formula has no equal sign at all, type = at the start and press Enter.

Find and fix circular references

A circular reference is a formula that refers to its own cell, directly or through other cells, and it can leave the cell showing 0 or an old result.

  1. Look at the status bar. Circular References followed by a cell address points to one of them.
  2. Select Formulas > Error Checking > Circular References, then select a cell in the list to go to it.
  3. Change the formula so it no longer includes its own cell, or move it to another cell.
  4. Repeat until Circular References disappears from the status bar. If it shows no address, check your other sheets and other open workbooks.

Don’t turn on iterative calculation just to make the warning go away; Microsoft recommends keeping it off in most worksheets. In Excel for the web, formula auditing is limited, so open the file in the Excel desktop app to find circular references.

Refresh or fix links to other workbooks

Formulas that pull data from another workbook can show out-of-date values until Excel refreshes the links.

  1. If a Security Warning bar appears when you open the file, select Enable Content only if you trust the workbook and where it came from. That trusts it for this session and updates the links.
  2. Select Data > Queries and Connections > Workbook Links, then select Refresh all.
  3. If a source file was moved or renamed, select More Commands (…) next to it, select Change source, and pick the file in its new location.
  4. Avoid Break links unless you mean it. It replaces the linked formulas with their current values and can’t be undone, so save a copy first.

In Excel for the web, select Trust workbook links instead, and expect refreshed values to take a few minutes to appear. If links were broken by mistake in a OneDrive or SharePoint file, you can restore a previous version of the Excel file.

Why does this happen?

  • Calculation was set to Manual, either by someone changing it or by a workbook saved in Manual that was the first file opened.
  • Show Formulas was turned on with Ctrl+` or the Formulas tab.
  • The cell was formatted as Text before the formula went in.
  • The entry starts with an apostrophe or a space, or has no equal sign.
  • A formula refers back to its own cell.
  • Linked workbooks weren’t refreshed, or they moved.
  • Numbers the formula uses are stored as text.

When should you call IT?

  • Formulas still won’t calculate in any workbook, even new ones, after you set Automatic.
  • Excel stops responding or crashes while it calculates.
  • Linked workbooks are on a server or SharePoint site you can’t open, or links broke after files moved.
  • A shared workbook that billing, payroll or reporting depends on shows wrong totals. Don’t change its settings without asking its owner.
  • Several people see the same problem with the same files.

If shared workbooks in OneDrive or SharePoint cause trouble across your team, read about our Microsoft 365 support.

OAC Technology’s IT support team fixes problems like this for businesses across the Twin Cities. Clients can call 952-548-5558, Monday to Friday, 8 a.m. to 6 p.m. Central. Not a client yet? Call the same number or send us a message.

Common questions

Why is my Excel formula not calculating?

Usually because calculation is set to Manual. Select Formulas > Calculation Options > Automatic, or press F9 to recalculate. If the cell shows the formula as text, check Show Formulas, the cell’s format, and the start of the entry.

Why is my formula showing as text and not calculating?

Show Formulas is on, the cell is formatted as Text, or the entry starts with an apostrophe or a space. Press Ctrl+` to turn off Show Formulas, change a Text cell to General and press F2 then Enter, or delete the extra character.

How do I force an Excel formula to calculate?

Press F9 to recalculate all open workbooks, Shift+F9 for the active sheet, or Ctrl+Alt+F9 to recalculate every formula whether it changed or not. Editing a formula and pressing Enter also calculates it, even in Manual mode.

Why are my Excel calculations not accurate?

Numbers stored as text can cause unexpected results, so look for the error indicator and choose Convert to Number. Also check that calculation is set to Automatic and that the status bar doesn’t show Circular References.

What does a circular reference warning mean?

It means a formula refers to its own cell, directly or through other cells, so Excel may show 0 or an old result. Select Formulas > Error Checking > Circular References to find the cell, then change the formula.

Related guides

Author picture

OAC Technology

Author

Related Articles