Add Data by Using Excel Linked Tables

Article
04/16/2014

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 Excel but is linked to a table in the PowerPivot window. The advantage of creating and maintaining the data in Excel, rather than importing it, is that you can continue to edit the values in the Excel worksheet, while using the data for analysis in PowerPivot.

Creating a Linked Table

The new table that appears 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. By default, Excel automatically generates names for tables (Table1, Table2, and so on) but you can easily rename tables by using the Excel interface.

Note

In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

To create a table in Excel

In the Excel window, select the data that you want to format as a table.
On the Home tab, click Format as Table, and then select a table style. Be sure to select My table has headers.
Name the table, as described in the next procedure.

To rename a table in Excel

In the Excel window, under Table Tools, on the Design tab, in the Properties group, type a name for the table.

To create the linked table in Excel

In the Excel window, select the table that you created.
On the PowerPivot tab, in the Excel Data group, click Create Linked Table.

A new table is inserted into the PowerPivot window, and the table is displayed. The data in that table is linked to the selected table in Excel and can be edited in Excel but not in the PowerPivot window.

Making Changes to Linked Tables

If you rename the table in Excel after the linked table has been created, the link between the tables will be broken. When you attempt to update data, the Errors in Linked Tables Dialog Box is displayed. Click Options to select an option to resolve the error. Changing the name in the PowerPivot window does not affect the link.

If you rename the column headers of the table in Excel, the changes will be updated in the linked table. However, if you rename a column header in Excel that is used in a PowerPivot relationship, you must create the PowerPivot relationship again. For more information about relationships, see Relationships Between Tables.

Updating Data in Linked Tables

By default, tables that are linked to Excel tables are updated automatically when data changes. They can also be updated manually from the PowerPivot window or from the Excel window.

To set the update mode in PowerPivot

In the PowerPivot window, under Table Tools, click the Linked Table tab, and in the Linked Tables group, click Update Mode, and then click Manual or Automatic.

To manually update linked tables in PowerPivot

In the PowerPivot window, under Table Tools, click the Linked Table tab, and in the Linked Tables group, click Update All or Update Selected.

To manually update linked tables in Excel

In the Excel window, click the PowerPivot tab, and in the Excel Data group, click Update All.