Another Excel workbook might just have the data you want to use or analyze, but you might not own the file or you don't want to risk changing it. You can use the Data Connection Wizard to create a dynamic connection between an "external" workbook and your workbook. You get to the Data Connection Wizard through the Data tab.
Important Connections to external data might be disabled on your computer. To connect to data when you open a workbook, enable data connections by using the Trust Center bar, or save the workbook in a trusted location.
Step 1: Create a connection with the workbook and its worksheets
Note that worksheets are referred to as "tables" in the Select Table dialog box that appears in step 5.
- On the Data tab, click Connections.
- In the Workbook Connections dialog box, click Add.
- Near the bottom of the Existing Connections dialog box, click Browse for More.
- Find your workbook, and click Open.
- In the Select Table dialog box, select a table (worksheet), and click OK.
Note You can select and add only one table at a time.
- Because any table that's added is named after its workbook's name, you can rename it if you want a more meaningful name.
- Select a table, and click Properties.
- Change the name in the Connection name box.
- Click OK.
- To add more tables, repeat steps 2 through 5 and rename them as needed.
- Click Close.
Step 2: Add the tables to your worksheet
- Click Existing Connections, choose the table, and click Open.
- In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.
- Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.
Keep the data in your workbook up to date
Now that you're connected to the external workbook, you'll want its latest data shown in your own workbook. Go to Data > Refresh All to get the latest data. For more information on refreshing, go to Refresh data connected to another workbook.
Connect to other types of data sources
Need to connect to other sources of data, such as OLE DB, Windows Azure Marketplace data feeds, Access, another Excel workbook, a text file, or one of many other types? See the following articles for more information.
Connect OLE DB data to your workbook
Connect a SQL Server database to your workbook
Connect a Windows Azure DataMarket feed to your workbook
Connect an Access database to your workbook
Connect external data to your workbook