Why Power Pivot can Change Your Working Life (part 01)

But first, what is Power Pivot?

Technically, it is an add-on tab in Excel.

excel bar.jpg

But actually, if Excel is like our mortal world, Power Pivot is Narnia.

If you’re a Potterhead, Excel is like our muggle world, and Power Pivot is Diagon Alley.

Basically, it’s a magical door disguised as a boring add-on tab INSIDE the Excel house.

excel door.jpg

What’s so special about the Power Pivot door?

Many people struggle with the concept of Power Pivot, so let’s get the basics right first.

When you want to bring in data, you have two options. You can either bring it into Excel itself, or into the doors of Power Pivot.  To see the difference between the two options, let’s walk through the process of analysing your data tables.

The usual way to do Pivot Table:

Step 1. Bring data into Excel

country table.jpg

Country Table: You have a table of countries that your boss has told you to find out more about. These three countries are USA, Singapore and Indonesia.

economic table.jpg

Economic Table: You have the economic data for a list of countries. This can be High/Low level of carbon emissions, employment rate, etc, represented by columns A,B,C,D.

demographic table.jpg

Demographic table: You have the demographic data for a list of countries. This can be the percentage of elderly, population growth and infant mortality, etc represented by columns 1,2,3,4.

Step 2: Combine your chosen data into one table

Before creating a Pivot Table, you must first combine data from these two tables into one. This is typically done using VLOOKUP or SUMIFS.

You thought that data from columns 1, 2, A, B are the most relevant details, so you picked those to extract. You used four VLOOKUPs to create this combined table.

Step 3: Create Pivot Table

pivot table edit with correct category.png

Using this combined table, you create your own Pivot Table. Since you have created four columns of data, you have four attributes to play with: 1, 2, A, and B.

Here, you decided to use 1 and A.

After getting the right data in the Pivot Table, you used it to create a Pivot Chart, formatted it and then submitted it to your boss.

And hopefully, that’s the end of the story.

But as with all good stories, it’s never the end.

 

You received a message from your boss 10 minutes later. It reads “Hey... I want to see the other economic and demographic data about those 3 countries. Thanks.”

 

Dang. You will have to go back to your VLOOKUP table, do another four VLOOKUPs for the remaining attributes that you did not choose. With this new combined table, you re-do a new Pivot Table and re-format your Pivot Chart.  All this while, hoping you didn’t make a mistake with those formulas. Urgh.

Can we suggest something?

Here’s where you should open the Power Pivot door:

Step 1. Bring data into Power Pivot

power pivot door.jpg

When you choose to import your data into Power Pivot, you open up several options to manage your data. One of them is the power to create relationships between your tables.

Step 2:  Create relationships between tables

Instead of using VLOOKUP, our favourite way to marry our tables together is to Drag-And-Drop the common attributes, like this:

Drag “Countries” from the Countries Table to “Countries” in both Demographics and Economics Table. Once this is done, you have created a link between these three tables!

Notice something? The goal of both VLOOKUP and Drag-And-Drop is the same: link your tables together to prepare them for the Pivot Table playground.

However, doing VLOOKUP requires you to manually key in formula to your data tables. As we all know, more manual work means more room for mistakes. What if you keyed in the wrong column number? What if you added a new column in the demographic table and the VLOOKUP formula did not change? What if you didn’t drag the formula all the way down?

Unless you are an Excel guru who can make robust yet flexible formula tables with confidence, or you need some added flexibility of VLOOKUP that relationships cannot provide, we suggest using Power Pivot’s Drag-And-Drop feature to link up your tables. This way, you can sleep better at night knowing all your data tables are correctly linked.

Step 3: Create your Pivot Table

pivot table edit with correct category -1.png

Since your tables are now linked, you can play with ALL the attributes from each table in the Pivot Table!

Let’s pause and think about this for a moment... Imagine that each of your table has 10 attributes, 20 attributes. If you go by the VLOOKUP way, you’ll either take a long time to VLOOKUP everything (and make mistakes while doing it) or select a few key data and hope you wouldn’t have to look at the rest.

In contrast, using Drag-And-Drop to link up tables allows you to have all attributes within your grasp! Being able to include them as and when you want / need to in your chart, you can easily reconfigure your reports and will no longer find yourself breaking out into panic sweat whenever that dreaded email for changes hits your inbox. Easy peasy!

No query language. No coding. No complex Excel formula. Just simple ready-made functions waiting for you to make use of them.

Impressed yet? This is just the tip of the iceberg of what you can expect to gain from Power Pivot.