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!

## 6 Comments

I’m so glad you included Step #2! I hate when people don’t rename their tabs 🙂

Pivot tables have always been slightly intimidating to me, and I’d much rather create charts than spend time in pivot tables, but this info on grouping and crosstabs has really helped me to deepen my understanding. Thanks!

Renaming the groups is a nice touch. Great tutorial. 🙂

yes, Pivot Tables are a great tool, I cannot believe how many hours of work I have saved thanks to the pivots. Great post! 😉

I have been using Pivot Tables for quite a while, but just learned about groups maybe last week. Definitely has been a huuuuge help!

You can make Pivot Charts from Pivot Tables. Basically, charts can update, expand and collapse as the table does. I have found them super helpful for analyzing various scenarios quickly.

I really loved the tips, I’m doing a search for the students.