Written by co-founder Kasper Langmann, Microsoft Office Specialist.
In this tutorial, we are going to look at how to recalculate and refresh formulas in Microsoft Excel.
We will do this using the Calculate Now and Calculate Sheet features in the application.
Excel Vba Manual Calculation
We will also look at using keyboard shortcuts!
How to Refresh Formulas in Excel: Fix Formulas That Aren’t Updating! Written by co-founder Kasper Langmann, Microsoft Office Specialist. In this tutorial, we are going to look at how to recalculate and refresh formulas in Microsoft Excel. We will do this using the Calculate Now and Calculate Sheet features in the application.
- How to Turn Off/On Microsoft Excel's Calculation Mode. Microsoft Excel has the capability for a user to control its workbook calculation mode. A user will set this mode to manual disabling all calculations when a workbook takes a long time to calculate when new values are entered.
- Precision is a measure of the degree of accuracy for a calculation. Excel stores and calculates with 15 significant digits of precision. However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas. Note: When you click Manual, Excel automatically.
We will perform all the examples in this article using Microsoft Excel 2016 for Windows.
Why do you need to recalculate and refresh?
Excel automatically calculates all the formulas within a worksheet.
When there are complex formulas or a large data set, Excel’s performance slows down. This is because it calculates after every change or update.
You may spend more time waiting on Excel to finish than you do on updating or entering information.
To solve this problem, you can set the calculation option from Automatic to Manual. Then you can use the application’s features to manually recalculate formulas.
Two methods to set calculation to manual
For the first method, go to the File tab in the ribbon and select Options.
When the Excel Options dialog box opens, select Formulas from the list of options on the left.
Excel Manual Calculation Key Machine
Select Manual from the Calculation options.
For the second method, go to the Calculation group on the Formulas tab.
Click on the down arrow for Calculation Options and make sure that you select Manual.
Get your FREE exercise file
Before you start:
Throughout this guide, you need a data set to practice.
I’ve included one for you (for free).
Download it right below!
BONUS:Download the Recalculate and Refresh Formulas Exercise Workbook File to go along with this post.
How to recalculate and refresh formulas
Now your formulas will no longer refresh automatically. You will need to know how to recalculate and refresh your formulas in order for changes to take effect.
The first step to recalculate is to go to the Calculation group on the Formulas tab.
Then you click on one of the calculate options where you can select either of two options.
The first option is Calculate Now – this option will calculate the entire workbook.
The second option is Calculate Sheet – this option will calculate the active worksheet.
There are also several keyboard shortcuts to improve your efficiency when refreshing your calculations.
- F2 – select any cell then press F2 key and hit enter to refresh formulas.
- F9 – recalculates all sheets in workbooks
- SHIFT+F9 – recalculates all formulas in the active sheet
- CTRL+ALT+F9 – force calculate open worksheets in all open workbooks including cells that have not been changed
- CTRL+ALT+SHIFT+F9 – recalculates all sheets in all open workbooks
Example: basic concept
In this example, we have a table of products with price and quantity. Then we have a total column that is a simple calculation of price multiplied by quantity.
Initially, our calculations are correct. This is because we haven’t made any changes since the last changes to the data involved.
We make changes to the quantities so we need to recalculate the totals. The values in column D will not change until we refresh the formulas.
To refresh the calculations after changing the quantities for our line items, we can press F9.
However, note that doing so will refresh calculations across the entire workbook. Keep this in mind if you are working with a file that has formulas on other worksheets.
Alternatively, we can press Alt + F9 to refresh the calculations on this worksheet only.
Conclusion
This shows how easy it is to refresh calculations manually.
Again, this becomes quite useful when working with large files with many formulas.
You can improve the performance of your Excel files by using manual calculation. This can improve efficiency and reliability in the end.