Refresh External Data Source in Excel Pivot Table

The cool thing with Pivot Tables is you can use a data source that is in a different workbook. The external data source could be stored in a shared drive in your company server that your team uses, or it could be in a different location stored in your computer.

Imagine this external data source will be updated frequently and we would not know when it got updated. So how would we ensure that our Pivot Table is refreshed and shows updated data?

I have a couple of methods for you on how to refresh external data source in the Pivot Table:

• Manual Refresh
• Refresh when opening the file
• Refresh at regular interval

Let’s look at each method one by one!

 

Before you proceed, don’t forget to download this workbook and follow along:

---

Let us check out our Pivot Table if it does really have an External Data Source.

Go to PivotTable Analyze > Data > Change Data Source

You can see that it is using an external data source. Click OK.

 

Manual Refresh

To update this the good old fashioned manual way, right-click on the Pivot Table and select Refresh

 

Refresh when opening the file

There is another way to ensure your Pivot Table is updated.

STEP 1: Go to PivotTable Analyze > Data > Change Data Source > Connection Properties

STEP 2: Select the checkbox – Refresh data when opening the file.

This triggers a pivot table update whenever you open the Excel file. This is a good option if you prefer this to happen only once when you open the file.

 

Refresh at regular interval

STEP 1: Go to PivotTable Analyze > Data > Change Data Source > Connection Properties

STEP 2: Select the checkbox – Refresh every X minutes.

This will refresh your pivot table automatically after the number of minutes has passed.

Try out these options and have fun keeping your Pivot Table up-to-date!