Pinterest Pixel

Refresh External Data Source in Excel Pivot Table

Bryan
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.
Refresh External Data Source in Excel Pivot Table | MyExcelOnline Refresh External Data Source in Excel Pivot Table | MyExcelOnline

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 workbookRefresh-External-Data.xlsx


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

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.

Refresh External Data Source in Excel Pivot Table

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

 

If you like this Excel tip, please share it
Refresh External Data Source in Excel Pivot Table | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Pivot Table Filter: Top 5 Customers

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...