Add a New Table to Excel’s Internal Data Model
You can add a new table to Excel’s Internal Data Model in one of two ways. The easiest way is to create a pivot table from the new table and then choose the Add This Data to the Internal Data Model option. Excel adds the table to the Internal Data Model and produces a pivot table. After the table has been added, you can open the Manage Relationships dialog box and create the needed relationship.
The second, and more flexible, method is to define the table manually and add it to the Internal Data Model. Here’s how:
- Place the cursor inside the data table and select Insert Table. The Create Table dialog box, shown here, opens.
- Specify the range for your data and click the OK button. Excel turns that range into a defined table that the Internal Data Model can recognize.
- On the Table Tools Design tab, change the Table Name field (in the Properties group), as shown. Pick a name that’s appropriate and easy to remember.
- From the Data tab on the Ribbon, select Connections. The Workbook Connections dialog box opens.
- Click the drop-down list next to Add and choose the Add to the Data Model option. The Existing Connections dialog box opens.
- On the Tables tab, find and select the newly created table, as shown. Click the Open button to add it to the Internal Data Model. At this point, all pivot tables built on the Internal Data Model are updated to reflect the new table. Be sure to open the Manage Relationships dialog box and create the needed relationship.