This tutorial will demonstrate how to make an Excel reference data model in a formula. 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 can do this by combining tables with data from multiple sheets or resources. So, it is very important to learn how to make an Excel reference data model in a formula.
What Is a Reference of Data Model in Excel?
Data model referencing means taking value from the pivot table to another cell. Sometimes you need to take some specific data from the pivot table. In that case, referencing is essential. The Pivot table helps you narrow down a large dataset and establish a relationship between data points. It recognizes the dataset metrics and dimensions and provides a meaningful summary of it. After creating the pivot table, you may have needed to refer to another cell or a new worksheet. In that case, you can use the GETPIVOTDATA function. You can use direct cell reference, but in that case, if you alter the Pivot Table format, the cell will show an error. Whereas the GETPIVOTDATA function won’t give any error.
How to Use Reference of Data Model in Excel Formula: Step-by-Step Procedures
We’ll use a sample dataset overview as an example in Excel to make it easy to understand. If you follow the steps correctly, you should learn how to make an Excel reference data model in a formula on your own. The steps are:
Step 1: Arranging Dataset
In this case, our first step is to create a Pivot Table using the existing dataset. For this instance, we have Month in Column B, Year in Column C, Income in Column D, and Expenses in Column E. After making the required pivot table, you will refer to the pivot table for other purposes. Follow the process
Step 2: Creating Pivot Table
Now, we want to create a pivot table with the help of our existing database. The steps are
- First, select the dataset. Then, go to Insert > PivotTable options.
- Second, in the ‘PivotTable from table or range’ dialog box, choose the desired range in the Select a table or range option. Then, press the New Worksheet option and click on OK.
- Third, on the right side of your Excel window, you will find the PivotTable Fields and press the desired option you want to show in your table. Now, select the Columns, Rows, and Values accordingly.
- Fourth, you will get the desired result.
Step 3: Inserting the Formula to Show Final Outcome
Now, we want to insert a formula to refer to the pivot table with the help of our existing database. The steps are
- To begin with, insert the following formula in the F5 cell.
=GETPIVOTDATA("Sum of Expenses",$B$4,"Month","Jan")/GETPIVOTDATA("Sum of Income",$B$4,"Month","Jan")
- Then, if you press the Enter button, you will get the result for this cell.
- In addition, you must manually input formulas in every cell and change them according to its need.
- Finally, if you can repeat the whole process correctly, you will get the result like the image below.
How to Create Excel Data Model Relationships
The Data Model is a special feature of Excel that allows users to gather data from various sources and create a relationship between them. Specifically, the model consists of one or more tables of data. Users can load to the memory of Excel using the data model. Combining this data model will create relationships between them. Now, we will show you how to create data model relationships in Excel.
Steps:
- First, arrange the data tables like the image below.
- Second, go to the tables > Data > Data Tools > Relationships options.
- Third, in the Create Relationship dialog box, select the Table, Related Table, Column, and Related Column accordingly and then press OK.
- Fourth, you will get the result in the Manage Relationships like the image below.
- Fifth, go to Insert > PivotTable > From External Data Source options.
- Sixth, in the PivotTable from an External source dialog box, select the Choose Connection Then, press the New Worksheet option and click on the OK button.
- Last, you will get the desired result.
Read More: How to Add Table to Data Model in Excel
How to Manage Data Models in Excel
This time, we 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.
Steps:
- To begin with, we need to prepare a compatible dataset. We have Product in Column B and Price in Column C.
- In addition, manage another table where we have SalesRep in Column B and Region in Column C.
- Furthermore, go to the Table > Insert > PivotTable > From Data Model options.
- Afterward, on the right side of your Excel window, you will find the PivotTable Fields and press the desired option you want to show in your table. Now, select the Columns, Rows, and Values accordingly.
- Finally, you will get the desired result.
Read More: How to Update Data Model in Excel
Excel Data Model vs Power Query
In this case, our goal is to compare Excel Data Model with Power Query. A data modeling 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.
Sometimes, you need to combine two tables in Excel. You can do it manually but that takes a lot of valuable time. Instead of doing this manually, you need to utilize a power query for that purpose. Power query initially set up a connection between two tables in Excel. After that, It merges the tables effectively. Moreover, we can also split columns using a power query. Splitting columns by using the power query is an easy task. In addition, the Power Query in Excel transforms rows into columns very handy.
Read More: Excel Data Model vs. Power Query: Main Dissimilarities to Know
Get Data from Data Model in Excel
Here we will demonstrate how to create a data model. Let’s walk through the following steps to create a data model using the relationship toolbar.
Steps:
- To begin with, prepare a dataset like an image below.
- In addition, go to Insert > PivotTable > From External Data Source options.
- Furthermore, in the PivotTable from an External source dialog box, select the Choose Connection Then, press the New Worksheet option and click on the OK button.
- Afterward, on the right side of your Excel window, you will find the PivotTable Fields and press the desired option you want to show in your table. Now, select the Columns, Rows, and Values accordingly.
- Finally, you will get the desired result.
Read More: How to Get Data from Data Model in Excel
Things to Remember
- In the case of preparing an Excel reference data model, you have to create the PivotTable first and the insert formula to refer to it.
- Moreover, you have to put formulas in every cell manually.
- Furthermore, you can use data model relationships when you are linking one table to many relationships.
- We want to also mark that we can use PivotTable from 3 sources one is External Sources, another is from Table/Range and the last is from Data Model. You can choose any of the sources accordingly.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to learn how to make an Excel reference data model in a formula in Excel. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.
Related Articles
<< Go Back to Data Model in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
