Lesson 6: Relationships - Excel Data Model


Introduction: Say goodbye to VLOOKUP?

No please don't you take away my VLOOKUP formula. It's almost the only one I master and I really understand.

Rest assured. VLOOKUP will always be around. I still use it myself quite a lot. I however would like to briefly introduce a nice Excel 2013's feature that will reduce the need to use VLOOKUP.

Let me first notify our readers of the fact that they, prior to reading this lesson, must be familiar with Excel tables. It's everything but a complicated feature and is a really handy instrument. This Microsoft Office Support page provides some useful information about it.

That being said, let me explain briefly the content of this lesson: when using VLOOKUP, you basically retrieve information from a certain data table into the table where you need that info. And you do that basing yourself on a common column between those two tables, right?

Correct. Let me give you an example:


So, in the above figure, I've got 2 tables, one with sales data per Account Manager and one with the region each Account Manager deserves. If I want to know how much we sold per region, I first need to retrieve the region info from the "Region Table" into my "Sales Table" using a VLOOKUP, as you can see in cell E5. I would then be able to draw a Pivot Table and I have the info I need in no time.

Brilliant! What you did is perfectly correct and will produce the required result. Let me ask you however another question. What would you do if you had 50.000 rows each of them containing 5 columns where a VLOOKUP is needed?

I never have to process those amounts of data.

You don't but someone else might.

Well that would be a piece of cake, I just would need 250.000 VLOOKUP's.

250.000 VLOOKUP's? Well that would work, but maybe the performance wouldn't be acceptable.

OK Mister I-Know-Everything-Better-Anyway, what do you suggest?

I would suggest creating some relationships in your data.

Relationships - Data Model

Let's go back to your example. You have your two data tables, one with the Sales figures and one with the Regions. The first (and almost only) thing you need to do is tell Excel that the "Name" column in the Sales table is related to the "Name" column in the Region table.

Of course they are related! That's why my VLOOKUP works.

Exactly! You just have to make the relationship explicit in Excel, and this is how. In the Data ribbon tab you'll find under the Data Tools section the Relationships icon:


This opens a create relationship dialog:


Clicking on "New" will allow you to define a new relationship:


The dropdowns on the left allow you to select a table name. The dropdowns on the right list all the column names in the table selected. That is why the data needs to be set up as tables.

You need to select the shared column in both tables. In our example, the column name between both tables is identical, but they could have been different. However, one of the columns must contain unique entries, if not, an error message will be displayed. This what is called a "primary key" in database language. This is pure logic: suppose Ellen would be linked to Region North as well as Region West, how would Excel know how to differentiate sales for Ellen between these two regions?

Once created, the relationship will be listed in the Manage Relationship dialog, and is ready for use:


I still don't have the data I need...

Patience my friend, patience...

With my VLOOKUP's, I would have had the data I needed already.

That's because this is a trivial example. You'll love this tool to process a more complex dataset, I promise. Can I now pursue?

Be my guest.

You can now create a PivotTable report based on the data of these combined two tables.

Just add a PivotTable to your spreadsheet the usual way, but instead of selecting a data range, choose "Use external data source".


In the dialog that now pops-up, click the "Tables" tab and then the Tables in Workbook Data Model option. Now click the Open button followed by OK.


Hold on! You plotted two arrows in the penultimate image and only explained to use the first one. Do I need to check the second one "Add this table to the Data Model" as well?

If you follow the steps we've done upfront, you don't. However, you could have skipped them and just have decided to add a PivotTable. But then you would have had to check that field "Add this table to the Data Model" telling Excel that the table you're now using in your PivotTable is part of a data model whose relationship you still will have to make explicit, the same way as we did before.

Now back on topic. You'll see that when inserting a pivot table that way I can now select data from both tables as shown below:


Which is nice because it allows me to create a combined report without the need for entering a single formula:


As you can see, I was able to summarize data based on the region even if the sales table does not contain any region related data. We just told Excel that the region could be found through the Salesperson's name. The download link can be found here.

Good to know, and to remember...

  • Your data must be setup as tables, it won't work otherwise;

  • The data type of both columns that are linked should be identical. You cannot e.g. link a column formatted as dates with a column formatted as text;

  • Remember that one column must contain unique values;

  • An important drawback of the above is that you cannot group data anymore, like grouping dates by months, quarters or years. PowerPivot however might be part of the answer then;

  • If you try to modify these tables automatically using VBA, please note that the name of PivotFields are not the ones displayed on the spreadsheet.

We appreciate your feedback!


About Cintellis

Let us surprise you with how much more you can achieve by using Excel and VBA based products.

Are you looking for an on the level, reliable and trustworthy company that will surprise you with how much more you can achieve by using Excel and VBA based products? Learn more about our philosophy, products, services or contact us for more info.





About Numis

Flexible, cheap and very short implementation time, discover Numis, our Excel based Cash Management and Management Accounting Software and experience a totally new way to analyse and report on your cash positions.

At Numis, we help you to get more out of your financial data than you thought possible. From enhanced reporting functionalities and detailed analysis to providing enterprise budgeting and forecasting solutions, we will help you to achieve your business objectives. When using Numis, you can share your analysis and strategy with your business partners, suppliers or employees as needed.

Numis is a product from Cintellis bvba & FS&P bvba.

CONTACT Us

You can contact us by phone, mail or by filling out the form below.

Corporate address:
Alsembergsesteenweg 588
1653 Dworp
Belgium

Phone Numbers:
+32 (0)471 65 19 66

E-Mail: info@cintellis.com