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!
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!
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
How to Use an External Data Source with Excel Pivot Tables
