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 good to format the range as table and then give it a proper name. It is so 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 does not have headers, consider creating them now. Otherwise, Excel will use arbitrary names (column1, column2, and so on) that convey no meaningful information about the contents of the column.
-
Name the table. In the Excel window, click Table Tools Design. In the Properties group, type a name for the table.
-
-
Place the cursor on any cell in the table.
-
Click Power Pivot > Add to Data Model to create the linked table. In the Power Pivot window, you will 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 is 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 or Create relationships in Diagram View for instructions.
If the workbook did not previously contain one, it now has a data model. A model is created automatically when you create a relationship between two tables or click Add to Data Model in Power Pivot. 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. The Data Model in Power Pivot will immediately synchronize.
Table names are an exception. If you rename a table in Excel, you will need to manually update the table 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 is a active connection that persists between the range or named table that contains the data values, and the Data Model that drives the report. If you add or remove data, or rename columns and tables, the Data Model is will update automatically.
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. Any 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 will 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.
