Add Data by using an Excel Linked Table (Tutorial)
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 Create Your First PowerPivot Workbook (Tutorial).
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, put your pointer in any of the cells of the Stores worksheet and format it as a table (CTRL+T). Be sure to select My table has headers.
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.
Click the Design tab.
In the Properties area, under Table Name: type Stores.
On the PowerPivot tab in Excel, click Create Linked Table. This opens the PowerPivot window and you can see that 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; and you'll see that 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).