Have you ever been in a VLOOKUP hell?
Its what happens when you have to write a lot of vlookup formulas before you can start analyzing your data. Every day, millions of analysts and managers enter VLOOKUP hell and suffer. They connect table 1 with table 2 so that all the data needed for making that pivot report is on one place. If you are one of those, then you are going to love Excel 2013’s data model & relationships feature.
In simple words, this feature helps you connect one set of data with another set of data so that you can create combined pivot reports.
Practical Example – VLOOKUP hell vs. Data Model heaven
Lets say you are looking sales data for your company. You have transaction data like below.
And you want to find out how many units you are selling by product category and customer’s gender.
Unfortunately, you only have product ID & customer ID.
With VLOOKUP Hell,
- You first fetch all the customer and product data and place them in separate ranges.
- Then write a vlookup formula to fetch product category, another to fetch customer gender.
- Then fill down the formulas for entire list of transactions.
- Now make a pivot table.
Assuming you have 30,000 transactions, you have to write 60,000 VLOOKUP formulas to create this one report!!!
With Data Model heaven,
- Create relationships between Sales, Products & Customer tables
- Create a pivot table
Creating a relationship in Excel 2013 – Step by Step tutorial
- First set up your data as tables. To create a table, select any cell in range and press CTRL+T. Specify a name for your table from design tab. Read introduction to Excel tables to understand more.
- Now, go to data ribbon & click on relationships button.
- Click New to create a new relationship.
- Select Source table & column name. Map it to target table & column name. It does not matter which order you use here. Excel is smart enough to adjust the relationship.
- Add more relationships as needed.
Using relationships in Pivot reports & analysis
- Select any table and insert a pivot table (Insert > Pivot table, more on Pivot tables).
- Make sure you check the “Add this data to data model” check box.
- In your pivot table field list, check “ALL” instead of “ACTIVE” to see all table names.
- Select fields from various tables to create a combined pivot report or pivot chart
Example: Category & Gender Sales Report
- Add category to row labels
- Add gender to column labels
- Add quantity to values
- and your report is ready!
Things to keep in mind when you using relationships
- Same data types in both columns: Columns that you are connecting in both tables should have same data type (ie both numbers or dates or text etc.)
- One to one or One to many relationships only: Excel 2013 supports only one to many or one to one relationships. That means one of the tables must have no duplicate values on the column you are linking to. (for example products table should not have duplicate product IDs).
- You can add slicers too: You can slice these pivot tables on any field you want (just like normal pivot tables). For example, you can further slice the above report on customer’s profession or product’s SKU size.
Download Example File
Click here to download Excel 2013 data model demo file. It contains 3 different tables and a combined pivot report (with slicer) to show you what is possible.
Do you use relationships?
Ever since discovering PowerPivot, I kind of stopped using VLOOKUP for most of my own analysis. Now that relationships are part of main Excel functionality in 2013, I am using them even more.
What about you? Are you using relationships & data model in Excel 2013? What cool things are you doing with it? Share your tips with us using comments.
Want even more? Try PowerPivot
If you want even more out of your reports, then try PowerPivot. It is a new feature in Excel 2013 (available as add-in in Excel 2010) that can let you do lots of powerful analysis on massive amounts of data. Here is an introduction to PowerPivot.