How to Use the Data Model in Excel

Page Image - How to use the data Model in Excel

Excel can analyze data from many sources. But are you using the Data Model to make your life easier? In this post you learn how to create a pivot table using two tables by using the Data Model feature in Excel.

What is a Data Model

Excel’s Data Model allows you to load data (e.g. tables) into Excel’s memory. It is saved in memory where you don’t directly see it. You can then instruct Excel to relate data to each other using a common column. The ‘Model’ part of Data Model refers to how all the tables relate to each other.

Old school Excel Pro’s, use formulas to create a huge table containing all data to analyze. They need this big table so that Pivot Tables can source a single table. Yet by creating relationships, you surpass the need for using VLOOKUP, SUMIF, INDEX-MATCH formulas. In other words, you don’t need to get all columns within a single table. Through relationships, the Data Model can access all the information it needs. Even when it resides in multiple places or tables. After creating the Data Model, Excel has the data available in its memory. And by having it in its memory, you can access data in new ways. For example, you can start using multiple tables within the same pivot table.

A Simple Task

Imagine your boss wants to have insight in the sales but also wants to know the Sex of the sales person. You got below dataset containing one table with the sales per person and another table containing the salespeople and their respective sex. A way to analyze your data is to use a LOOKUP formula and make a big table containing all information. As a next step you can then use a Pivot Table to summarize the data per sex.

Data Model - 1. Data

Advantages of the Data Model

And before method is great when you work with very little data. Yet there are advantages of using the Data Model feature in Excel. Here follow some advantages:

  1. Checking and updating formulas may get arbitrary when working with many tables. After all, you need to make sure all the formulas are filled down to the right cell. And after adding new columns, your LOOKUP formulas also need to be expanded. The Data Model requires only little work at setup to relate a table. It uses a common column at the setup. Yet columns that you add later, automatically add to the Data Model.
  2. Working with big amounts of data often results in a very slow worksheet due to calculations. The Data Model however handles big amounts of data gracefully without slowing down your computer system.
  3. Excel 2016 has a limit of 1.048.576 rows. However, the amount of rows you can add to the memory of the Data Model is almost unlimited. A 64-bit environment imposes no hard limits on file size. The workbook size is limited only by available memory and system resources.
  4. If your data resides only in your Data Model, you have considerable file size savings.

Add Data to Data Model

You will now learn how to add tables to the Data Model. To start with, make sure your data is within a table. Using Power Query you can easily load tables into the Data Model.

  • Click the Data tab -> Click a cell within the table you want to import
  • Select From Table / Range
Data Model - 2. From Table Range

In the home tab of the Power Query editor

  • Select Close & Load -> then Close & Load to…
Data Model - 3. Close and Load To
  • Select Only Create Connection
  • Make sure to tick the box Add this data to the Data Model
Data Model - 4. Import to Data Model

This adds the data to the Data Model. Please make sure to do these steps for both tables.

Creating Relationships Between Data

After adding your data to the Data Model, you can relate common columns to each other. To create relationships between tables:

  • Go to the tab Data -> Select Manage Data Model
Data Model - 5. Manage Data Model

The Power Pivot screen will appear.

  • Click Diagram view. It gives you an overview of all the tables in the Data Model.
  • Then relate the common column ‘Seller’ in the first table, with the column ‘Seller’ in the second table. You can do this by clicking-and-dragging one column, onto the other. A relationship should appear.
Data Model - 6. Create relationship
Note: When you make a relationship between 2 columns, it is common practice to have unique values in one of the columns. This is called a one-to-many relationship. Having duplicates on both sides may give you an error. For advanced calculations many-to-many relationships can exist (for example in Power BI). This however is too advanced to handle in this article. If you are interested in these topics make sure to research ‘Many-to-Many Relationships’.

Using the Data Model

Now we come to the exciting part. To use the Data Model in a PivotTable perform the following steps:

  • Go to the tab Insert -> Click Pivot Table

The ‘Create PivotTable’ pop-up screen will appear. As you have a Data Model in place, you can now select to use it as data source.

  • Click Use this workbook’s Data Model
Data Model - 7. Insert Pivot Table

In the PivotTable Fields you will now see all the possible Data Sources for your PivotTable. The yellow database icon on the lower right corner of the marked tables, shows that it is part of Excel’s Data Model.

Data Model - 8.Tables in Pivot Table

As the two tables have a relationship between each other, you can use fields from both tables within the same pivot! Read previous sentence again. Isn’t that amazing?? Below example uses the Sales and Seller field from the ProductSales table, while the Sex field comes from the other table. And the numbers are still correct!

Data Model - 9. Pivot Table Result

Using the Data Model you can analyze data from several tables at once. All without using any LOOKUP, SUMIF or INDEX MATCH formula to flatten the source table. Yet the data analyzed could also come from a database, text file or cloud location. The possibilities are endless.

To minimize the usage of LOOKUP formulas even more, an amazing tool to look at is Power Query. There’s several articles to find about it on my website. For example, you can read how to use Power Query for Creating Unique Combinations or for Transforming Stacked Columns.

Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills. Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

10 Responses

    • That’s a great tip Omisile. Where normal Pivots don’t have the option to do a unique count, a pivot based on a workbook’s Data Model does have that functionality. Thanks for adding that, appreciate the comment!

  1. doesn’t work for me! In the data model everything is being duplicated between the two tables! So I have 99 unitue rows in table 1, and 99 unique rows in table 2, so there is a 1-1 mapping. Table one. I moved one column from table 1 to table 2 and tried to combine the two using the data model and all it does it repeat the 99 fields from table 2 under the key in table 1 – vlookups are much easier. Also I’ve been getting all sort of memory issues so it’s not as good as vlookups for large datasets.

    • Hi P.

      From your description I have a difficult time following why your data duplicates. In my experience, the Data Model is especially good for large datasets.

      Could you provide a sample file, and elaborate where the issues arise. I’d love to have a look !

      Have a nice evening,
      Rick de Groot

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment