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:

Let’s look at each method one by one!

 

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

DOWNLOAD EXCEL WORKBOOK


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

See also  Sort an Excel Pivot Table Manually

Go to PivotTable Analyze > Data > Change Data Source

Refresh External Data Source in Excel Pivot Table

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

Refresh External Data Source in Excel Pivot Table

 

Manual Refresh

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

Refresh External Data Source in Excel Pivot Table

 

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

Refresh External Data Source in Excel Pivot Table

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 External Data Source in Excel Pivot Table

 

Refresh at regular interval

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

Refresh External Data Source in Excel Pivot Table

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

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

See also  Microsoft Excel Online Course Private Access - 20+ Hours Beginner to Advanced Course

Refresh External Data Source in Excel Pivot Table

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

 

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin