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.
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.
- 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.
- 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.
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.
- 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.
Read More: How to Fix Formula in Excel (9 Easy Methods)
- Make Games in Excel (with Easy Steps)
- How to Create a Leaderboard in Excel (with Easy Steps)
- Create Fibonacci Calculator in Excel (With Easy Steps)
- How to Create Toggle Button on Excel VBA UserForm
- Create a Fibonacci Pivot Point Calculator in Excel
ii. Using Keyboard Shortcuts
Here, we will use a keyboard shortcut to recalculate manually in Excel.
- 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.
- 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.
3. Force Recalculation Using VBA Macro
Here, we will use a VBA macro to recalculate in Excel.
- 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.
- 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.
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.
- Calculate Cost of Funds in Excel (with Easy Steps)
- How to Calculate Root Mean Square Error in Excel
- Calculate APR in Excel (3 Simple Methods)
- How to Calculate WACC in Excel (with Easy Steps)
- Add Trailing Zeros in Excel (2 Easy Ways)
- Count Words in Excel (6 Easy Methods)
- How to Do Bookkeeping for Small Business in Excel (Step-by-Step)