Blog

If you don’t want to suffer from premature ageing, the most important thing is to move with the times and not get set in your ways.  This might be a traumatic thought for Excel users who have been so attached to the Vlookup Function for the past 20 years that giving it up might be like losing a beloved relative.

Why is the Vlookup Function “so last year”?  Well, one of the best new features in Excel 2013 is the ability to build a “Data Model” by linking tables of related data together.  You then simply use an Excel Pivot Table to “mix and match” data across related tables (where you previously would have used Vlookups to pull data into one table).

Let’s take an alcoholic example.  We’ve just opened our new bar in down town Billingshurst, selling a variety of wines, spirits and beers. We want to find out if we’re selling a balanced range of alcohol (don’t want too many wines if people in Billingshurst turn out to prefer beer) so we need to know how many drinks there are in each our categories.

Our data sits in two Excel tables of Drinks and Categories:-

Image

So to associate a Drink with its Category, you might think you need a Vlookup and do this:-

Image

But what then? We’re going to need a Pivot table to find out the number of Drinks in each Category. But in that case, with Excel 2013 YOU DON’T NEED A VOOKUP!

Just enable Power Pivot and then add your tables the Data Model by using the “Add to Data Model” icon on the Power Pivot tab.  Then on the Power Pivot tab, click on the “Manage” icon and in the Power Pivot window, move to Diagram View by using the “Diagram View” icon.  In Diagram View, you can create a relationship between the two tables by dragging and dropping CategoryNo from the Drinks table to Category No in the Categories table like so:-

Image

Image

Now in the Power Pivot window, click on the “PivotTable” icon.  Wow! Notice that in your field list you’ve got both tables and you can now “mix and match” data across both tables.  So how many Drinks in each Category? Move Category from the Categories Table into Row Labels and ProductName from the Drinks table into values.  Hey, you could also find the average prices for each category.

Image

But hang on a minute.  Don’t we have any customers in our Bar?  Next blog – let’s find out which customers are worth looking after (without using a Vlookup)!