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

 

 

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

Share on Google+

Related Posts

Importing Excel Workbooks in Power Pivot  Power Pivot is a very powerful analytical tool which allows you to import data from various external sources!This opens up many possibilities and gives you the power to do further data analysis and get insightful business metrics.You can import data from the fol...
Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation.This is a new calculation in Excel 2010 and onwards.This option will immediately calculate the percentages f...
Sort an Item Row (Left to Right) With Excel Pivot ... I knew that I could sort virtually anywhere with Excel Pivot Tables, but I was surprised that I could even sort any row from left to right!!Below I have an Excel Pivot Table that consists of Sales Numbers over a three year period.Make sure to download the Excel Workbook b...
Show The Percent of Difference From Previous Years...  I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer.One is using Formulas, but that will take time to set up and you are exposed to errors!The other method is t...