How to Create a Data Model in Excel (3 Handy Ways)

A data model is an essential feature in data analysis. Using the data model, you can load data (such as tables) into Excel’s memory. Then, you can tell Excel to use a common column to connect the data. The relationship between each table is described by the word “Model” in the Data Model. Excel offers multiple ways to create a data model. This article will discuss how to create a data model in Excel in 3 different ways.

1. Using the Relationships Tool to Create a Data Model in Excel

In this instance, we will use the Relationships toolbar in Excel to create a data model. The relationships toolbar allows us to make connections between two tables using a common column that contains the same data.

Steps:

• Firstly, select a data value from a dataset.
• Then, go to the Insert tab in the ribbon.
• From the Insert tab, select the Table command.

• Secondly, in the Create Table prompt, select the entire dataset as table data.
• Then, click OK.

• Thirdly, go to the Table Design tab.
• Then, give a name to the newly created table.
• This will allow you to distinguish the table in the upcoming steps.
• Finally, repeat the same process for the rest of the dataset and turn them into tables.

• After that, go to the Data tab in the ribbon.
• Then, from the Data Tools option select the Relationships tool.
• Consequently, a window will show up.

• Then, from the Manage Relationships window, select New.
• As a result, Create Relationship prompt will appear on the screen.

• In the prompt, firstly, select the table that you want to analyze.
• In our case, it is the Sales table.
• Secondly, select the column that is common to both tables as Column(Foreign).
• This column can contain duplicate values.
• In our case, the common column is ID.
• Thirdly, select the look-up table as the Related Table.
• From this table, we will look for the value that relates to the previous table.
• In our case, the table is the Executives table.
• Then, select the common column as the Related Column(Primary).
• This column must contain unique values or values with no duplicates.
• Here, the column is the ID column.
• Finally, click OK.

• Consequently, a window will show up showing the relationship.
• Click OK to confirm the relationship.

• After that, go to the Insert tab.
• Click on PivotTable.
• Finally, from the drop-down select From External Data Source.

• Then, from the PivotTable from an external source window select Choose Connection.

• In the Existing Connections window, first, go to Tables.
• There you will find that Excel has listed the two tables that we have previously connected in the Relationships toolbar.
• Choose Tables in Workbook Data Model.
• Finally, click Open.

• Then, from the PivotTable from an external source window select New Worksheet.
• After that, check the Add this to the Data Model option.
• Finally, click OK.

• Consequently, you will find that a pivot table is created using the data model.
• Here, you can relate the two tables.
• For example, you can select the executives’ names from the Executive table and find out their sales numbers from the Sales table.
• You can do that because the two tables are connected via a data model.

Read More: How to Get Data from Data Model in Excel

2. Creating a Data Model with Excel Power Query

In this example, we will use Excel Power Query to create a data model. Power Query allows us to connect two or more tables to create a data model.

Steps:

• To begin with, pick a data value from a dataset.
• After that, select the Insert tab from the ribbon.
• Select the Table command from the Insert tab.

• Secondly, choose the entire dataset as table data in the Create Table prompt.
• Then, press OK.

• Thirdly, select the Table Design tab from the ribbon.
• After that, give the newly created table a name.
• This will help you identify the table in the subsequent steps.
• Finally, carry out the same procedure on the remaining dataset to create tables.

• After that, choose the Data tab from the ribbon.
• Then, select From Table/Range.
• Consequently, the Power Query window will be opened.

• From the Power Query Editor first, select the Home tab.
• Then, choose the Close & Load option.
• From the drop-down options, select Close & Load To… .
• Consequently, the Import Data window will be on the screen.

• After that, from the Import Data prompt, choose the Only Create Connection option.
• Then, check the Add this to the Data Model box.
• Finally, click OK.
• Repeat this process for the rest of the tables.

• After that, go to the Data tab in the ribbon.
• Then, choose the Data Tools option.
• Finally, select the Manage Data Model tool.
• This will open a new window.

• In the new window, select the Home option.
• Then, choose the View tab.
• Finally, select the Diagram View.

• As a result, the data will be in diagram view.
• In the diagram view, you will see the tables as diagrams.
• Now, connect the common columns of the two tables.
• In our case, the column will be ID.
• We can see that the connection has a 1 on one side and an asterisk on the other.
• This implies that the tables have a One to Many relationship.
• The 1 means that the Executive ID column has no duplicate value.
• On the other hand, the Sales table’s ID column has duplicate values.

• Afterward, go to the Insert tab.
• Select PivotTable.
• Finally, from the drop-down list select From Data Model.

• Then, from the prompt on the screen, first, select New Worksheet.
• Then, click OK.

• As a result, you will discover that the data model is used to create a pivot table.
• You can relate the two tables here.
• As an illustration, you could choose the names of the executives from the Executives table and then look up their sales numbers from the Sales table.
• That is possible because a data model connects the two tables.

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

3. Applying Power Pivot to Make a Data Model in Excel

In this example, we will opt for the Power Pivot toolbar to create a data model. The Power Pivot toolbar allows us to connect two tables and make a data model out of them. We will use the common column to connect the two tables.

Steps:

• To start with, choose a data point from a dataset.
• Then, from the ribbon, choose the Insert tab.
• From the Insert tab, choose the Table command.

• Secondly, in the Create Table prompt, select the entire dataset as the table data.
• Then, click OK.

• In the third step, choose the ribbon’s Table Design tab.
• After that, give the table you just created a name.
• In the steps that follow, will assist you in finding the table.
• Finally, use the same process to create tables from the remaining dataset.

• Then, go to the Power Pivot tab.
• After that, select Add to Data Model.

• Afterward, in the Power Pivot window, first, go to the Home tab.
• Then, from the View option, select Diagram View.

• Then, connect the two common columns from the two table diagrams.
• In this case, the common column is ID.
• The two tables are connected in One to Many relationships.

• After that, go to the Home tab in the ribbon.
• Choose PivotTable.

• From the Create PivotTable prompt, first, select New Worksheet.
• Then, click OK.

• As a result, you will find a pivot table with two tables.
• A data model relates the two tables.
• This allows you to look for values in one table and display them in relation to values in another table.

Read More: How to Add Table to Data Model in Excel

Download Practice Workbook

Conclusion

In this article, we have learned how to create a data model in Excel in an exhaustive manner. This will allow users to analyze and display their data in a more attractive and sophisticated way.

Related Articles

• How to Update Data Model in Excel
• How to Use Reference of Data Model in Excel Formula
• How to Remove Table from Data Model in Excel

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