How to Get Data from a Data Model in Excel -2 Easy 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.

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

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

• In Manage Relationships, select New.

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

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

Method 1 – Using an External Data Source

Steps:

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

• In PivotTable from an external source, 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.

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

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.

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

Read More: How to Use Data Model in Excel

Download Practice Workbook

Download the practice workbook to exercise.

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