Add Data by Using an Excel Linked Table (Tutorial)
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
A Linked Table is a table that has been created in a worksheet in the Excel window, but is linked to a table in the PowerPivot window. The advantage of creating and maintaining the data in Excel, instead of importing it or pasting it in, is that you can continue to modify the values in the Excel worksheet, while you are using the data for analysis in PowerPivot.
In this task, you will create a link from the Stores data in the worksheet in your Excel window to the table in the PowerPivot window. This means that any change made to the source data will be automatically updated in your linked table in the PowerPivot window.
Keep in mind that in order to use this data in your analysis, you must create relationships between the data in the linked table and the other data in your PowerPivot window. For more information, see Create Relationships Between Tables (Tutorial).
Prerequisites
This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.
Create a Linked Table
In the previous task (Add Data by Using Copy and Paste (Tutorial)) the Geography table is static because after you pasted the data, it is not automatically updated. Linked tables, on the other hand, automatically reflect changes that were made to the source. Linked tables must reference Excel data found in a separate Excel window within the same PowerPivot workbook.
To create a linked table
In the Excel window, point to any of the cells in the Stores worksheet and format it as a table (CTRL+T). Make sure My table has headers is selected. Click OK.
The new table that will appear in the PowerPivot window always has the same name as the table in Excel. Therefore, you should give the Excel table a meaningful name before you create the linked table in PowerPivot. By default, Excel automatically generates names for tables (Table1, Table2, etc) but you can easily rename tables by using the Excel interface.
While you are still in the Excel window, click the Design tab.
In the Properties area, under Table Name: type Stores.
In the Excel window, on the PowerPivot tab, click Create Linked Table. The PowerPivot window opens, and a new table has been created. Notice the link icon on the tab.
In the PowerPivot window, on the Linked Table tab, click Go to Excel Table to return to the source table in the Excel window. Change the value in cell C2 from 35 to 37.
Return to the PowerPivot window. The corresponding row has now been updated to the new value as well.
Next Step
To continue this tutorial, go to the next topic: Save Your PowerPivot Workbook (Tutorial).
See Also
Concepts
Add Data by Using Excel Linked Tables