Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use Reference of Data Model in Excel Formula

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.


Download Practice Workbook

You can download the practice workbook from here.


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 to 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 reference it to another cell or a new worksheet. In that case, you can use the GETPIVOTDATA function. But 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.


Step-by-Step Procedures to Use Reference of a Data Model in Excel Formula

We’ll use a sample dataset overview as an example in Excel to understand easily. 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

Dataset to Make Excel Reference Data Model in the Formula


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.

Inserting PivotTable to Make Excel Reference Data Model in the Formula

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

Using PivotTable Dialog box to Make Excel Reference Data Model in the Formula

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

Selecting PivotTable Window to Make Excel Reference Data Model in the Formula

  • Fourth, you will get the desired result.

Final Result to Make Excel Reference Data Model in the Formula

Read More: How to Create a Data Model in Excel (3 Handy Ways)


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")

Inserting Formula to Make Excel Reference Data Model in the Formula

  • Then, if you press the Enter button, you will get the result for this cell.

Showing Result to Make Excel Reference Data Model in the Formula

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

Final Result to Make Excel Reference Data Model in the Formula


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.

Arrange Dataset to Make Excel Reference Data Model in the Formula

  • Second, go to the tables > Data > Data Tools > Relationships options.

Using Data Tools Relationship Option to Make Excel Reference Data Model in the Formula

  • Third, in the Create Relationship dialog box, select the Table, Related Table, Column, and Related Column accordingly and then press OK.

Using Connection Option to Make Excel Reference Data Model in the Formula

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

Final Result to Make Excel Reference Data Model in the Formula

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


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.

Final Result to Make Excel Reference Data Model in the Formula

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


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.

Using PivotTable to Make Excel Reference Data Model in the Formula

  • 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 (2 Easy Methods)


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.

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. Follow the ExcelDemy website for more articles like this. 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

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo