Pinterest Pixel

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

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

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!

Key Takeaways:

  • Connect to External Data – Excel allows you to connect Pivot Tables to external data sources such as databases, online services, or other Excel workbooks, making it easier to work with large datasets outside of the workbook.
  • Choose Data Connection Type – You can select different types of data connections (e.g., OLE DB, SQL, or Web Query) depending on your data source, allowing for flexible integration.
  • Data Refreshing – When you connect your Pivot Table to an external data source, you can refresh the data to ensure it is up-to-date whenever the external source is updated.
  • No Need to Import Data Into Workbook – External data sources allow you to analyze large datasets without physically importing the data into your workbook, which helps to keep file sizes manageable.
  • Data Source Security – When connecting to external data sources, always ensure that your connection is secure and the data source has the appropriate access permissions to protect your data integrity.

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!

download the youtube excel practice fileImportingData.xlsx

*** 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 fileImportingData.xlsx

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 filesExternal-Data-Source-files.zip

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

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

 

 

Frequently Asked Questions

How can I connect an external data source to a Pivot Table in Excel?

You can connect to an external data source by selecting your Pivot Table, going to the “Data” tab, and choosing “Get Data” or “From Other Sources.” Then, choose the appropriate connection type (e.g., SQL Server, OLE DB, etc.), and follow the steps to set up the connection.

What types of external data sources can I use for an Excel Pivot Table?

You can use a variety of external data sources including databases (SQL Server, Access), cloud services (Azure, OData), web services, and other workbooks or CSV files to create a Pivot Table.

How do I refresh the data in my Pivot Table when using an external data source?

To refresh the data, simply right-click on the Pivot Table, select “Refresh,” or go to the “Data” tab and click “Refresh All” to update the data based on the most recent information in the external data source.

Will using an external data source increase my file size?

No, using an external data source will not significantly increase your file size, as Excel doesn’t import the data directly into your workbook. Only metadata and connection information are stored, not the full dataset.

How do I ensure that my connection to an external data source is secure?

When connecting to an external data source, ensure that you are using secure authentication methods (e.g., Windows Authentication, SQL Authentication) and that your connection uses encrypted protocols (e.g., SSL or TLS) to protect your data.

If you like this Excel tip, please share it



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


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Data Model and Relationships In Microsoft Excel Pivot Tables

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

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!

Share to...