Skill Up! How to do a pivot table in Excel

I’m of the opinion that there are certain skills that every man, woman and child must learn. I’d mandate it out of the womb if I could but tiny hands make it difficult to use big keyboards apparently :)

A pivot table comes into play when you want to shift around data to look at it in different ways.

For example, let’s say you found a list of birthdays for all the members of the Duggar family (name, birthday…) and you wanted to know how many of them were born in January. You could use a pivot table to find that.

Or say you wanted to know the dates, deaths, states, influences, and details behind the 400+ terrorist attacks planned, thwarted or successful in the United States since 9/11. (I built this infographic together using data from Pivot tables.)

Let’s try to find something in between huge families and terrorist attacks. Like sales data from a national software company.

First, let’s start with some data. In this case sales results from January and February for a national team of sales reps listing Date of Sale, the Product, Revenue, Sales Rep and Sales Region:

Step 1: Select the data. Hint: You can sometimes do this by selecting the entire sheet (⌘-A/Ctrl-A) and Excel is smart enough to find the dataset. Once you have selected the fields you want, go to Data in the top menus, choose “Summarize with Pivot Table.”

Step 2: Confirm where you want to put it. By default, it will put the pivot table on another sheet. (Just press OK and a new sheet will appear.)

Note: there are lots of bells and whistles which we’re going to avoid on this rather simple tutorial. Just know — like any other Microsoft product — you’ll never use 75% of the features there. :)

Step 3: Now we get to play with the data. The image to the right is the PivotTable Builder which allows you to plug and play rows and columns and filters and all sorts of goodies. We’ll keep this simple for now.

Let’s say for example we wanted to see how each rep was performing. Then we wanted to see how each region performed as well. Good news we can do that all in one big PivotTable and it’s pretty easy to do. We’ll be working with Rows, Values and Columns to accomplish this.

Don’t get too hung up on the details and outputs. Some of this is still a very manual process to get just right… just know that many of your company knowledge pain points can be solved with this tool.

Step 4: Drag and drop stuff. First, we’re gonna drag “Sales Rep” into Rows and “Revenue” Values. Just drag it from the fields on the top.

And just like that, you’ve done your first pivot table.

If you want to see how regions perform in the same table… easy… drag “Sales Region” to columns:

From this we can see that Central and East are tied for top revenue contending and that Martha is killing it!

Good job Martha… and good job to all of you! Now go teach your infant how to do this.