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.
💡 Note:
A pivot table created using the Data Model has some restrictions when compared with a pivot table created from a single data table. The most notable one is: that you can’t create groups. In addition, you can’t create calculated fields or calculated items.
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.
💡 Tips:
The pivot table can be converted to formulas. To do this, select any cell in the pivot table and choose PivotTable Tools ➪ Analyze ➪ OLAP Tools ➪ Convert to Formulas. The pivot table will be replaced by formula-based cells. The formulas were generated using the CUBEMEMEMBER and CUBEVALUE functions. In spite of the fact that the new range of data will not be a pivot table, the formulas will still update when the data changes.
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!
