WARNING: Excel 2013 Content Ahead

Chandoo, the Excel Master of Excel Masters and is a blessing for many of us in the Excel world. His humor and Excel knowledge keep us Excel geeks on top of our game. Today, I borrow from both his humor and his reminder of an Excel feature I started dabbling with a few months ago: Excel Data Models.

3 years ago Chandoo asked does PowerPivot really wear underwear on top of pants? Many folks won’t find out because PowerPivot is no longer available for standard Excel versions. You need Excel Professional Plus to get it. But today

I will show you how YOU can wear your underwear outside YOUR pants!

TODAY’S MISSION We’re going to take information from multiple reports and easily access the summaries that we need. This is going to save time, treat separate reports as if they’re one, and give us tools to look deeper into the reality of our reality. AND it’s not difficult.

THE SCENARIO: Reviewing Website User Activity

You have data related to website users, their professional areas and amount of time logged in. This will help you and your team develop effective content and user interfaces. You can dig into:

• Are certain professions getting a lot out of the site?
• Are certain professions getting almost nothing from the site?
• How much time are the site developers logged into the site vs other types of users?
• Who spends a lot of time logged in? Can we contact the top few users and find out what’s going right for them?

THE PROBLEM: Data is in 3 Different Reports

click the image for a larger view

click image for a larger view

The magenta lines show the IDs of each user. We’d like to see the relationship between the Profession and Time Spent,
designated by the green lines. But they aren’t in the same dataset.

Seem familiar? This is the dark world of Data Parsing. The information that you need is all here but isn’t useful. Observe:

• The information is in 3 separate datasets.
• User ID, Personal ID and ID are 3 names for the same thing.
• We can’t immediately determine who 12883CC is, their name, profession and how much time he/she spent on the site between 17APR and 4MAY. We just know that 12883CC is a Subscriber.

This is typical of what we deal with when we step up to the challenge of working with data. Other examples of data split across different reports:

SOLUTIONS FOR PARSING THIS DATA & GETTING IT INTO THE NEEDED SHAPE

1. The almighty VLOOKUP can handle this via the creation of a single monster table.
2. The anti-VLOOKUP people would use INDEX\MATCH.
3. Get some coffee and use the brute force method of sort, highlight, drag, copy, paste …

Those will work but neither are worthy of wearing your underwear outside your pants. Get ready …

TIME TO PUT YOUR UNDERWEAR OUTSIDE YOUR PANTS!
(Only available to PC users on Excel 2013)

The video below shows the steps:

1. Convert data ranges into Tables
2. Under the Data tab, choose Relationships
3. Create relationships between the Tables
4. Put your cursor in one of the Tables
5. Under the Insert tab, choose Pivot Table
6. In the Pivot Table’s Field List, change the Active default to All
7. Uncollapse the Table views
8. Build the Pivot Table views that you need to see
9. Start wearing your underwear outside your pants! You deserve it, you Data Superhero!

SUMMARY

Excel developers have loaded Excel 2013/Office 365 with powerful analysis tools, it’s worth spending time poking around and seeing what’s inside. Subscribe to my blog and YouTube channel, and check out other Excel bloggers on my Pinterest board: Badass Excel/Data Bloggers. Let’s help each other retain our Data Superhero status.

For those of you who are still on Excel 2003, c’mon! You can’t create Excel Data Models, you’ve got no SUMIFS function, no slicers, no Pivot Charts, no nothing.

What we see in the Excel Data Models and Relationships is opportunity to cut out a lot of steps for integrating data and using it to create more vivid images of our business, membership activity, donor base, fund-raising efforts … anything that’s got data from multiple origins. Check it out!

Superman & Batman Image Credit
Justice League Photo Credit