Pinterest Pixel

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... read more

Download Excel Workbook
Bryan
Posted on

Overview

When creating an Excel Pivot Tablewhat 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.

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

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

See also  Group By Quarters and Years With Excel Pivot Tables

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

Download the YouTube Excel practice file

*** 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!

Download the YouTube Excel practice file

Import from another Excel Workbook:

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

Import From Microsoft Access and into Excel:

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

Wondering how this is even possible? Read on!

DOWNLOAD FILES

Import from another Excel Workbook:

 

STEP 1:  Go to Insert > Tables > PivotTable

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

 

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

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

 

STEP 3: Select Browse for More.

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

 

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

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

 

STEP 5: Select the first option and click OK.

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

 

STEP 6: Click OK.

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

 

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

See also  Frequency Distribution with Excel Pivot Tables

Your Pivot Table is ready from the Excel data source!

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

 

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

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

 

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

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

 

STEP 3: Select PivotTable Report and click OK.

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

 

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!

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

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

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

 

 

Further Learning:

See also  Show The Percent of Column Total With Excel Pivot Tables

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Use An External Data Source To Import Data Into An Excel Pivot Table | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

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!