Advanced Excel data model

Need to learn how to manage Data Model in Excel? If you are looking for such unique kinds of tricks, you’ve come to the right place. This article will discuss every step of managing a Data Model in Excel. Here, we will take you through a complete guideline to learn all of this.

Table of Contents

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Managing Data Model.xlsx

What Is Data Model in Excel?

The Excel Data Model is a particular form of data table wherein two or more tables are related to one another via one or more shared data sets. We create a  single table that can access the data from all the tables in the Data Model. We do this by combining tables with data from multiple sheets or resources.
As an illustration, a data table containing tables of Products, SalesReps, and Sales. We can link these datasets together. Using the Excel Data Model, we can establish relationships among them.

Why Is It Important to Create Data Model in Excel?

Understanding the significance of something aids in understanding why you should use it. The data required to create a report cannot be contained in a single data table. It frequently occurs in numerous situations, which is why Excel Data Model is necessary.
Using the aforementioned scenario, it is unlikely that a report about the Products and the SalesReps will be added to the Sales Record database. To create a relationship between the tables Product, SalesReps, and Sales, the price of the products and regions of sales reps are required to extract the table’s name for use in the Sales report.

5 Steps to Manage Data Model in Excel

In the following section, we will use one effective and tricky method to manage Data Model in Excel. Creating Data Model in Excel is the first step, and then you’ll learn how to manage them. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

Step 01: Prepare Dataset

To create Data Model, first, we need to prepare a compatible dataset. For this, just follow along.

• Firstly, write the Product’s Price List in some merged cells at a larger font size, That will make the heading more attractive. Then, type your required Headline fields for your data.
• Now, after completing the heading part, you have to enter the Product and Price columns.
• In the Product column, we will enter the name of the products.
• Then, in the Price column, we have to insert the corresponding prices of these products.
• Therefore the first data range should look like the one below.

• After that, create another dataset of Info of SalesReps using SalesRep and Region columns along with their realistic entry.

• Lastly, construct the final dataset of the Monthly Sales Record. Here, we’ve used the Date, SalesRep, Product, and Unit columns to keep account of the sales.

These are our datasets. We constructed these 3 data ranges in three different sheets in a single workbook.

Read More: How to Get Data from Data Model in Excel (2 Easy Methods)

Step 02: Insert Table

In our second step, we’ll convert these simple data ranges into the data table and give them different names. Let’s see it in action.

• Firstly, select cell B4. Therefore, you can select any cell on this dataset (i.e any cell in the B4:C10 range).
• Secondly, go to the Insert tab.
• Then, select the Table option on the Tables group.
• Alternatively, press CTRL+T to insert the table.

• Instantly, the Create Table input box opens.
• Afterward, make sure to check the box beside My table has headers.
• Next, click OK.

• Hence, we get the table.
• Now, move to the Table Design tab.
• Then, click on the Table Style Options group drop-down.
• Later, uncheck the box beside the Filter Button.

• At this moment, select the box of Table Name under the Properties group. And, edit the name. In this case, we wrote the table name as Product.

• Now, our first table is fully ready like the one below.

• , insert the other two tables SalesRep and Sales from the datasets.

Read More: How to Add Table to Data Model in Excel (3 Effective Ways)

Step 03: Add to Data Model

At this stage, we’ll add these tables to the Data Model. Let’s see the process in detail.

• Initially, select any cell on the table. Here, we selected cell B4.
• Then, jump to the Power Pivot tab.
• After that, select Add to Data Model on the Tables group.

• After a while, the window will open.
• Hence, we can see the table added in a sheet named after the table.

• , add the other two tables in Power Pivot.

Read More: How to Remove Table from Data Model in Excel (2 Quick Tricks)

Step 04: Manage Data Model

At this moment, we’ll manage the Data Model. So, without further delay, let’s dive in.

• Firstly, select Diagram View on the View group.

• In this instance, click on Product on the Product table and drag the mouse to Product on the Sales table.

• Thus, it creates a one-to-many relationship between these two tables.

• , establish another relationship between tables SalesRep and Sales.

• At this point, select Data View on the View group to return to the previous viewing condition.

• Hereafter, add a new column Sales Amount in the table Sales at the right of the Unit column.
• Frankly, it will contain the sales amount which is equal to the multiplication of unit price with units sold.

• Then, select the first cell under the Sales Amount column.
• After that, write down the following formula in the Formula Bar.

=RELATED('Product'[Price])*Sales[Unit]

• Later, hit the ENTER key.

• In turn, select the cell as shown in the image below.
• Additionally, paste the formula below.

Total Amount:=SUM(Sales[Amount])

• Consequently, press ENTER.

Read More: How to Use Reference of Data Model in Excel Formula

Step 05: Create PivotTable

It’s the last step of our work. Here, we’ll create reports using this Data Model. So, let’s go through the steps.

• First of all, click on the PivotTable group.

• Immediately, it opens the input box.
• Then, check the circle beside Existing Worksheet.
• Next, select the Location as cell G4 in the Sales worksheet.
• Subsequently, click Ok.

• Instantly, the PivotTable area appears on the active worksheet.

• Then, click anywhere on the PivotTable area. Thus, it’ll open the PivotTable Fields task pane.
• After that, drag the Product field to the Rows area and the fx Total Amount field to the Values area just like in the picture below.

• Thus, it creates a report of product-based sales amount.

• Again, go to the PivotTable Fields task pane.
• Later, from table SalesRep, drag the Region field to the Rows area.

• Hence, it establishes a report region-wise product-based sales amount. Also, we can determine the Grand Total from the PivotTable.

In this way, you can add more elements to the report you need. For example, you also can create a report of sales rep-wise sales amount.

Read More: How to Create Calculated Field in Pivot Table Data Model

Conclusion

This article provides easy and brief solutions to managing the Data Model in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.

What is Advanced Modeling in Excel?

What are Excel data models?

What are Advanced Excel for data analysis?

What is the most advanced version of Excel?