Wasim Akram
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Simple Methods to Refresh Excel Sheet Automatically
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 way 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?
Read More: How to Refresh Pivot Table in Excel (4 Effective Ways)
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 and 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 refreshed automatically. If we don’t click the “Refresh All” the dataset will refresh automatically after 1 minute. As we choose 1 minute in the time section of refreshing.
Read More: How to Refresh Chart in Excel (2 Effective Ways)
Similar Readings
- How to Disable Background Refresh in Excel (2 Handy Methods)
- How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)
- [Fixed!] Excel Cells Not Updating Unless Double Click (5 Solutions)
- How to Update a Pivot Table Automatically When Source Data Changes
3. Run a VBA Code to Refresh Excel Sheet Automatically
Using VBA code we can also refresh excel sheet automatically. 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.
Read More: How to Refresh Excel Sheet Automatically Using VBA (4 Methods)
Things to Remember
- While refreshing data from another workbook don’t close the “Queries & Connections” window. It may occur problems while refreshing data.
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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
