How to Use Data Model in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF