Excel usually automatically refreshes the formulas used in any workbook or worksheet. But if the formulas are not refreshing automatically we need to do it manually. In this tutorial, we will be showing you step-by-step how to enable the manual calculation and refresh formulas in Excel. We will also provide you with a free Excel workbook for your better understanding.
How to Refresh Formulas in Excel: 2 Easy Methods
Consider the following dataset. It contains study materials and their corresponding per unit price, quantity, and total. Here the formula that we are using to calculate the Total is:
Total = Price Per Unit * Quantity
Let us assume the per-unit price of chairs drops to $5 and the price of benches rises to $15. After updating the dataset will look like this.
But after updating Price Per Unit, Excel isn’t automatically refreshing or recalculating the values in Total. So we need to manually refresh it. There are two methods of refreshing formulas in Excel. We will use the above dataset to illustrate the methods of this article. Before we start manually refreshing the formulas, we have to enable manual calculation. To enable manual calculation follow these steps.
STEPS:
- Firstly, launch the Excel application and click on Options.
- The above command will open a new dialogue box named Excel Options.
- Secondly, from that dialogue box select formulas.
- Thirdly, select Manual instead of Automatic from the Calculation options section.
- Then press OK.
Or if you are already on a workbook follow these steps:
- First, go to the Formulas tab.
- Then, from the Calculation Options dropdown select the Manual option instead of Automatic.
Now we can start refreshing our formulas manually.
1. Refresh Formulas Using Excel Ribbon
Excel already has some built-in functions in the ribbon to manually refresh the formulas and recalculate the worksheet or the entire workbook at once. Here we will describe both methods of refreshing Excel functions in detail.
1.1 In Current Worksheet Only
If there are multiple worksheets in an Excel workbook but we need to refresh only the current sheet, we will use this method. To do so, we should follow these steps.
STEPS:
- In the first place, go to the Formulas tab.
- Next, click on Calculate Sheet from the ribbon. This will refresh the entire calculation of the current worksheet.
- As a result, by clicking Calculate Sheet, the output datasheet will look like this. Here the values of the total quantity of Chair and Bench have been updated.
1.2 Refresh Entire Workbook
If we want to refresh all the formulas of any workbook in Excel, we will follow these steps.
STEPS:
- In the beginning, go to the Formulas tab.
- Now instead of ‘Calculate Sheet’, if we click Calculate Now, our entire workbook formulas will be refreshed at once.
After refreshing the entire workbook as mentioned in 1.2, all the worksheets having updated values of chair and bench should look like the output table of 1.1.
2. Refresh Excel Formulas Using Keyboard Shortcuts
By default, there are some keyboard shortcuts in Excel that can refresh formulas of the entire workbook or worksheet, even single-cell without using the Excel ribbon. These shortcuts and procedures are given below.
STEPS:
- To begin with, click on cell E5 that we want to update. Here we want to update the total value of the chair.
- In addition, press F2. This will allow you to see the following formula used for that cell:
=C5*D5
- Then press Enter to refresh or update the value. Here is one thing that we should notice, the total price of the Bench has not been updated. It’s because the F2 key works on only one cell, not on the entire worksheet or workbook.
More Keyboard Shortcuts:
- Using the F9 key: By pressing the F9 key, you can refresh the entire workbook or all the worksheets at once. Works in the same way as the option Calculate Now works.
- Use of ‘Shift+F’: Refreshes and recalculates all the formulas of the current sheet.
- Utilize ‘Ctrl+Alt+F9’: Force the calculation of all open workbook’s open worksheets, including those with unchanged cells.
- Use ‘Ctrl+Alt+Shift+F9’: Force the calculate all open workbooks’ all worksheets.
Things to remember
- We must enable Manual Calculation before trying any of these methods.
- The F2 key can be used only on one cell, not the entire worksheet or workbook.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
This article shows how simple it is to refresh formulas in Excel. Once more, when working with huge files that include several formulas, the manual computation method is quite useful. Using manual computation will help to increase the efficiency of your Excel files. In the end, this could increase effectiveness and accuracy. If you still facing any problems with these steps, please let us know through the comment section. Our team is waiting to answer all the queries you have.
Related Articles
- [Fixed!] SUM Formula Not Working in Excel
- [Fixed!]: Excel Formula Not Showing Correct Result
- Excel Formulas Not Calculating Automatically
- [Fixed!] Excel Formulas Not Working on Another Computer
- [Fixed!] Formula Not Working in Excel and Showing as Text
- [Solved:] Excel Formula Not Working unless Double Click Cell
- [Solved]: Excel Formulas Not Updating Until Save
- [Solved]: Excel Array Formula Not Showing Result
<< Go Back To Formulas not Working in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
