How to Refresh Formulas in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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

2 Easy Methods to Refresh Formulas in Excel

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.

2 Easy Methods to Refresh Formulas in Excel

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.

2 Easy Methods to Refresh Formulas in Excel

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.

Refresh Formulas Using Excel Ribbon

  • 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.

Refresh Excel Formulas Using Keyboard Shortcuts

  • In addition, press F2. This will allow you to see the following formula used for that cell:
=C5*D5

Refresh Excel Formulas Using Keyboard Shortcuts

  • 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.

Refresh Excel Formulas Using Keyboard Shortcuts

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


<< Go Back To Formulas not Working in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nasir Muhammad Munim
Nasir Muhammad Munim

Nasir Muhammad Munim has been an Excel and VBA Content Developer for over a year in Exceldemy and published more than 30 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Electrical and Electronic Engineering from the Islamic University of Technology. Apart from creating Excel tutorials, he is interested in developing PostgreSQL, MySQL, and Android applications. He is fascinated by CAD-based designing systems and building... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo