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:
Import From Microsoft Access and into Excel:
Wondering how this is even possible? Read on!
DOWNLOAD FILES
Import from another Excel Workbook:
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!