The Recalculate button calculates the formula you entered in your dataset. Whenever you use a volatile function that changes with the span of time, then you may need to recalculate the formula. You can do this with the click of a button utilizing VBA macros. If you are thinking this is going to be a tough task to create a button. You are wrong this time. In this article, we are going to show you easy steps to make a Recalculate button in Excel. Let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
5 Steps to Make a Recalculate Button in Excel
There are 5 easy and straightforward steps to make a recalculate button in Excel. But first of all, you have to create a dataset where the formula is created. Typical worksheets contain multiple formulas that may require recalculation. Let’s say we have a worksheet that displays the Countdown to an ongoing exam. Now, we want to find the Time Elapsed each time we click on the Recalculate Button. We have taken the date using the TODAY function and the present time is set by the NOW function. We calculated the Time Elapsed subtracting the Present Time from the Starting time.
Not to mention, we have used the “Microsoft 365” version. You can use any other version at your convenience.
Step 1: Create a VBA Module
- In the very first step, go to the Developer tab >> pick Visual Basic.
- Consequently, select the Insert tab >> Module >> and click Module1.
- Thirdly, write the VBA code in the General box.
Sub RecalculateActivesheet()
ActiveSheet.Calculate
End Sub
Read More: How to Recalculate in Excel (3 Effective Methods)
Step 2: Insert the Recalculate Button
- At this moment, we want to put a button on the sheet.
- In that case, hover over the Developer tab >> move to the Insert command in the Controls section. Pick a Button in the Form Controls command.
- Consequently, the Assign Macro dialog wizard appears. Hit the OK button.
- Sequentially, Button 1 is created. Right-click on the button section and move to the Edit Text command. You can rewrite the name of the button. We have created it as the Recalculate button.
Read More: How to Make a Calculator in Excel (with Easy Steps)
Step 3: Modify the Button
- Now, right-click on the button and go to Format Control from the Context Menu.
- Subsequently, the Format Control window appears. Select Bold in Font Style. Make Size 14 and choose a Color of your preference. Click on OK.
Read More: How to Create Toggle Button on Excel VBA UserForm
Similar Readings
- How to 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)
- Make Games in Excel (with Easy Steps)
- How to Fix Formula in Excel (9 Easy Methods)
Step 4: Assign Macro to Recalculate Button
- At this moment, again right-click on the button and select Assign Macro in the Context Menu.
- Eventually, the Assign Macro window appears. We put the macro name as RecalculateActivesheet and click on OK.
Read More: How to Create a Calculator Using Macros in Excel (with Easy Steps)
Step 5: Test the Recalculate Button
- Finally, we have created the Recalculate button. Click on the button like the image below.
- Here, the Time Elapsed will be recalculated whenever we click on the Recalculate button.
Forcing Recalculation Using Excel VBA
You can also recalculate the worksheet formula forcefully with the VBA code. Whenever you run the code, it will change the value in your worksheet. Moreover, we have used the same dataset for force recalculation. In the first image, you can see that the Time Elapsed is 1:48 hours.
- Now, run the following code with the F5 key in the Module as we mentioned in Step 1.
Sub ForceCalc(Optional Full As Boolean)
With Application
.Calculation = xlCalculationManual
.Calculation = xlCalculationAutomatic
If Full Then.CalculateFull
End With
End Sub
This code was found on Folkstalk.com.
- Consequently, the Macros window appears. Select the name of the sheet and click on Run.
Consequently, you recalculate the Time Elapsed like the image below.
Read More: How to Force Recalculation with VBA in Excel
How to Refresh an Excel Sheet Automatically
However, we can automatically refresh the dataset numbers by using the keyboard. Additionally, we have taken a dataset of random numbers generated by the RANDBETWEEN function. The formula is in B5:B15 cells.
After that, press the F9 key, and you can see the random numbers are refreshed automatically. Simple, right? It’s a very easy method to work with.
Conclusion
That’s all about today’s session. And these are some easy steps to make a Recalculate button in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.
Related Articles
- How to Insert Clipart in Excel (4 Easy Ways)
- Create a Leaderboard in Excel (with Easy Steps)
- How to Insert WordArt in Excel (2 Simple Methods)
- Calculate Root Mean Square Error in Excel
- How to Calculate APR in Excel (3 Simple Methods)
- Calculate Cost of Funds in Excel (with Easy Steps)
- How to Calculate WACC in Excel (with Easy Steps)