How to fix Excel formulas not calculating (Refresh Formulas)

Formulas are the life and blood of Microsoft Excel.

We use them to add numbers, subtract dates, and even extract texts.

When entering a formula, the result comes almost immediately!

But what happens when it doesn’t?

Obviously, 2+2 = 4! Not 5!

Could we actually be better at basic math than Excel? Probably not 🤣

So, how do you fix a formula that won’t calculate automatically?

In this tutorial, you learn about why your formulas are not updating and how to fix them!

If you want to tag along, download the sample Excel file here.

Let’s dive right into the most common cause of formulas not updating:

Calculation options set to ‘Manual calculation’ mode

What? 😲

Could it really be that simple?

In Excel, it is actually possible to change the calculation setting.

You can check and set the current calculation mode like this:

1. Click the Formulas tab.

2. Click on Calculation Options.

3. Verify that the calculation setting is Automatic.

4. Formulas will not recalculate automatically if Excel is set to Manual calculation mode.

In the practice Excel workbook, the formula in cell C2 is a simple addition formula:

=A2 + B2

You can change the values of A2 & B2 as you wish…

…but the formula result will not change while the setting is still in Manual calc mode.

5. To get the correct result, set the Calculation Options to Automatic calculation mode.

Voila!

Now you can go back to working with Excel as usual!

Alternatively…

You can also change the calculation mode by going into File > More… > Options > Formulas tab.

There are four calculation modes to choose from:

• Automatic – All dependent formulas in the entire workbook are recalculated as cell values change.
• Automatic Except for Data Tables – Same as Automatic. But Data Tables are only recalculated if a cell inside the table has changed
• Manual – The entire workbook is only recalculated if you press F9. Or if you click Calculate Now or Calculate Sheet in the Formulas ribbon
• Manual / Recalculate before saving – Same as Manual calc mode. But formulas are also recalculated every time you save the file.

But why did my Calculation Mode change? 🤔

Bear in mind that the calculation setting is an application-level setting.

If you change the Calculation Mode, this applies to the entire workbook and all other open workbooks.

Also, Excel uses the last saved calculation mode of the first workbook opened. All workbooks opened in the same session will use the same mode.

Running a macro can also change the calculation mode

This is especially true if the macro or VBA developer used any of the lines below:

• Application.Calculation = xlCalculationAutomatic
• Application.Calculation = xlCalculationManual

If you are not working with a macro-enabled workbook, then you can definitely rule this out!

Why use ‘Manual calculation’ mode?

Well, if you are working with a large amount of data, you may notice a slight lag or delay in Excel. 🐌

This is usually caused by Excel automatically recalculating formulas with every change.

This can slow your work down quite a lot.

💡 So, you can change to Manual calculation mode as you enter or change data and switch back to Automatic later on.

Manual calculation shortcuts

In Manual mode, you can refresh formulas by pressing F9.

You can also click the Calculate Now or Calculate Sheet buttons in the Formulas ribbon.

This can help you save time and avoid the stress of waiting for Excel to finish updating formulas!

Cell is formatted as text

Wrong cell format could also prevent the automatic calculation of a formula.

Take a look at the worksheet “Example-Text Format” of the practice Excel file.

The Excel formula in cell C2 is exactly the same as the formula in cell C2 of the worksheet “Example-Calc Mode”.

But in the worksheet “Example-Text Format”, it only shows the formula and not the value.

Even if the Excel workbook is not set to Manual calc mode, the cell value will not update.

To fix this, you can change the cell format:

1. In the Home ribbon, click on the Number Format drop-down.

2. Select your desired number format.

The General and Number formats are usually used for calculations.

3. Double-click on the cell or click the formula bar.

The cell references should are now highlighted as they normally are in an Excel formula.

4. Hit Enter to get the result!

Excel set to show formulas instead of results

Another thing to consider is the Show Formulas feature.

If this is ON, cells will show the formulas instead of the values.

You can toggle it ON and OFF by clicking the Show Formulas button in the Formulas ribbon.

You can also use these shortcuts to toggle display between formulas and values:

• On Windows: Use Ctrl + ‘
• On Mac: Use ^ + `

The Show Formulas feature changes the display between formula and cell value.

This is so you can check for errors and inconsistencies in the entire workbook.

Try it out for yourself!

There’s a circular reference somewhere in your workbook

Are you still having issues even after the above fixes?
If so, you might have a circular reference in your workbook.

This is when a formula refers to its own cell either directly or indirectly.

In the worksheet “Example-Circ Ref 1” of the practice workbook, we have a simple SUM formula at cell B6.

The formula includes itself in the calculation “=SUM(B2:B6)”.

Thus, the formula will not calculate correctly.

You can identify and fix formulas with circular references like this:

1. In the Formulas ribbon, click on Error Checking.

2. This opens a drop-down. Select Circular References.

It will then show you a list of cells with circular references that need fixing.

Alternative method to check for circular references

You can check the bottom left corner of the Excel window.

It will display a message like the one below if there are circular references.

You may also see blue lines that show formulas dependent on one another.
Open the worksheet “Example-Circ Ref 2” for this next example.

Here you have slightly more complex formulas involving a budget spreadsheet.

The example above shows two ways to calculate the Contingency and Total Project Cost.

• In Calculation A: The formula for the Total Project Cost at cell B9 is “=SUM(B4:B8)” while the formula for the 5% Contingency at cell B7 is “=B9*0.05”.
  Thus, cells B7 and B9 have formulas dependent on one another. This is a circular reference and the Excel formulas default to zero.
• In Calculation B: This circular reference is fixed by having a Sub-total at cell F7.
  This way, the Contingency can be computed at cell F8 and the Total Project Cost is now just “=F7+F8”.
  There are no circular references here so the values are computed correctly!

You can learn more about this and other examples in our tutorial about circular references here.

That’s it – Now what?

You are now familiar with the four most common reasons why your Excel formulas won’t automatically calculate.

Reason number 1, the Calculation Mode setting, is almost always the culprit ⚙️

This is especially true when you open workbooks downloaded from the Internet. Or if you open workbooks from a different computer.

You will encounter wrong cell Text formatting, Show Formulas toggle, and Circular References less often.

But all this doesn’t matter if you don’t know how to actually build formulas and functions that make your work easier.

Functions like IF, SUMIF, and VLOOKUP.

You learn all these (and more!) in my free 30-minute training. Click here to enroll.

Other relevant resources

You just read what to do when the formulas don’t update. But what if you get different error messages like #VALUE, #REF, and #NAME?

That’s an entirely different part of Excel and you can read all about that here.

Or you can read here how to use IFERROR or ISERROR to work with errors and have formulas work all the time!