How to Get Data from a Data Model in Excel – 2 Methods

What Is a Data Model?

Data models are essential to perform data analysis. You can load and connect data, using tables and column references.


How to Create a Data Model in Excel

Steps:

  • Select a data value from a dataset.
  • Go Insert.
  • Select Table.

create a Data Model and get data in Excel

  • In the Create Table dialog box, select the entire dataset.
  • Click OK.

  • Go to Table Design.
  • Name the table.
  • Convert the rest of the dataset into tables.

rename the table

  • Go to Data.
  • In Data Tools, select Relationships.

  • In Manage Relationships, select New.

click on new to create new relationship

  • In Create Relationship, select the table that you want to analyze. Here, Table_Sales.
  • Select the column that is common to both tables in Column (Foreign). Here, ID.
  • In Related Table, select the table in which you will look for values that relate to the previous table. Here, Executive.
  • In Related Column(Primary), select the column in Table_Sales containing unique values and no duplicates. Here, ID.
  • Click OK.

  • A window will display the relationship.
  • Click OK.

creating a data model


Method 1 – Using an External Data Source

Steps:

  • Go to the Insert tab.
  • Click PivotTable.
  • Select From External Data Source.

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

  • In PivotTable from an external source, select Choose Connection.

select choose connection

  • In Existing Connections, go to Tables.
  • Excel lists the two tables that you previously connected in Relationships.
  • Choose Tables in Workbook Data Model.
  • Click Open.

  • In PivotTable from an external source, select New Worksheet.
  • Click OK.

click on OK to create pivot table

  • A pivot table is created using the data model.

You can relate the two tables. For example, select the executives’names in the Executive table and find their sales numbers in the Table_Sales table.

 

Get Data from Data Model in Excel

Note:

In a pivot table created using the Data Model you can’t create groups, calculated fields or calculated items.

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


Method 2 – Using the Data Model Command

Steps:

  • Go to the Insert tab.
  • Click PivotTable.
  • Select From Data Model.

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

  • In PivotTable from Data Model, select New Worksheet.
  • Click OK.

  • A pivot table is created using the data model.

You can relate the two tables. For example, select the executives’ names in the Executive table and find their sales numbers in the Table_Sales table.

Getting Data from Data Model in Excel

Tips:

The pivot table can be converted to formulas. Select any cell in the pivot table and choose PivotTable Tools ➪ Analyze ➪ OLAP Tools ➪ Convert to Formulas. The pivot table will be replaced with formula-based cells generated using the CUBEMEMEMBER and CUBEVALUE functions. Formulas will update when data changes.

Read More: How to Use Data Model in Excel


Download Practice Workbook

Download the practice workbook to exercise.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo