6 Comments

  • Chris Newman says:

    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!

  • tagrilla123 says:

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

  • MasterExcel says:

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

  • KC says:

    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.

  • Leave a Reply to Chris Newman Cancel reply

    Your email address will not be published. Required fields are marked *

    How to Save Time and Energy by Analyzing Your Data with Pivot Tables in Microsoft Excel

    Updated on: Jul 19th, 2016
    Data Analysis
    , , ,
    A screencast showing how to use pivot tables in Microsoft Excel.

    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.

    The first step is inserting a pivot table from scratch.

    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.

    Your pivot table will appear in a new sheet. To keep my workbook clutter-free, I give each sheet a descriptive name.

    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.

    Explore your pivot table fields.

    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.

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

    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.

    Watch 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.

    Drag the State variable from the Pivot Table Fields box into the Columns box.

    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.

    drag State into the Rows box, right below Gender. The tallies in the pivot table on the left update themselves accordingly.

    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.

    Drag the State variable into the Filters section. State now appears in the first row of my spreadsheet

    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.

    Drag Age into Rows and Employee into Values.

    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.

    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.To stay organized, let’s give each of the groups a descriptive name which will keep everything neater in the long run.

    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.

    Explore the toggle feature of our Younger Half and Older Half groups by clicking on the plus and minus signs to expand or collapse the menu.

    I hope you enjoyed this gift of knowledge!

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

    6 Comments

  • Chris Newman says:

    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!

  • tagrilla123 says:

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

  • MasterExcel says:

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

  • KC says:

    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.

  • Leave a Reply to Chris Newman Cancel reply

    Your email address will not be published. Required fields are marked *

    You Might Like

    Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

    Enroll

    How to Bring Your Technical Tables to Life

    Just because I’m pro-graph, I’m not anti-table. Technical tables have so much value, especially as visual appendices for reports. In this blog post, you’ll get ideas for bringing your technical tables to life.

    More »

    Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.

    Enroll

    Subscribe

    Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.