Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Recalculate in Excel (3 Effective Methods)

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 EXecl needs to recalculate the formula. In this article, we will discuss how to recalculate Excel with proper explanations and examples.


Download Practice Workbook

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


3 Easy Methods to Recalculate in Excel

There are two 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.


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.

Recalculate automatically 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.

Recalculate automatically from Ribbon

After this, the recalculation will perform automatically in Excel.

Read More: How to Make a Recalculate Button in Excel (with Easy Steps)


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.

Recalculate automatically from Excel Options

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

Read More: How to Make a Calculator in Excel (with Easy Steps)


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.

Recalculate manually from ribbon

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.

Read More: How to Fix Formula in Excel (9 Easy Methods)


Similar Readings


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.

Excel shortcut to Recalculate

There are also other keyboard shortcuts to recalculate manually.

  • F9: This recalculates the formula of all open workbooks. This will work if we make any after the last calculation and if the formulas are dependent.
  • Shift+F9: This recalculates the formulas of the current sheet only after any modification of data.
  • Ctrl + Alt + F9: This also recalculates all the opened workbooks. If we have any confusion that some calculations returned the wrong result, then we can use this shortcut.
  • Ctrl + Shift + Alt + F9: This shortcut recalculates only the dependent formula of the open workbooks.

Read More: How to Create a Calculator Using Macros in Excel (with Easy Steps)


3. Force Recalculation Using VBA Macro

Here, we will use a VBA macro to recalculate in Excel.

📌 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: 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.


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. Please have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo