How to Recalculate in Excel (2 Built-In Options + Bonus)

Recalculation means calculating the existing formula based on the change of data in Excel. When we input any formula in a cell of Excel, we get the result based on the values. But if we change any data on which the formula is dependent, then Excel needs to recalculate the formula. In this article, we will discuss how to recalculate in Excel with proper explanations and examples.

How to Recalculate in Excel: 2 Ways

There are two built-in ways to recalculate in Excel. One is automatic, and the other is a manual process. In the below section, we discussed both methods in detail. We will consider the following dataset to show how to recalculate in Excel. Lastly, we will show another way that needs VBA to be used.

1. Recalculate Automatically in Excel

In this section, we will show how to recalculate in Excel. Look at the below section and see how automatic recalculation performs in Excel.

We can see recalculation performs accurately. There are several ways in Excel to recalculate automatically.

i. Recalculation from the Automatic Button of Calculation Options

We can enable automatic recalculation in Excel from the ribbon.

📌 Steps:

• Go to the Formulas tab.
• Choose Calculation Options from the Calculation group.
• Choose Automatic as the Calculation Options.

After this, the recalculation will perform automatically in Excel.

ii. Refresh Sheet Automatically from Excel Options

We can enable automatic recalculation from the Excel Options window.

📌 Steps:

• Go to File >> Options.
• Select the Formulas option from the Excel Options.
• Then, find out the Calculation options section.
• Mark the Automatic option as the Workbook Calculation.
• Finally, press the OK button.

After performing this task, Excel will be able to recalculate automatically.

Read More: How to Make a Recalculate Button in Excel

2. Recalculate Manually in Excel

In the previous section, we already showed how to recalculate automatically in Excel. Now, we will want to recalculate manually in Excel.

We can see manual calculation is set for this Excel file.

i. Update Cells Using ‘Calculate Sheet’ Option

Now we will apply the ribbon shortcut to recalculate manually in Excel.

📌 Steps:

• Go to the Formula tab.
• We will see two options in the Calculation group for recalculation.

If we want to recalculate the whole workbook manually choose the Calculate Now option. Or if we need to recalculate the current worksheet only, then click on the Calculate Sheet option.

• Look at the dataset.

We can see recalculation has been performed successfully.

ii. Using Keyboard Shortcuts

Here, we will use a keyboard shortcut to recalculate manually in Excel.

📌 Steps:

• Go to Cell E5 and edit the cell by pressing the F2 button.
• Then, press the Enter button to recalculate manually.

There are also other keyboard shortcuts to recalculate manually.

How to Force Recalculate Using VBA Macro

However, you can also recalculate forcefully using VBA macro. See how to do this.

📌 Steps:

• Go to the Sheet Name at the bottom of the worksheet.
• Press the right button of the mouse.
• Choose View Code from the Context Menu.

• Choose the Module option from the Insert tab.

• The VBA module window appears.
• Insert the following VBA code in the module.

Sub Recalculate_Workbook()
Dim W_Sheet As Worksheet
For Each W_Sheet In Worksheets
W_Sheet.Calculate
Next W_Sheet
End Sub

• Now, run the code by pressing the F5 button.

Look at the dataset. This VBA code recalculates the whole workbook. But if we want to recalculate only the current worksheet, then we will use this VBA code.

Public Sub Recalculate_Current_Sheet()
EnableCalculation = False
EnableCalculation = True
Calculate
End Sub

This will be applicable only to the current sheet.

Read More: How to Force Recalculation with VBA in Excel

How to Enable Iterative Calculation in Excel

In this section, we will show how to enable iterative calculation in Excel.

📌 Steps:

• Go to File >> Options.
• Then, select the Formulas option from the left side.
• Find out the Calculation options from the right side.
• Then, mark the Enable iterative calculation option.

We get two relation options here.

By default, the value of Maximum Iterations is 100. If this value is higher, the recalculation will take work slowly.

The value of Maximum Change is 0.001. If this value is smaller then the recalculation will give a more accurate result.

Things to Remember

Sometimes it is seen that recalculations do not work properly. Look at the below image.

We can see data of Cell C5 and D6 are in text format. So, when we apply recalculation the formula does not work. This is because data is in text form. Now, change the data in a format to recalculate successfully.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described how to recalculate in Excel with automatic and manual processes. We also mention why we face problems in the recalculation. I hope this will satisfy your needs.

<< Go Back to Excel Calculation Options | How to Calculate in Excel | Learn Excel