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!
tagrilla123 
KC 
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.