Refresh Excel Formulas (Fix IF Not Calculating)

Key Points

• Go to the Formulas Tab > Calculations > Calculation Options > Automatic to activate the automatic calculation.
• You can use the keyboard shortcut current sheet using the keyboard shortcut (Shift + F9).
• You can also use the keyboard shortcut to calculate the entire workbook using the F9 key.

Formulas are the power of any spreadsheet program. Excel is the most powerful and popular spreadsheet application in the world, and it has an extensive list of Excel functions and formulas.

If you are one of those Excel users who uses formulas a lot, I will share a tutorial that will help you a lot today. When using formulas, there’s one problem: sometimes, Excel doesn’t calculate the formulas or refresh the results.

Refresh the Formulas

The easiest way to refresh the formulas is to use the keyboard shortcut F9 or you can also use the options from the Formula Tab in the Ribbon.

In the tutorial, we will learn about all these options in detail.

Change the Calculation Style in Excel to Fix the Formulas

If you are facing a problem where formulas are not recalculating, the best way to deal with this problem is to check the calculation style and then change it.

Here are the steps for this:

1. First, make sure to open the workbook.
2. After that, go to the Formula tab on the Ribbon.
3. From there, “Calculation” and then click on the “Calculation Option”.
4. In the end, click on the “Automatic” to activate the option.

When you activate the option, Excel will start calculating the formula automatically. That means it automatically recalculates all formulas whenever any change is made in the workbook. This ensures that the data is always current and reflects any updates immediately.

Change Formula Calculations from Excel Options

Apart from the above option, you can activate the “Automatic” calculation style from the Excel Options.

For this, you need to use the below steps:

1. First, click on the “File” tab to open the backstage view, then click “More”.
2. Now, click on the “Options” to open the Excel Options.
3. Next, go to “Formulas” to open the options.
4. In the end, from there, click in the “Calculation Options”, and in the “Workbook Calculation” select the “Automatic” from there and click “OK” to save the settings.

This is the same option you activate from the Formulas tab on the Ribbon.

Calculate Sheet

When you have manual calculation activated, you can only calculate the formula from the current worksheet. All you need to do is go to the “Formula” tab and click the “Calculate Now” button.

You can also use the keyboard shortcut Shift + F9.

Calculate Now

If you want to calculate the entire workbook just for one time, in that case, you can click on the “Calculate Now” button from the formula tab.

You can also use the keyboard shortcut F9.

Types of Calculations in Excel for Formulas

It’s important to understand the types of calculations you can apply.

• Automatic: Excel automatically recalculates all formulas whenever any change is made in the workbook. This ensures that the data is always current and includes any updates immediately.
• Partial: This mode means “Automatic Except for Data Tables.” In this mode, Excel automatically recalculates all formulas except those within data tables, which are recalculated when you manually trigger it.
• Manual: With this mode, Excel does not recalculate formulas automatically. However, you need to manually recalculate the formula by pressing F9 or choosing to recalculate the entire workbook or specific sheets. This mode is useful when working with extensive data in workbooks to prevent slowdowns of a workbook.

VBA Codes to Change Calculation to Fix Formula

You can also run a VBA code to change the calculation mode and fix the formula refresh. Below, we have codes for each of the calculation modes.

Sub SetCalculationToAutomatic()
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Calculation mode set to Automatic."
End Sub
Sub SetCalculationToManual()
    Application.Calculation = xlCalculationManual
    MsgBox "Calculation mode set to Manual."
End Sub
Sub SetCalculationToAutomaticExceptDataTables()
    Application.Calculation = xlCalculationSemiautomatic
    MsgBox "Calculation mode set to partial."
End Sub
Sub RecalculateActiveSheet()
    ActiveSheet.Calculate
    MsgBox "Active sheet recalculated."
End Sub
Sub RecalculateWorkbook()
    Application.Calculate
    MsgBox "Entire workbook recalculated."
End Sub

You can use these codes as per the calculation you want to apply. To run these codes, you need to open the “Visual Basic Editor” and insert a new module, paste the code there, and then click on the run button to run the code.

When Formulas Not Working Because Numbers are Saved as Text

If you have an apostrophe (‘) before a number in a cell in Excel, it makes that number a text value rather than a numeric value, or if the format of the cells is already converted into a text and when you enter a number there, it adds an apostrophe before it.

Changing the calculation mode won’t make any difference in this situation.

To correct these numbers, you need to convert these numbers into actual numbers by removing apostrophes. For this, you can use the function VALUE, or you can also use find and replace to remove apostrophes from all the cells in one go.

Circular reference can cause formulas not to recalculate and refresh

Yes, that is right. In Excel, the circular reference prevents a formula from recalculating because it creates a situation where a formula depends on its result, either directly or indirectly.

This can lead to an endless loop of calculations that Excel cannot resolve unless you have configured it to handle such a situation.

By default, when Excel detects a circular reference, it usually shows a warning. This way, Excel alerts you that the formula cannot be calculated as required.

When you click OK, this alert will disappear. But if Excel recalculates a formula with a circular reference without limits, it could enter an infinite loop, constantly recalculating without ever reaching a solution.

Direct Circular Reference

This happens when a formula refers directly to its own cell address. For example, if you enter =A1 + 1 in cell A1, Excel will recognize that the formula refers to itself, creating a circular reference.

When you face a situation where you have a circular reference in your formulas, you might get a 0 in the result while entering the formulas, as we have in the above example. Even though you have a 1 in the formula along with the

Indirect Circular Reference

This happens when a formula refers to another cell, the original cell back. For example, a circular reference is formed if cell A1 contains =B1 + 1 and cell B1 contains =A1 + 2.

Is there a way to handle circular reference to make the formula work?

You can handle circular reference-making and activating the iterative calculation.

When you activate the Iterative Calculation, it allows Excel to recalculate the formula until it reaches a result, which is useful in cases where circular references are necessary, such as in financial models or iterative algorithms.

For this, all you need to do is to follow the steps below.

1. First, click on the “File” tab to go to the backstage view of Excel.
2. After that, click on “More” and then click on “Option”.
3. Now, go to the “Formulas” section in the Excel Options.
4. Next, tick mark the “Enable iteration calculation” and enter the Maximum iteration and Maximum Change.
5. In the end, click OK to save the settings.

Below is a simple way to understand both Maximum iteration and Maximum Change.

• So, Maximum Iterations decides how many times Excel will recalculate the formula. For example, if set to 100, Excel will recalculate up to 100 times. Higher numbers can give more accurate results but may slow down your Excel workbooks.
• Maximum change decides the value change between iterations Excel accepts as a stable solution. The default value is 0.001. Smaller values yield more precise results but may require more iterations.

Enabling iterative calculation is a powerful way to handle circular references in Excel. This allows formulas to use their results to get a new result.

Tips to Handle When Formulas Are Always Refreshing

There are a few points that you can consider in making sure that the formulas in the Excel files are always refreshing.

• Keep Calculation Mode on Automatic – This setting ensures that Excel recalculates all formulas automatically whenever a change is made in the workbook.
• Minimize External Links – If you have links to data in external workbooks, they may not always refresh automatically. Go to Data > Edit Links > Startup Prompt and choose the appropriate option to deal with this.
• Data Connections with Auto-Refresh – When data is imported from external sources, like Power Query, you must ensure the data refresh is on auto-refresh.
• Dynamic Named Ranges – You can use named ranges that dynamically update the range when there are new entries in the data. This ensures that the formula references the range with the complete data and gives your updated result.