Refresh External Data Source in Excel Pivot Table | MyExcelOnline

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.

Go to PivotTable Analyze > Data > Change Data Source

Refresh External Data Source in Excel Pivot Table | MyExcelOnline

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

Refresh External Data Source in Excel Pivot Table | MyExcelOnline

 

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 | MyExcelOnline

 

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 | MyExcelOnline

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 | MyExcelOnline

 

Refresh at regular interval

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

Refresh External Data Source in Excel Pivot Table | MyExcelOnline

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

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

Refresh External Data Source

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

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 101 Best Excel Tips & Tricks:

Refresh External Data Source in Excel Pivot Table | MyExcelOnline

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn