A linked table is an Excel table that contains a link to a table in a Data Model. The advantage of creating and maintaining the data in an Excel table, rather than importing the data from a file, is that you can continue to edit the values in the Excel worksheet while using the linked table in a Data Model as the basis of a PivotTable or PivotChart report.
Adding a linked table is as easy as selecting a range and clicking Add to Data Model. It's also a good idea to format the range as a table and give it a meaningful name. It's much easier to perform calculations and manage relationships by using named linked tables.
Follow these steps to link data to a table:
Select the range of rows and columns that you want to use in the linked table.
Format the rows and columns as a table:
- Click Home > Format as Table, and then select a table style. You can choose any style, but be sure to always select My table has headers. If the table doesn't have headers, consider creating them now. Otherwise, Excel uses arbitrary names (column1, column2, and so on) that convey no meaningful information about the contents of the column.
- On the Table Design tab, in the Properties group, enter a name for the table.
Place the cursor on any cell in the table.
On the Power Pivot tab, select Add to Data Model to create the linked table. In the Power Pivot window, you see a table with a link icon, indicating the table is linked to a source table in Excel:
If the model already contains tables, then there's only one more step. You should create a relationship between the new table that you just added and the other tables in the model. See Create a relationship between two tables in Excel or Create relationships in Diagram View in Power Pivot for instructions.
If the workbook didn't previously contain one, it now has a Data Model. A Data Model is created automatically when you create a relationship between tables or select Add to Data Model on the Power Pivot tab. To learn more, see Create a Data Model in Excel.
Now that you have a linked table, you can edit any cell in the worksheet, including adding and removing rows and columns. Changes made to a linked table are automatically synchronized with the Data Model. You can also control update behavior by changing the linked table update mode in Power Pivot.
Tip
To work through a tutorial to learn more about linked tables, see Tutorial: Import Data into Excel, and Create a Data Model.
Synchronize changes between table and model
By default, a linked table maintains an active connection between the Excel table and the Data Model. Changes to data, rows, and columns in the Excel table automatically appear in the Data Model. If you add or remove data, or rename columns, the Data Model updates automatically. Table names are an exception. If you rename a table in Excel, you might need to update related references in the Data Model and any dependent reports.
In some cases, you might want to control the update behavior. Switch to manual update mode by using the Power Pivot add-in.
Follow these steps to switch to manual update mode:
- Ensure that the workbook that contains the linked table is open in Excel.
- Open the Power Pivot window.
- In the tabs at the bottom, click the linked table. A linked table is indicated by a small link icon next to the table name.
- In the ribbon at the top, click Linked Table.
- In Update Mode, choose Manual or Automatic. Automatic is the default. If you switch to Manual, updates only occur when you use the Update All or Update Selected commands in the Linked Table ribbon in the Power Pivot window—or the Update All command in the Power Pivot ribbon in Excel.