Connect to another workbook

You can create a dynamic connection between an existing Excel workbook and another workbook. You can then refresh the connection whenever the data in the second workbook changes.

Use Excel's Get & Transform (Power Query) experience to connect to an Excel workbook.

  1. Click the Data tab, then Get Data > From File > From Workbook. If you don't see the Get Data button, click New Query > From File > From Workbook.

  2. Browse to the workbook in the Import Data window.

  3. In the Navigator window, select the table or worksheet you want to import, then click Load or Edit.

In Excel 2010 and 2013, there are two methods of creating a connection to another workbook. The recommended method is to use Power Query, which is available if you download the Power Query add-in. If you can't download the Power Query add-in, you can use the Data Connection Wizard.

Power Query

  1. In the Power Query ribbon tab, click From File > From Excel.

  2. Browse to the workbook.

  3. In the Navigator window, select the table or worksheet you want to import, then click Load or Edit.


Data Connection Wizard

Step 1: Create a connection with another workbook

  1. On the Data tab, click Connections.

    Connections

  2. In the Workbook Connections dialog box, click Add.

  3. Near the bottom of the Existing Connections dialog box, click Browse for More.

  4. Find your workbook, and click Open.

  5. In the Select Table dialog box, select a table (worksheet), and click OK.

    Notes: 

    • Worksheets are referred to as "tables" in the Select Table dialog box.

    • You can only add one table at a time.

    • You can rename a table by clicking on the Properties button. You can also add a description.

  6. To add more tables, repeat steps 2 through 5.

  7. Click Close.

Step 2: Add the tables to your worksheet

  1. Click Existing Connections, choose the table, and click Open.

  2. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a Table, PivotTable, or PivotChart.

    Note: In Excel 2013, 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.

Data Connection Wizard

Step 1: Create a connection with another workbook

  1. On the Data tab, click Connections.

    Connections

  2. In the Workbook Connections dialog box, click Add.

  3. Near the bottom of the Existing Connections dialog box, click Browse for More.

  4. Find your workbook, and click Open.

  5. In the Select Table dialog box, select a table (worksheet), and click OK.

    Notes: 

    • Worksheets are referred to as "tables" in the Select Table dialog box

    • You can only add one table at a time.

    • You can rename a table by clicking on the Properties button. You can also add a description.

  6. To add more tables, repeat steps 2 through 5.

  7. Click Close.

Step 2: Add the tables to your worksheet

  1. Click Existing Connections, choose the table, and click Open.

  2. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a Table, PivotTable, or PivotChart.

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.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×