Use An External Data Source To Import Data Into An Excel Pivot Table

When creating an Excel Pivot Table, what happens if your data source is in another location?

Would you have to copy your data into the same spreadsheet?

Well, NO!  You can simply use the External Data Sources feature in your Pivot Table and Excel will magically import the data for you!

You can import data into your Pivot Table from the following data sources:

• Excel workbook
• Microsoft Access database
• SQL Server
• Analysis Services
• Windows Azure Marketplace
• OData Data Feed

For our example, we will import data using two data sources, an Excel workbook and an Access file.

Want to know how to Import Data into Microsoft Excel?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch it on YouTube and give it a thumbs-up!

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch it on YouTube and give it a thumbs-up!

Import from another Excel Workbook:

Import From Microsoft Access and into Excel:

Wondering how this is even possible? Read on!

 

STEP 1:  Go to Insert > Tables > PivotTable

 

STEP 2: Select Use an external data source and click Choose Connection.

 

STEP 3: Select Browse for More.

 

STEP 4: Select the Excel file with your data. Click Open.

 

STEP 5: Select the first option and click OK.

 

STEP 6: Click OK.

 

STEP 7: In the VALUES area put in the Sales field, for the COLUMNS area put in the Financial Year field, and for the ROWS area put in the Sales Month field

Your Pivot Table is ready from the Excel data source!

 

Import From Microsoft Access and into Excel:

 

STEP 1: Now let us try for an Access data source!

Go to Data > Get External Data > From Access

 

STEP 2: Select the Access Database Source file in your desktop or company file path. Click Open.

 

STEP 3: Select PivotTable Report and click OK.

 

STEP 4: In the VALUES area put in the Sales field, for the COLUMNS area put in the Financial Year field, and for the ROWS area put in the Sales Month field

Your Pivot Table is ready from the Access data source!

More Ways to Import External Data into an Excel Pivot Table:

You can also use this functionality to get data from other source types: SQL Server, Analysis Services, Windows Azure, and oData Data Feed

 

 

Further Learning:

• Show The Difference From Previous Months With Excel Pivot Tables
• Show The Percent of Row Total With Excel Pivot Tables
• Show The Percent of Difference From Previous Years With Excel Pivot Tables