Pivot tables are the fastest, easiest way to make sense of your data, and they’re easier than you think. In this post, I’ll show you how to insert a pivot table and then drag and drop variables to find patterns in your spreadsheet.

Step 1: Insert a Pivot Table

The first step is inserting a pivot table from scratch. Click on the cell in the upper left-hand corner of your tabular data. In this case, we would click on cell A5 because that cell is the upper left-most cell this table. Then, go to the Insert tab and click Pivot Table.

 

Step 2: Rename Your Sheet to Stay Organized

Your pivot table will appear in a new sheet. To keep my workbook clutter-free, I give each sheet a descriptive name. Rename your new pivot table sheet (something easy like “pivot” is fine) by right-clicking on the sheet and clicking on Rename Sheet.

 

Step 3: Explore the Pivot Table Fields

Now, let’s take a closer look at that pivot table that popped up in your new sheet.

In our original data sheet (named Pivot Table Data in my example), the columns are named Employee, Gender, Age, Industry Experience, and State.

Next, let’s check out the pivot table’s sheet (named Pivot Table in my example). Some of the important pivot table features appear along the right side of my screen. The boxes say Pivot Table Fields, Filters, Columns, Rows, and Values.

The Pivot Table Fields box, in the upper right, contains all the variables that we get to play around with. Notice how each of the columns of data from our Pivot Table Data sheet show up here: Employee, Gender, Age, Industry Experience, and State.

 

Step 4: Drag and Drop Variables

Now, on to the fun part, dragging and dropping variables! This feature is what makes a pivot table a pivot table.

Let’s start with simple math: Figuring out how many males and how many females are listed in our spreadsheet.

Click on Gender in the Pivot Table Fields list and drag it downwards into the Rows box. The pivot table, located off to the left in the main spreadsheet area, will say Row Labels, Female, Male, and Grand Total. It’s starting to build a table for us, which will eventually contain tallies of males and females.

Then, drag Gender into the Columns box. Watch how the table pivots, or switches from rows to columns accordingly. Now, Female and Male are listed across the top of the table rather than down the side.

 

Let’s figure out how many males and females are in the dataset. We’ve got the outline of our table but we need to fill in the body of the table with the actual tallies.

Watch below as I drag Employee into the Values box. The pivot table on the left-hand side of my screen will automatically update to show that there are 6 female employees and 4 male employees in my spreadsheet.

 

On to the really, really fun part: cross tabulations! Cross tabulations, or crosstabs for short, is a fancy way of saying that pivot tables give us the ability to stack multiple variables on top of each other. Figuring out how many males and how many females are in our spreadsheet is a good starting point, but crosstabs let us dig even deeper into the information.

Watch as I drag the State variable from the Pivot Table Fields box into the Columns box. The Gender categories (Female and Male) are listed along the left side of my table and the State categories (DC, MD, and VA) are listed along the top of my table.

The inner body of the table shows how many people fall into each category. For example, there are 3 females who live in DC, 3 males who live in DC, 1 female who lives in Virginia, 1 male who lives in Virginia, and 2 females who live in Maryland.

The Grand Total section reminds us that we’re talking about 10 people altogether.

 

Another way to cross tabulate your data is to double-stack two or more variables into a single box.

Before, we had Gender in Rows and State in Columns.

Now, I’ll drag State into the Rows box, right below Gender. Watch how the tallies in the pivot table on the left update themselves accordingly.

Within a single box, like the Rows box, you can also re-order the variables. You can have Gender on the top, or, drag State above Gender. There’s no single right answer here; practice dragging and dropping your variables into whichever order is most interesting and useful for you.

 

Bonus! Filter Out Certain Variables

We’ve explored the Pivot Table Fields, the Columns, the Rows, and the Values.

Now, let’s take a look at the remaining box, Filters. Just like its name implies, this option lets you sift out certain categories so that you can focus exclusively on the information that’s most helpful for you.

Watch as I drag the State variable into the Filters section. State now appears in the first row of my spreadsheet. Do you see the little arrow beside the word All? The arrow reminds us that we’ve just created a filter. When I click on that arrow, a drop-down menu appears. I can choose to only look at people who live in DC, Maryland, and/or Virginia.

 

Bonus! Grouping

We haven’t explored the employees’ ages yet. The other categories were simple: Female/Male, DC/MD/VA, and so on. But with Age, there are as many different possibilities as there are employees.

First, watch as I drag Age into Rows and Employee into Values. The tallies aren’t very helpful here. My resulting pivot table on the left side of the screen just tells me that there is 1 employee for each of the ages listed. For example, there’s 1 employee who’s 24, 1 employee who’s 28, 1 employee who’s 35, and so on.

We need to condense the 10 different ages into a small number of categories. Let’s pretend that we’re interested in dividing the employees into two groups, the younger half and the older half.

Select or highlight the first five ages (the younger half of the employees). Then, right-click and select Group. Those first five ages are now clustered together in a brand new category called Group1.

 

Now let’s do the same thing for the five employees who fall into the older half of the bigger group. Highlight or select those five ages (from 50 down to 65), right-click, and select Group. We’ve got two groups: Group1 and Group2.

 

It’s pretty obvious to us what each group represents—the first group contains the younger employees and the second group contains the older employees. But, this grouping might not be so obvious if we email our spreadsheet to a coworker, classmate, or friend.

To stay organized, let’s give each of the groups a descriptive name. Removing the guesswork will keep everything neater in the long run.

Click on Group1 and simply begin typing Younger Half. Then, click on Group2 and type Older Half.

 

Finally, let’s take a moment to explore the toggle feature of our Younger Half and Older Half groups. Click on the plus and minus signs to expand or collapse the menu.

 

I hope you enjoyed this gift of knowledge!

 

Want to learn more? Join me on August 4, 2016 as I lead a 90-minute webinar on pivot tables. In this hands-on session, you’ll practice using pivot tables with three different datasets. I’ll talk about the pros and cons of applying pivot tables to each dataset, so you’ll have a solid understanding of when to use ’em and when to stick with regular old formulas. And speaking of formulas, we’ll conclude by analyzing survey data with the =countif function, the function I demo’d in my most popular YouTube video to date.