How to Use Data Model in Excel (3 Examples)

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.

How to Use Excel Data Model

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.

Converting Datasets to Table Objects for Showing How to Use Excel Data Model in Excel

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.

 Using Power Pivot to Add Table to Excel Data Model for Creating Relationship

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

Creating Pivot Table for Showing How to Use Excel 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.

How to Use Excel Data Model

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.


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

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

Get FREE Advanced Excel Exercises with Solutions!
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF