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.


Download Practice Workbook


3 Handy Ways to Create a Data Model in Excel

In this article, we will talk about 3 handy methods to create a data model in Excel. Firstly, we will use the Relationships toolbar to create the model. Then, we will opt for Power Query to complete the task. Finally, we will resort to the PowerPivot toolbar to create a data model. We will use the following sample dataset to illustrate the methods.

How to Create a Data Model in Excel


1. Using Relationships Toolbar

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.

Inserting Table to Create a Data Model in Excel

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

Opening Relationships Toolbar to Create a Data Model in Excel

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

Relating the Tables to Create a Data Model in Excel

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

Selecting PivotTable to Create a Data Model in Excel

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

Choosing External Sources to Create a Data Model in Excel

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

Selecting Existing Connections to Create a Data Model in Excel

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

Inserting PivotTable to Create a Data Model in Excel

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

Using Relationships Toolbar to Create a Data Model in Excel

Read More: How to Get Data from Data Model in Excel (2 Easy Methods)


2. Applying 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.

Adding Table to Create a Data Model in Excel

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

Opening Power Query to Create a Data Model in Excel

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

Applying Close & Load To...Command to Create a Data Model in Excel

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

Importing Data to Power Query to Create a Data Model in Excel

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

Managing Data Model to Create a Data Model in Excel

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

Connecting the Two Tables to Create a Data Model in Excel

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

Inserting PivotTable From Data Model to Create a Data Model in Excel

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

Applying Power Query to Create a Data Model in Excel

Read More: How to Use Data Model in Excel (3 Examples)


3. Using Power Pivot

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.

Transforming Range to Table to Create a Data Model in Excel

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

Corroborating Table Range to Create a Data Model in Excel

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

Adding to Data Model via Power Pivot Toolbar to Create a Data Model in Excel

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

Relating the Two Tables to Create a Data Model in Excel

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

Using Power Pivot to Create a Data Model in Excel

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


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

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo