Excel can interpret enormous amounts of data, but if you’re not using the Data Model tool to organize it, you might be working too hard. With the help of this tool, you may combine data from many tables by building associations based on a shared column. In this article, we will show you how to use the Excel data model.
How to Use Data Model in Excel: 3 Examples
The approach streamlines PivotTable objects and other reporting features while operating in the background. However, you can avoid utilizing formulas like VLOOKUP, SUMIF, and INDEX-MATCH by building connections. In other words, you are not required to obtain data for every column in a single table. The Data Model has access to all the data it needs through relationships. even when it is spread over several locations or tables. The data is now available in Excel’s memory after the Data Model has been created. Additionally, you can access data in novel ways because it is in its memory. For instance, you can begin utilizing several tables inside the same pivot table. Here, you will learn to use Excel data model with some easy steps.
Let’s suppose we have a sample data set that includes sales price, seller name, product name, and date, and also another that contains seller name and gender.
1. Converting Datasets to Table Objects
Ordinary data sets cannot be related to one another. Only table objects are compatible with the Data Model. In this section, we will demonstrate to you to convert datasets to table objects.
Step 1:
- Therefore, we will convert the above two data sets to table objects.
- Here, we must click anywhere in the data set.
- Then, go to the Insert tab.
- Now, choose the Table command.
Step 2:
- Besides, click OK.
Step 3:
- Finally, you will see the outcome where the data set is converted into a table here.
Read More: How to Create a Data Model in Excel
2. Using Power Pivot to Add Table to Data Model for Creating Relationship
A data modelling tool called Power Pivot enables you to build data models, define relationships, and do computations. With Power Pivot, you can deal with enormous data sets, establish intricate relationships, and produce sophisticated or simple calculations all inside the comfortable confines of Excel. In this step, we use the Power Pivot tab to add a table to the data model. In this third step, you can connect related columns after adding your data to the Data Model to establish connections between tables.
Step 1:
- Here are the two data sets which are converted to a table to establish relationships between tables.
Step 2:
- At the start of this part, we will choose the Power Pivot tab first.
- Secondly, select the Add to Data Model Tables command.
Step 3:
- To view the connections between two data tables, select the Diagram View command.
Read More: Excel Data Model vs. Power Query: Main Dissimilarities to Know
3. Creating Pivot Table
One of Excel’s most useful features is a pivot table. You can draw the significance from a sizable, comprehensive data collection using a Pivot table. In this last step, you will observe the pivot table where numerous data have been included from various datasets.
Step 1:
- Firstly, navigate the Insert tab.
- Secondly, choose the Pivot Table command.
- Finally, select the option named “From Data Model”.
Step 2:
- Now, for creating a Pivot Table, we will choose Rows and Values according to Seller Name and Sales Price
Step 3:
- As a result, you will observe the total results of the sales price for every seller.
Read More: How to Add Table to Data Model in Excel
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
In this article, I’ve covered step by step process of how use Excel data model. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
- How to Get Data from Data Model in Excel
- How to Remove Table from Data Model in Excel
- How to Update Data Model in Excel
- How to Use Reference of Data Model in Excel Formula
<< Go Back to Data Model in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
