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.


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.


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.

create a Data Model and get data in Excel

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

rename the table

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

click on new to create new relationship

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

creating a data model


2 Easy Methods to Get Data from Data Model in Excel

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.

using External Data Source to Get Data from Data Model in Excel

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

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.

click on OK to create pivot table

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

Get Data from Data Model in Excel

💡 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 Data Model in Excel (3 Examples)


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.

using data model command to Get Data from Data Model in Excel

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

Getting Data from Data Model in Excel

💡 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 Manage Data Model in Excel (with Easy Steps)


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo