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.
Table of contents
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 the Data Model refers to how all the tables relate to each other.
Old-school Excel pros use formulas to create a huge table containing all the 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 into the sales but also wants to know the Sex of the salesperson. You got the 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 the information. As a next step, you can then use a Pivot Table to summarize the data per sex.
Advantages of the Data Model
The before method is great when you work with very little data. Yet, there are advantages of using the Data Model feature in Excel. Here are some advantages:
- 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 a 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.
- 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.
- 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.
- 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
In the home tab of the Power Query editor
- Select Close & Load -> then Close & Load to…
- Select Only Create Connection
- Make sure to tick the box Add this data to the 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
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.
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 the data source.
- Click Use this workbook’s Data Model
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.
As the two tables have a relationship with each other, you can use fields from both tables within the same pivot! Read the previous sentence again. Isn’t that amazing?? The 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!
Conclusion
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 are 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.
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
I have done some transformations and added a few more columns in my data model.
I now need to export this data into a CSV file from the data model, how do I go about doing that Rick? I have 7.5Mil records.
Hi Anshu,
I’ve never tried this before. But you could try to export the data using DAX Studio. It’s a separate tool that you can download for free. It can connect to Excel’s data model. After doing that you can try exporting it. Do let me know if things worked out for you! Best, Rick
https://daxstudio.org/
Hi Rick! – Thanks for sharing!
I have a question for you I hope you can answer:
I have a CSV file with 2 mill. rows (GL Entries with transactions for every day). I use Power Query to connect to the file, and add to the Datamodel.
If I do nothing, the Datamodel must handle 2 mill. rows.
If I do a grouping with aggregation of the amount per month in Power Query, then there will be only 80 thousand rows.
Will that be an advantage, make it faster afterwards in the datamodel, to use PowerQuery to make such an aggregation?
Hey Dan,
The grouping in Power Query could be a good idea. Excel can handle 2 million rows in the data model. But if you don’t need your data on a daily or weekly level, then grouping the data to a monthly level will benefit the performance of any operations you do on the data model. Less data, means less processing time of any calculations you do.
Good luck!
Rick
Hi, Rick, thanks for replying. I am only trying to create an info list at this point. I figured it out though; when I merge them in Power Query it identifies the unique Color Code key and gives me what I’m looking for. Thanks so much!
I have 5 Lists in Sharepoint based on production parts that I want to pull into a Data Model (first try at this).
I’m just starting with 2 of them “Primary Parts” list and “Color Codes” list. I’ve validated that the Color Code column in “Color Codes” list is unique (2,000 unique codes). There are about 1,800 part numbers in the “Primary Parts” list. The join is on “Color Code” which is in each list. When I attempt to pull in the color description from Color Codes list I get a message that the pivot table report will not fit on the sheet – it’s attempting to assign every color code to each individual part number, like there is no relational connection between the Color Code columns in each list, but there clearly is in my data model. Any suggestions? Seems like a no-brainer at this first step but then…
Hi Jeff!
Thanks for your comment. Have you added a value as well? Like the sum of sales for example? If this doesn’t help, could you share an example file with me?
Regards,
Rick
Hi Rick,
Your article is clear, simple and easy to understand.
I read many articles about data model and power pivot but yours is really simple.
I got a question for you.
I created a dashboard in xlsb format and I inserted many slicers, pivot tables and graphs.
The 2 tables from which the data come from consist of 200k rows but they always grow week by week.
Every week I have to elaborate the data into the tables because the sources of such data are different (SAP, Coupa) and moreover I make some additional changes to that.
Now, i noticed the time for loading data model is growing too much and I am looking for a way to make queries faster.
I saved the file as xlsb. I also deleted the source data (initially I put the tables into the same file as the dashboard in different sheets) : after I update the source data and I refresh the dashboard, I am used to delete the sheets where the source data were, to reduce the file size. At the moment the file size is 20mb (xlsb).
The ral problem is the time for loading the data model.. Still too much.
I Haven’t used power query /pivot yet because this dashboard is shared with many users and if I remember well all of them should activate this feature to make the dashboard working well. This could be really complex to manage.
I already tried not to use functions.
The origin data source with many columns and all the Functions in it is saved as different file. Then what I do is to put in the same file where the dashboard, but in another sheet, only the columns I really need to make it working.
As Said above, after refreshing the pivot tables and graphs, I delete the source data (that is saved in a different excel file).
So, do you have suggestion to make the queries faster?
Thanks
Michael
HI Rick. Thanks for your time writing this.
So, I am basically stuck with the fact that I have a processing workbook, which adds and calculates columns and measures.
Now… I want t use this data model in ANOTHER excel file. The connection must be automatically updated, since both files are on Sharepoint.
My question is whether i can use calculated columns and measure from one “empty” workbook (Data model) in another workbook, so I can create there a new pivot table.
Any chances?
Thanks,
Santiago from Peru
Hi Santiago,
If you make a datamodel in an Excel file, I’m afraid you can’t reference that model from another empty workbook.
Centrally creating data models and measures is often done with SSAS. Excel is not the right tool for it. Would using SQL Server Analysis Services (SSAS) be an option for your company?
Rick
Hi K,
In your Model some data is currently duplicating. In the Pivot table, the FY field comes from the ‘ModuleInfo’ table. If you remove that one, and instead get the FY field from the ‘CourseRunInfo’, your data for Printing Cost will not duplicate.
Also I would recommend using a calendar table in your data model to slice your data by year.
Hope that helps!
Rick
I have a dashboard populated by pivot tables that are populated by the data model. I want to include slicers to filter the data. If I then send the file to someone that does not have access to the original data source, will the slicers work for that person? Or will they get an error because Excel and the data model can no longer find the data source? ( I know they cannot refresh the data model, I’m just worried about viewing what I already populated before sending to them)
Hi Tim,
You’re right. The dataset can’t be refreshed, but users are able to use slicers to the already loaded data model. Even if they don’t have access to the data source itself.
Cheers,
Rick
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
So basically Excel becomes more like Access?
That’s been my thought as I learn more about this tool, especially once I saw the Relationships.
My experience with MS Access is very limited. However, relationships are a strong part of the Data Model. In that sense they have become more similar.
Thanks, great info
One important thing I like about data models is the additional functions you are able to use in Pivot Tables especially “Unique Counts”.
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!