اكسل

Practical Example – V(X)LOOKUP 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,

1. You first fetch all the customer and product data and place them in separate ranges.
2. Then write a vlookup formula to fetch product category, another to fetch customer gender.
3. Then fill down the formulas for entire list of transactions.
4. 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,

1. Create relationships between Sales, Products & Customer tables
2. Create a pivot table

Creating a relationship in Excel – Step by Step tutorial

1. 

   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.
2. Now, go to data ribbon & click on relationships button.
3. Click New to create a new relationship.
4. 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.

   

5. Add more relationships as needed.

Using relationships in Pivot reports & analysis

1. Select any table and insert a pivot table (Insert > Pivot table, more on Pivot tables).
2. Make sure you check the “Add this data to data model” check box.

   

3. In your pivot table field list, check “ALL” instead of “ACTIVE” to see all table names.
4. Select fields from various tables to create a combined pivot report or pivot chart

Example: Category & Gender Sales Report

1. Add category to row labels
2. Add gender to column labels
3. Add quantity to values
4. 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.

Benefits of Data Model based Pivot Tables

Once you have a data model in spreadsheet, you will enjoy several benefits (apart from multi-table pivots that is). They are,

• Distinct counts: This simple but often tricky to calculate number is easy to get once you have data model based pivot. Just go to value field settings and change the summary type to “Distinct count”. Here is a tip explaining how to get distinct counts in Excel pivots.
• Measures & DAX: Once you have a Data Model, you can unleash the full Power Pivot features on your workbook. You can create measures (using DAX language) and calculate things that are otherwise impossible with regular Excel. Here is an example of percentage of something calculation with DAX & Data Model, to get started.
• Pivots from data in other files & databases: You can combine data model with the abilities of Power Query to create pivots from data in other places. For example, you can make a pivot from sales data in SAP with customer data in CRM system. Here is an overview of what is Power Query?
• Pivots from more than 1mn rows of data: You can connect to very large datasets and make pivots from them with the help of data model. Here is a demo of how to set up data model for 1+mn rows of data.
• Convert Pivot Tables to formulas: Once you have a data model based pivot table, you can turn it in to a set of formulas. You can access this feature from “Analyze” ribbon. This will replace your pivot with a bunch of CUBE formulas. Here is an overview of CUBE formulas.

Drawbacks of Data Model:

Of course, its not all cup cakes and coffee with Data Model. There are a few drawbacks of data model based pivot tables.

• Compatibility: Data model & relationship feature is available only in Excel 2013 or above. This means, you cannot create or share such pivot reports with people using older versions of Excel.
• Not able to group data: In regular Pivot Tables, you can group numeric, data or text fields. But with data model pivot tables, you can no longer group data. You must create another table with the group mapping and use it as a relationship.

Download Example File

Click here to download Excel 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 (or XLOOKUP) for most of my own analysis. Now that relationships are part of main Excel functionality, I am using them even more.

What about you? Are you using relationships & data model in Excel? 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.