Load Data to Power Pivot from External Excel Files

Linked tables in Power Pivot have a distinct advantage over other types of imported data in that they immediately respond to changes in the source data within the workbook. If you change the data in one of the tables in the workbook, the linked table within the Power Pivot data model automatically changes. The real-time interactivity you get with linked tables is especially helpful if you're making frequent changes to your data.

The drawback to linked tables is that the source data must be stored in the same workbook as the Power Pivot data model. This isn't always possible. You'll encounter plenty of scenarios where you need to incorporate Excel data into your analysis, but that data lives in another workbook. In these cases, you can use Power Pivot's Table Import Wizard to connect to external Excel files.

Open the Power Pivot window and click the From Other Sources command button on the Home tab. This opens the Table Import Wizard dialog box, shown here. Select the Excel File option and then click the Next button.

The Table Import Wizard asks for all the information it needs to connect to your target workbook.

On this screen, you need to provide the following information:

• Friendly Connection Name: In the Friendly Connection Name field, you specify your own name for the external source. You typically enter a name that is descriptive and easy to read.
• Excel File Path: Enter the full path of your target Excel workbook. You can use the Browse button to search for and select the workbook you want to pull from.
• Use First Row as Column Headers: In most cases, your Excel data will have column headers. Select the check box next to Use First Row As Column Headers to ensure that your column headers are recognized as headers when imported.

After you enter all the pertinent information, click the Next button to see the next screen, shown here. You see a list of all worksheets in the chosen Excel workbook. In this case, you have only one worksheet. Place a check mark next to the worksheets you want to import. The Friendly Name column allows you to enter a new name that will be used to reference the table in Power Pivot.

When reading from external Excel files, Power Pivot cannot identify individual table objects. As a result, you can select only entire worksheets in the Table Import Wizard. Keeping this in mind, make sure to import worksheets that contain a single range of data.

You can use the Preview & Filter button to filter out unwanted columns and records, if needed. Otherwise, continue with the Table Import Wizard to complete the import process.

As always, be sure to review and create relationships to any other tables you've loaded into the Power Pivot data model.

Loading external Excel data doesn't give you the same interactivity you get with linked tables. As with importing database tables, the data you bring from an external Excel file is simply a snapshot. You need to refresh the data connection to see any new data that may have been added to the external Excel file.