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

If you are looking for special tricks to get data from the data model in Excel, you’ve come to the right place. There are numerous ways to get data from a data model. This article will discuss the details of these methods to get data from the data model. Let’s follow the complete guide to learn all of this.

What Is Data Model?

Data models are essential to data analysis. You can load data (such as tables) into Excel’s memory using the data model. To connect the data, you can tell Excel to use a common column. In the data model, the word “Model” describes the relationship between each table. There are multiple ways to create a data model in Excel. There are three ways to create a data model: using the Relationship Toolbar, applying Power Query, and using Power Pivot.

How to Create a Data Model in Excel

Here we will demonstrate how to create a data model. Let’s walk through the following steps to create a data model using the relationship toolbar.

📌 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, when the Create Table dialog box appears, select the entire dataset as table data.
• Then, click on 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, the 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 Table_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 Executive 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 on OK.

• Consequently, a window will show up showing the relationship.
• Click OK to confirm the relationship.
• This is how you will be able to create a data model.

How to Get Data from Data Model in Excel: 2 Easy Methods

In the following section, we will use two effective and tricky methods to get data from a data model.  This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

1. Using External Data Source

Now, we will demonstrate how to get data from the data model. Here, we will apply the External Data Source process to get data from a data model. Let’s walk through the following steps to do the task.

📌 Steps:

• First of all, go to the Insert tab.
• Then, 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.
• Finally, click on 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 executive’ names from the Executive table and find out their sales numbers from the Table_Sales table.
• You can do that because the two tables are connected via a data model.
• Finally, you will be able to get data from the data model.

Read More: How to Use Reference of Data Model in Excel Formula

2. Using Data Model Command

Now, we will demonstrate another method to get data from the data model. Here, we will apply the data model command process to get data from a data model. Let’s walk through the following steps to do the task.

📌 Steps:

• First of all, go to the Insert tab.
• Click on PivotTable.
• Finally, from the drop-down select From Data Model.

• Then, from the PivotTable from Data Model window select New Worksheet.
• 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 executive’ names from the Executive table and find out their sales numbers from the Table_Sales table.
• You can do that because the two tables are connected via a data model.
• Finally, you will be able to get data from the data model.

Read More: How to Use Data Model in Excel

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Conclusion

That’s the end of today’s session. I firmly believe that from now, you may be able to get data from a data model in Excel. If you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!

Related Articles

• How to Remove Table from Data Model in Excel
• How to Add Table to Data Model in Excel
• How to Update Data Model in Excel
• Excel Data Model vs. Power Query: Main Dissimilarities to Know

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