Choose your own PivotTable

This post is brought to you by Allie Rutherford, a Program Manager on the Analytics and Presentation team.

In many offices, there always seems to be at least one person who is referred to as an Excel expert/master/wizard. Their masterful status often originates from one fact: they can make a PivotTable. Are you jealous of this person? Have you ever wished you could have that glory for yourself instead? Do you secretly wonder what a PivotTable even is?

Excel 2013 can help you achieve this masterful status. You’ll be a legend at the water cooler. How? We made PivotTables easier to make than ever before (no, really!).

We’ve done something pretty special: you can create a nice summary of your data with just a few steps.

You can go from this:


To this:

Without doing any of this:

Awesome, no?

Have some data

Let’s go over how this is possible. Before you begin, you gotta have some data. You can borrow mine . It’s sales data! Go ahead and download it, I’ll wait.

Note: If you’re doing this on another table that’s not mine (it’s OK my sales data will understand) and it’s not contiguous (i.e. there are blank lines, whatever), you’ll want to select the whole thing. If your data isn’t tabular (i.e. in column-ular form), then this feature won’t work for you.

Step One

Okay, first thing you do: click on the table. Get that cursor anywhere within the range of data you want to use. I chose this spot:

Take a minute to appreciate the awesomeness that is my dataset; it has sales records (i.e. table rows) that include who made the sale, what office they work in, and the sales amount. What kinds of things might you want to know if this was your data set and not mine?

Some examples might include:

  1. What office is bringing in the most sales?
  2. What office is bringing in the most sales amount?
  3. What month did we do well in?
  4. Do existing customers or new customers buy more?

If you thought of any of these (and probably even if you didn’t), you’re going to want a PivotTable! It can tell you all of those things. PivotTables are very handy for summarizing and categorizing your data, putting it into smaller chunks that you can really munch on. They work best when you have column names and category columns in your workbook as well as something you can count/add up/average/(insert other aggregations here).

Step Two

Ok, ready for step two? On the insert tab, hit the “Recommended PivotTables” button. It’s the second from the left.

You’ll get a dialog that looks something like this:

Here we have a few things: a list of recommended choices we’ve generated for you on the left, a preview of the currently selected table on the right, and the ability to just create a blank PivotTable yourself on the bottom there if we were way off base.

It’s a lot easier to figure something out if you’ve got an example, right? This feature will get you started on PivotTables if you’ve never done one before and/or show you different ways to lay out your data.

Step Three

Anyway, what you should do next is pick your favorite (choose wisely!) and hit OK. Most of you will probably pick the first one (and I do too), so you’ll probably get this on a new worksheet:

Congratulations! You made a PivotTable in three easy steps. We just answered Question #1: What office is bringing in the most sales? It’s Seattle!

What this is all about

What’s most awesome about this (and there’s a lot that’s pretty super awesome here) is that you can quickly get to something that answers a question without knowing much about PivotTables. If we had done this without a PivotTable, it might include SumIF and VLookup and things I can’t even imagine. It would be hard. This is easy.

PivotTable Recommendations will help you make awesome report-outs in no time that will make your coworkers jealous and your boss super happy. It can revolutionize the way you summarize your data and shave time off of your work week. Sounds pretty good, eh? Go get crackin’ on one of your own workbooks!

Here’s the consolidated steps:

  1. Have data that’s organized into columns
  2. Click on the data or select the range you want to summarize
  3. Click on the “Insert” tab and then click the “Recommended PivotTables” button
  4. Choose your favorite PivotTable and hit OK
  5. Party!

Until next time,