Searching... Searching...

Total Subscribers Receiving Our Weekly Excel Lessons

+75,000

CLICK TO RECEIVE OUR FREE EXCEL LESSONS

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

Email this to someoneShare on FacebookTweet about this on TwitterShare on LinkedInShare on Google+

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.

Import from another Excel Workbook:

External Data Source

Import From Microsoft Access and into Excel:

External Data Source

Wondering how this is even possible? Read on!

DOWNLOAD FILES

Import from another Excel Workbook:

 

STEP 1:  Go to Insert > Tables > PivotTable

External Data Source

 

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

External Data Source

 

STEP 3: Select Browse for More.

External Data Source

 

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

External Data Source

 

STEP 5: Select the first option and click OK.

External Data Source

 

STEP 6: Click OK.

External Data Source

 

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!

External 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

External Data Source

 

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

External Data Source

 

STEP 3: Select PivotTable Report and click OK.

External Data Source

 

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!

External 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

 

 

How to Use an External Data Source with Excel Pivot Tables

Helpful Resource:

Excel Pivot Table

 

 

Related Posts:

Pivot Table Styles The default Pivot Table style in Excel is very bland and boring, stemming from a lack of creativity from the nerds over at Microsoft!You can give some life to your Excel Pivot Table by simply chan...
Show & Hide Field List in Excel Pivot Table Have you ever had the scenario where you are working on your Pivot Table and the Field List disappears?Lots of Excel users get annoyed with this but luckily I'm here to help you out 🙂If your ...
Move an Excel Pivot Table If you have your Excel Pivot Table ready, but it's not in the best location, how do you move this onto another section of your Excel worksheet?Well, you can simply select Move PivotTable and you c...
Refresh All for Excel Pivot Tables When the information in your data source gets updated or changed, you need to Refresh your Pivot Table to see those changes in your Pivot Table.How about if you have multiple Pivot Tables from the...
Searching... Searching...

DO YOU WANT TO GET BETTER AT EXCEL?
If so, join over 75,000 professionals who get career boosting, Free Excel lessons delivered on a weekly basis!

Click here to subscribe

Leave a Comment

Your email address will never be published. Required fields are marked with *

Comments