Add worksheet data to a Data Model using a linked table

A Linked Table is a table in Excel that’s linked 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, PivotChart, or Power View report.

Adding a linked table is as easy as selecting a range and clicking Add to Data Model. Better yet, format the range as table so that you can give it a proper name. Working with a named linked table makes calculations and relationships easier to manage.

  1. Select the range of rows and columns that you want to use in the linked table.

  2. Format the rows and columns as a table.

    1. 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 information about column contents.

    2. Name the table. In the Excel window, click Table Tools Design. In the Properties group, type a name for the table.

  3. Place the cursor on any cell in the table.

  4. 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:

    Linked table icon

  5. If the model already contains tables, you have one more step. You should create a relationship between the new table 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 a model, it does now. 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 be immediately synchronized. Table names are an exception. If you rename a table in Excel, you will need to manually update the table in Power Pivot.

Tip:  You can step through a tutorial to learn more about linked tables. See Tutorial: PivotTable data analysis using a Data Model in Excel for more information.

Synchronize changes between table and model

By default, a linked table is a live connection between the range or named table that contains the data values, and the Data Model that powers the report. If you add or remove data, or rename columns and tables, the Data Model is updated automatically.

In comes cases, you might want to control the update behavior. To switch to manual update mode, use the Power Pivot add-in.

  1. Make sure that the workbook that contains the linked table is open in Excel.

  2. Open the Power Pivot window.

  3. In the tabs at the bottom, click the linked table. Linked tables are indicated by a small link icon that appears next to the table name.

  4. In the ribbon at the top, click Linked Table.

  5. 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.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!