In this article, we will discuss and show how you can use and create a data model in Excel. We will go through some easy techniques to do the task.
With Excel’s data model, you can import and connect data from multiple sources, such as databases, external files, or other Excel worksheets, and create relationships between the tables based on common fields or keys. This enables you to work with large datasets and perform powerful calculations and analyses.
So let’s dive into the following article to get a comprehensive understanding of the Excel data model.
Download Practice Workbook
What Is a Data Model in Excel?
The Excel Data Model is a special kind of data table where two or more tables are connected to each other using shared data. In the data model, the word “Model” describes how each table is related. To link the data, you can tell Excel to use a common column.
We make a single table that can access data from all the tables in the Data Model. We do this by putting together tables with data from different sheets or sources.
How to Create an Excel Data Model Using Relationship Toolbar
In this section, we will create a data model using the relationship toolbar.
- Click OK in the Create Table dialog box.
- Similarly, we created a table for another dataset as well.
- Click on a cell of the first table >> go to the Table Design.
- From Properties, type a name for the table.
- Here, we typed Employee_1.
- We set the name for the second table as Sales_1.
- Click on cell B5 >> go to the Data tab >> select Relationships.
In the Manage Relationships dialog box >> click New.
- Then, in the Create Relationship dialog box, select Worksheet Table: Sales_1 in the Table.
- Select Worksheet Table: Employee_1 in the Related Table.
- Select ID in the Column and Related Column.
- After that, in the Manage Relationships dialog box, click Close.
- Go to the Insert tab >> click on Pivot Table >> select From External Data Source.
- Click on Choose Connection in the PivotTable from an external source.
- In the Existing Connections dialog box >> click Tables.
- Click on Tables in Workbook Data Model.
- Click Open.
- Select New Workbook >> click OK.
- From the PivotTable Fileds, select Employee from the Employee_1 table, and drag it to the Rows.
- Select Sales from the Sales_1 table, and drag it to the Values.
Therefore, you can see the Data Model.
How to Create Data Model Using Excel Power Pivot
Here, we will create a data model using Power Pivot.
- Following the steps described above, we created the following two Tables.
- Click on cell B5 >> go to the Power Pivot tab >> click on Add to Data Model.
- From the View tab >> select Diagram View.
- Then, go to the Home tab >> from the PivotTable group >> select PivotTable.
- In the Create PivotTable dialog box, select New Worksheet >> click OK.
- From the PivotTable Fileds, select Employee from the Employee_1 table, and drag it to the Rows.
- Select Sales from the Sales_1 table, and drag it to the Values.
Therefore, you can see the Data Model.
How to Create Data Model Using Power Query in Excel
In this section, we will describe how you can create a data model using Power Query.
- Following the steps described above, we created the following two Tables.
- Click on cell B5 >> go to the Data tab >> click on From Table/Range.
- In the Power Query Editor window, go to Home.
- Then, select Close & Load >> click on Close & Load To.
- In the Import Data dialog box >> select Only Create Connection.
- Mark Add this data to the Data Model >> click OK.
- Go to the Data tab >> click on Manage Data Model.
- Then, go to the Home tab >> from the View group >> select Diagram View.
- Now, go to the Insert tab >> select Pivot Table >> select From Data Model.
- Then, in the PivotTable from Data Model dialog box >> click on New Worksheet >> click OK.
- From the PivotTable Fields, select Employee from the Employee_1 table, and drag it to the Rows.
- Select Sales from the Sales_1 table, and drag it to the Values.
Therefore, you can see the Data Model.
Data Model in Excel: Knowledge Hub
- Create a Data Model in Excel
- Use Data Model
- Manage Data Model
- Get Data from Data Model
- Update Data Model
- Use Reference of Data Model in Formula
- Add Table to Data Model
- Remove Table from Data Model
- Remove Data Model from Pivot Table
- Data Model vs. Power Query: Main Dissimilarities to Know
- Fixed!] Excel Data Model Relationships Not Working
Things to Remember
- We can examine the information from multiple tables together by using the data model. When we establish connections within the data model, we eliminate the need for using VLOOKUP, SUMIF, INDEX, and MATCH functions.
- Data models are automatically created when we import datasets into Excel from external sources. If we import related tables with primary and foreign key relationships, we can automatically generate table connections.
- When creating relationships, the columns we connect in tables should have the same type of information.
- With pivot tables that are created using the data model, we can add slicers and use them to filter the data in pivot tables based on any field we want.
Conclusion
This article states the uses and ways to create a Data Model in Excel. This article explains the topic in detail, and we believe you thoroughly understand the topic.
Here, we presented how to create a Data model using the Relationship toolbar, using Power Pivot, and Using Power Query. Thank you for reading this article. We hope you find this article beneficial. If you have any queries or suggestions, please let us know in the comment section.
Data Model in Excel: Knowledge Hub
- How to Create a Data Model in Excel
- How to Use Data Model in Excel
- How to Get Data from Data Model in Excel
- How to Update Data Model in Excel
- How to Use Reference of Data Model in Excel Formula
- How to Add Table to Data Model in Excel
- How to Remove Table from Data Model in Excel
- Create Relationships in Excel
<< Go Back to Learn Excel
