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.

We will also look at using keyboard shortcuts!

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Kasper Langmann, Co-founder of Spreadsheeto

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.

file-options-menu

When the Excel Options dialog box opens, select Formulas from the list of options on the left.

Select Manual from the Calculation options.

manual-calculation

For the second method, go to the Calculation group on the Formulas tab.

Kasper Langmann, Co-founder of Spreadsheeto

Click on the down arrow for Calculation Options and make sure that you select Manual.

calculation-options

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!

cloud-download

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.

calculate-now

The second option is Calculate Sheet – this option will calculate the active worksheet.

calculate-sheet

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.

initial-table

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.

unupdated-table

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.

updated-table

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.