Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Excel provides you with certain features for creating data models. You may also need to add a table to the data model. Hold on if you are on this way! This tutorial will provide you with a complete guideline to add table to data model in Excel.


Download Practice Workbook

You can download the practice book from the link below.


3 Effective Ways to Add Table to Data Model in Excel

Let’s say, we have a dataset consisting of 2 tables with some information about some sellers of a company. One is about the name and ID of the Sellers and another describes the ID, Products, and amount of Sales they have made.

Dataset for Excel Add Table to Data Model

In this section, you will find 3 effective ways to add table to data model in Excel. I will demonstrate them one by one here. Let’s check them now!


1. Applying Relationship Feature

Excel has a unique feature for creating relationships and adding tables between two datasets => Relationship. Applying this feature, you can add table to the data model.

Steps:

  • First of all, click a random cell of your first dataset.
  • Then, go to the Insert tab> click Table under the Tables group.

Insert Table to Dataset in Excel

  • Then, 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 marked and then click OK.

  • Hence, a table will be inserted into your data range.
  • Now, select a random cell in the table and a new tab named Table Design in the toolbar.
  • Go to the Table Design tab> assign a name to your table (i.e. Seller)

  • Next, repeat the steps for the other table and assign a unique name to that table also (i.e. Sales).

Add Table to Data in Excel

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

  • As a result, the Manage Relationships dialog box will appear.
  • Here, click New.

  • After that, Create relationship prompt will show up. Here, you have to 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)
  • Then, click OK.

  • After that, Close the Manage Relationships box to confirm the relationship.

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

Excel Add Table to Data Model

  • Now, a new pop-up PivotTable from an external source 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

  • After that, 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

  • As a result, table will be added to your data model.

Excel Add Table to Data Model

Read More: [Fixed!] Excel Data Model Relationships Not Working (6 Solutions)


2. Applying the Power Query Tool

Power Query is a powerful tool just like its name. With the help of it, you can add table to your data model. Check the ways out.

Steps:

Excel Add Table to Data

  • Next, 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…

  • Now, a pop-up named Import Data will appear.
  • Here, select Only Create Connection and then keep Add this data to the Data Model and click OK.

Excel Add Table to Data Model

  • After that, go to the Data tab> select Data Model from the Manage Data Model group.

Excel Add Table to Data Model

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

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

  • Next, 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


Similar Readings


3. Utilizing the Power Pivot Feature

Another procedure available for adding table to the data model is to utilize the Power Pivot feature. In order to apply this tool to perform your task, just proceed with the steps below.

Steps:

  • First, follow these steps of Method 1 to create table from your data.
  • After creating table from your data, go to the Power Pivot tab and then select Add to Data Model under the Tables group.

Excel Add Table to Data Model

Read More: How to Create Calculated Field in Pivot Table Data Model


Practice Section

Here, I am providing you with a practice sheet so that you can practice yourself to add table to data model.

Practice in Excel to Add Table to Data Model


Conclusion

So, these are the ways you can follow to add table to data model in Excel. Hope the article has helped you to some extent. If you have any questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo