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.
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.
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.
- 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.
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
- 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
Get FREE Advanced Excel Exercises with Solutions!
