How to Manage Data Model in Excel (with Easy Steps)

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.


Download Practice Workbook

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


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.

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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.

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

How to Manage Data Model in Excel

Read More: How to Use Data Model in Excel (3 Examples)


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.

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

  • Similarly, add the other two tables in Power Pivot.

How to Manage Data Model in Excel


Step 04: Manage Data Model

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

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

How to Manage Data Model in Excel

  • Equivalently, establish another relationship between tables SalesRep and Sales.

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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

How to Manage Data Model in Excel

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

Inserting DAX Function

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

Total Amount:=SUM(Sales[Amount])

  • Consequently, press ENTER.

Inserting DAX Function

Read More: [Fixed!] Excel Data Model Relationships Not Working (6 Solutions)


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.

Inserting PivotTable

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

Inserting PivotTable

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

Inserting PivotTable

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

Inserting PivotTable

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

Inserting PivotTable

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

Inserting PivotTable

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

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


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo