Excel Video 477 Relationships in the Excel Data Model

Creating and managing relationships in the Excel Data Model, as we’ll do in Excel Video 477, adds tremendous power and flexibility to our analysis. In a typical Pivot Table, you can only pivot data from one table. If you wanted to combine data from multiple tables, you have a serious VLOOKUP project ahead of you. In today’s example, there would millions of VLOOKUP formulas. That would slow down your spreadsheet a little! Relationships in the Excel Data Model make that complexity much faster and simpler to manage.

This Excel Video is longer than usual so that I can explain what a relationship is. Watch how the Billed Charges table has a DOS field that is also in the Dates table, a Procedure Code field that is also in the wRVU2014 table, and a Provider field that is also in the ProviderCategory table. As you’ll see in the video, the fields don’t have to have the same name to make the relationship work, but having the same name in both tables can make the relationships easier to understand.

Once we’ve set everything up, watch how much easier it is to create and manage relationships compared to writing and then copying VLOOKUP formulas. Relationships from the Diagram View are drag and drop. It’s fast and easy. Note that I got to the Diagram View in the bottom right corner of the PowerPivot window. There’s also a Diagram View button to the right on the Home tab that does the same thing. I’ll also show you how to manually create relationships from the Design tab.

Now that the tables are related, we have much more information to analyze. We’ll start that analysis in the next Excel Video. I look forward to seeing you then.