How to Add Table to Data Model in Excel (3 Effective Ways)

Method 1 – Applying Relationship Feature


  • Click on any cell of your first dataset.
  • Go to the Insert tab> click Table under the Tables group.

Insert Table to Dataset in Excel

  • A dialog box with the heading Create Table will appear in the window. In the data field of the box, you will see that Excel has recognized all the cells of the range of the data although you have selected only a single cell. Keep the My table has headers checked and click OK.

  • A table will be inserted into your data range.
  • Select any cell in the table. A new tab named Table Design in the toolbar appears.
  • Go to the Table Design tab> assign a name to your table (i.e. Seller).

  • Repeat the steps for the other table and assign a name (i.e. Sales).

Add Table to Data in Excel

  • Click in any table and go to the Data tab> click Relationships under the Data Tools group.

  • The Manage Relationships dialog box will appear.
  • Click New.

  • Create relationship prompt will open. Select four fields in this prompt.

  • Table: The first table you want to analyze (i.e. Sales)
  • Column(Foreign): The common column between the tables (i.e. ID)
  • Related Table: Table that looks up the value in the previous table (i.e. Seller)
  • Related Column (Primary): The common column between the tables (i.e. ID)
  • Click OK.

  • Close the Manage Relationships box to confirm the relationship.

  • Go to the Insert tab> click dropdown of the PivotTable > select From External Data Source.

Excel Add Table to Data Model

  • PivotTable from an external source box will appear. Click Choose Connection…

  • From the Existing Connection prompt, go to the Tables group and click Tables in Workbook Data Model and click Open.

  • Click OK on the PivotTable from an external source.

Excel Add Table to Data Model

  • The PivotTable field will appear on the right side of the Excel window. Drag the fields to relevant areas (i.e. Seller=> Rows, Sales=> Values).

Excel Add Table to Data Model

  • The table will be added to your data model.

Excel Add Table to Data Model

Read More: How to Create a Data Model in Excel

Method 2 – Applying the Power Query Tool


Excel Add Table to Data

  • The Power Query Editor window will appear on the worksheet. From the Home tab of this window> click the dropdown of Close & Load > select Close & Load To…

  • A pop-up named Import Data will appear.
  • Select Only Create Connection and keep Add this data to the Data Model and click OK.

Excel Add Table to Data Model

  • Go to the Data tab> select Data Model from the Manage Data Model group.

Excel Add Table to Data Model

  • The Power Pivot window will appear. From the Home tab> select Diagram View from the View tab.

  • The relationship will be created in diagram view. This is one to many relationship (* denotes many).

  • Go to the Insert tab> click dropdown of the PivotTable> select From Data Model.

Excel Add Table to Data Model

Read More: Excel Data Model vs. Power Query: Main Dissimilarities to Know

Method 3 – Utilizing the Power Pivot Feature


  • Follow these steps of Method 1 to create table from your data.
  • Go to the Power Pivot tab and select Add to Data Model under the Tables group.

Excel Add Table to Data Model

Download Practice Workbook

Related Articles

<< Go Back to Data Model in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF