While working with Microsoft Excel you might need to refresh the dataset so that you won’t lose data. Often we forget to refresh Excel sheets. Excel has some built-in features to refresh data automatically. In this article, I will show you how you can refresh Excel sheet automatically.
How to Refresh Excel Sheet Automatically: 3 Simple Methods
In the following article, I have described 3 simple methods to refresh Excel sheet automatically.
1. Apply Keyboard Shortcut to Refresh Excel Sheet Automatically
One of the most simple ways to refresh your Excel sheet is by using a keyboard shortcut.
Suppose we have a dataset of some random numbers in a workbook. Now we are going to refresh the Excel sheet just by a single press.
As you can see in the following screenshot we have applied the RANDBETWEEN function to take some random numbers.
Steps:
- While in the sheet press F9.
- The data has refreshed automatically. Simple isn’t it?
2. Use Connection Properties Feature to Refresh Excel Sheet at Regular Intervals
Sometimes we might take some data from a worksheet and work with that data in a new worksheet. So, when we change the data in the previous worksheet we want the change to happen in the new worksheet too. Thus we don’t have to edit the dataset several times. We have a solution for this in this method. By connecting those worksheets together if we change data in a worksheet it will refresh the changes in the new worksheet automatically.
Suppose we have a dataset in a workbook. Now we will open a new workbook and connect the properties with the new workbook so that it refreshes automatically with the changes.
Step 1:
- Go to your window and click the “Excel” icon to open a new workbook.
- In the new workbook go to Data > Get Data > From File > From Excel Workbook.
- A new window will appear named “Import Data”.
- From the new window choose your previous workbook to connect.
- Press Import to continue.
Step 2:
- Now in the “Navigator” window choose the workbook and click “Load”.
- As you can see, we have our data from the previous workbook in the new workbook.
- Now, go to “Data” and choose “Connection Properties” from the “Refresh All” option.
- Check mark the “Refresh every” and input a time inside the “minutes” section.
- Thus the time interval will be selected.
- Hit the OK button to continue.
Step 3:
- Let’s get back to our previous dataset, select some data and press delete.
- You will notice the selected data is deleted.
- Now, open the new workbook and click “Refresh All”.
- You will see the dataset has been refreshed automatically. If we don’t click the “Refresh All” the dataset will refresh automatically after 1 minute. We chose 1 minute in the time section of refreshing.
3. Run a VBA Code to Refresh Excel Sheet Automatically
We can also refresh Excel sheet automatically using the VBA code. Follow my steps below-
Steps:
- Press Alt+F11 to open the “Microsoft Visual Basic Applications” window.
- Go to “Insert” and choose “Module”.
- In the module section apply the following code-
Sub Calculate_Range()
Range("C1:C14").Calculate
Application.OnTime DateAdd("s", 5, Now), "Calculate_Range"
End Sub
- Hit the “Run” button.
- This way you will see the excel sheet will refresh after every 5 seconds.
Things to Remember
While refreshing data from another workbook don’t close the “Queries & Connections” window. It may occur problems while refreshing data.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover the simple steps to refresh Excel sheet automatically in Excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. Stay tuned and keep learning.
Related Article
- How to Refresh Excel Sheet Automatically Using VBA
- How to Auto Update with Interval 5 Seconds in Excel
Go Back to << Refresh Excel Sheet | Excel Worksheets | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
interesting, very clear and useful
thank you
Hello Gianluigi,
You are most welcome.
Regards
ExcelDemy