Sunday, November 09, 2008

"Mind" -> Pivot The Data

My son is thinking about becoming an engineer when he grows up. At a home school assembly, he is supposed to answer some questions about his career choice. My wife and he thought I would be a good resource for information, and they were asking me what does an engineer do. Although I am in marketing today, I started off as an engineer.

One of the things that I told my son is that the hallmark of an engineer is "somebody that wants to solve problems." I find this engineering desire used in my marketing job every day, and because of this desire, I am a better marketeer.

So, if you want to find a solution to a problem, first you need to understand what is the root of the problem, and as a firm believer in "The Fifth Discipline," I know that getting to the root of the problem is not simple. There is only one way to get root cause: look at the data.

Now this sounds so very, very obvious, but I find on a consistent basis that most people don't analyze the data that they get. As a matter of fact, they often don't even use the tools that are at their disposal. So, I am immediately going to tell you that you have one fantastic tool in solving problems if you do anything with spreadsheets.

A Pivot Table.

If you don't use pivot tables, you may have your bachelor degree in solving problems, but you need to have a Master's degree or a Ph.D. in solving problems to be really successful. As a matter of fact, I work with finance people and marketing people all day, and it constantly surprises me how most people may have some familiarity with a spreadsheet, but have little to no background with Pivot Tables. The analogy is using a spreadsheet without a pivot table to analyze data is a bit like only driving your car in first gear. Your car has five gears for a reason. To have a vehicle that goes fast, you need to use all of them.

I think the biggest problem with pivot tables is that until you see them in action with somebody for a while, they aren't really clear what they are. For example, look at the description of a pivot table at Wikipedia: "A pivot table is a data summarization tool found in data visualization programs such as spreadsheets...."

Wow, that's helpful...NOT!

The best way of thinking about a Pivot table is that it is a spreadsheet that has many different spreadsheets "hidden" inside, and you can almost instantly pull them out. There are two things you need to know about pivot tables:

1. How to use them
2. How to make them

On this first post, we are going to start off on "how to use them." We'll save the creation for later.

Therefore, I am going to assume that for this first step you are just going to want to become the user of a pivot table that I made for you. Since I work in the hard drive industry, I am going to give you an example from my industry, although the data is all made up.

If you are selling hard drives, you will probably track several things:

a. What is the size of the hard drive that you are selling
b. What is the RPM of the hard drive that you are selling
c. Who sold the hard drive?
d. How many sales of the hard drives have been happening every month.

Now, let's say your hard drive boss comes to you, and he says, "Hey, how have the sales of the 5400 RPM drives doing versus the 7200 RPM drives? You look in your email and you see that I have sent you a pivot table in a spreadsheet.In this case the answer is very simple. Because when you opened up the spread sheet you have the answer right before your eyes.

You say to your supervisor, "Well I have the data right here. In January and February, the 5400 RPM models sold more than the 5400 RPM models. However, in March the 7200 sold better than 5400 RPM models."

Your boss thinks for a second, and he says "well, that is interesting, but I'm curious, how are the sales team doing sell hard drives?"

Now, normally if you wanted this answer you would have to go back to another spreadsheet and pick up all the names of the sales people and see how they have been selling. However, you have a pivot table. This is where the secret sauce comes in.

Before, we get to the next step, we need to ask ourselves, "just what exactly is a spreadsheet?" The answer maybe is not exactly as simple as you might think when I tell you that you can't describe a spreadsheet as a spreadsheet.

In reality, a spreadsheet is simply a list of something. In our case, the question are being asked about the number of hard drives sold. This is the most important point because this defines the characteristic of the pivot table. Either explicitly or intuitively, you need to understand that they only reason that your boss is talking to you is about the number of hard drives sold. In the future, he may not be interested in the number of hard drives. He might be interested in the selling price of each hard driver. He may be interested in the profit of each hard drive. However, in this case, he is interested in the volume of sales for each hard drive.

This is very important because I only gave you a pivot on the quantity sold. Therefore, you know that every cell that is between the "month's column" and the "RPM row" is going to be a quantity of HDD sold. You'll need to remember this, because we always need to be able to leave the data in the pivot. (If this is confusing, just wait a minute and keep reading.)

Now, if you can get the next part of the demonstration, you are about 90% over the most difficult part of understanding what a pivot table is. In the illustration to the left, I have identified three areas of the pivot table.

The area in the upper left hand corner is the "holding pen" for the rows in the data. I have identified this with a red line. If you want to put certain types "classifications of the data" out of the current grid, or put "classifications of the data" into the current grid, you drag the classifications in and out of the holding pen.

The pink line is the "active data classification rows" that is currently being used. In the current case, it is very simple. The spreadsheet is set up to show the sales of hard drives per month, and the row shows the data classified into two different rows: 5400 RPM and 7200 RPM.

Finally, the green area is the labels for the data area.

Notice, the first column has all the "data classifications" listed: in our example, we have the ability to classify the data by Capacity, Salesman, or RPM. These are the only three things in the first column that have the special gray box.

Now, there is another gray box, but it is in the second column. It says "data," which is important. No matter how I construct the pivot table, the second column is always going to be some type of generic "data" type label. (Over simplified, but a good way of thinking about it right now.)

Now, lets go back to the second question of your supervisor. He wanted to know sales by salesman. Very simply, drag the RPM out of the row area and into the holding pen, and this action takes out the RPM classification. Then, you drag the salesman from the holding pen to the row area, and this puts the new classification in.

If you are successful, then you will have the spreadsheet off to the side. Now, the first time that you drag a row label out of the first column, it may be a little confusing. They way that you do it is to put your cursor on the grey box RPM, then click and drag the grey RPM into the holding pen.

Then click and drag the gray Salesman box onto the row area. As a hint, make sure to drag the grey box onto the row area. Remember that since the row area and the column area intersect at the top left hand corner of the spreadsheet, if you drop the label onto this box, the pivot table will try and put the salesman onto the column area and not the row area! In this case, you would have to pull the label until it was on top of the "Total" box. However, if you do this, you'll have the table with the data quickly called out. You have the answer for your supervisor with one drag of the mouse.

The final trick on a pivot table for today is the "nesting of rows."

After your box asks you this, he says, "Well that is interesting, I wish I could see the breakout of the sales of 5400 and 7200 RPM drives by sales person."

One of the magic items in pivot tables is that you can build up the row area. In this case, all we need to do is pull down the RPM into the column area without pulling out the salesman item. If you put it in the left side of the row area, the RPM will show up first. If you drag it to the right side, the salesman will show up first. If you want to switch the order on the rows, just simply drag the gray boxes either right or left.

In this case, we get this table. The answer only takes one click and drag.

Your supervisor says, "Well Sally isn't selling any 5400 RPM drives. She just got into sales, and it looks like she doesn't know how to sell them. I'll go and make sure that she gets training on how to sell these drives."

This is the magic of pivot tables. Once you have the table, you can find answers hiding in the data in seconds rather than hours. This type of work allows you to get to the bottom of questions extremely quickly.

Today, we used a very simply example. This is very good to start on. However, once you get down how a pivot table works, you will find that they can be use in much more flexible ways.

They truly are a revolutionary tool.

Learn 'em.