Chapter 12. Pivot Tables: Hardcore grouping

Pivot tables are among Excel’s most powerful features.

But what are they? And why should we care? For Excel newbies, pivot tables can also be among Excel’s most intimidating features. But their purpose is quite simple: to group data quickly so that you can analyze it. And as you’re about to see, grouping and summarizing data using pivot tables is much faster than creating the same groupings using formulas alone. By the time you finish this chapter, you’ll be slicing and dicing your data in Excel faster than you’d ever thought possible.

Head First Automotive Weekly needs an analysis for their annual car review issue

Head First Automotive Weekly has signed you on to help them create some table visualizations out of their annual car test data.

The magazine’s readers are serious data junkies; they just love looking at stats on all the cars available. On the one hand, it’s great that you have such passionate readers, but on the other hand, it’s kind of a drag that you have to slice and dice the car data in so many ways in order to satisfy them.

Load this!

www.headfirstlabs.com/books/hfexcel/hfe_ch12_pivot_tables.xlsx

Sharpen your pencil

The editor has just given you a pretty broad request. Using the fields in your data, come up with four ways you might group and summarize the MPG (miles per gallon) information. One is provided for you.

1. The average MPGs grouped by Make and Type._________

   Note

   Here’s an example.

2. ______________________________________________

3. ______________________________________________

4. ______________________________________________

5. ______________________________________________

Note

Put your answers in the blanks.

Describe some of the steps you’d have to undertake to implement these summaries using formulas. How long do you think it would take to do all this?

_____________________________________________

_____________________________________________

_____________________________________________

_____________________________________________

Sharpen your pencil Solution

You just brainstormed a bunch of ways to summarize the data on the basis of MPG. What did you find?

1. The average MPGs grouped by Make and Type.

2. The minimum MPG for each Make.

3. The maximum MPG for each type of car.

4. The average MPG grouped by reliability and Make.

5. The average MPG of SUVs under $40,000.

Describe some of the steps you’d have to undertake to implement these summaries using formulas. How long do you think it would take to do all this?

This would take quite a while. For each one of these, I’d have to write formulas to group the data in one or more ways, and I’d have to write formulas like AVERAGE or MAX that would actually calculate the summaries.

You’ve been asked to do a lot of repetitive operations

There is complexity in these data summaries that you’ve envisioned. You can slice the data in a million different ways, and it could take forever.

But there’s simplicity as well. These summaries basically have you doing the same sort of operation over and over again: applying formulas to various groups and subgroups of data.

Pivot tables are an incredibly powerful tool for summarizing data

How do you group data in a bunch of different ways and summarize the groupings with formulas? The best approach is to use Excel’s pivot tables. Pivot tables are an extraordinarily powerful feature of Excel that let you quickly and visually run these operations. Here’s the basic idea behind how to make them.

What you want to do is take your data and put the different fields together into a new summary table.

Drag one of your fields to the row blank. This will show unique values from that field as row elements. That is the sort of grouping that takes place in pivot tables.

Next, you do the same thing for the element you want to represent in your column. Drag the field name into the column blanks on the pivot table.

Finally, pick the quantitative field that you’d like to see summarized and pick the function you want to use. Generally (but not always), your rows and columns will be categories, and your data blank will be the numerical thing you want to group and summarize by the row and column categories.

Pivot table construction is all about previsualizing where your fields should go

Pivot tables are their own little universe inside Excel, and people get intimidated at first by all the options. The thing you need to remember is this: stay focused on your analytical objectives, and try to create tables that help you understand your data better.

Exercise

Try out creating your first pivot table from the summary you envisioned in the first exercise of this chapter.

Note

Go through these steps to create your pivot table.

1. Select a cell in your data, and click Insert > Pivot Table.

2. Drag fields from the Field List to the column, row, and data blanks.

3. Click the “Sum of MPG” drop box, and change the Value Field Settings so that you’re taking the Average. Also, tweak the Number Format so that you don’t end up with a bunch of decimal zeros.

   

Exercise Solution

You just created your first pivot table, summarizing average MPG by Make and Type. How did it go?

1. Select a cell in your data, and click Insert > Pivot Table.

2. Drag fields from the Field List to the column, row, and data blanks.

3. Click the “Sum of MPG” drop box, and change the Value Field Settings so that you’re taking the Average. Also, tweak the Number Format so that you don’t end up with a bunch of decimal zeros.

   

The pivot table summarized your data way faster than formulas would have

The steps to create a pivot table are pretty simple. Just select your data and drag your fields where you want them to be.

The steps to create this table using formulas are more complicated.

Using formulas to create something like a pivot table

That’s true. In order to create the functionality of a MAXIFS() formula you’d need to write a long array formula, and those are beyond the scope of this book. Aren’t pivot tables just easier?

There are no Dumb Questions

Q:	Q: Where does the word “pivot” come into play? Have we been pivoting the data somehow?
A:	A: You “pivot” the data when you look at it from different angles. One of the things that pivot tables enable you to do is switch around your data summaries really quickly, so if you wanted to “pivot” your rows and columns literally you’d be able to do it easily.
Q:	Q: But what if I just wanted to make groups and summarize them, but not actually pivot the data?
A:	A: Don’t get too hung up on the word “pivot.” If you think of pivot tables as efficient ways to group and summarize, you’ve grasped what they’re all about.
Q:	Q: How would I use pivot tables in a workflow for data analysis?
A:	A: They are great for doing exploratory data analysis, where you are looking at data from a bunch of different angles in order to prepare your ideas for the use of more advanced data analysis techniques.
Q:	Q: So if there was something in the data I wanted to optimize with Solver, for example, I might knock around inside the data with pivot tables in order to develop my ideas about what I wanted to optimize?
A:	A: That’s exactly it. And the reason that pivot tables are good for dealing with data in this way is because they are so fast. As long as it would take to create the formulas you’d need to create one summary table, you can create a whole mess of pivot tables.
Q:	Q: Speaking of formulas, what if I want the speed and flexibility of pivot tables but just want a little scrap of summary data rather than a full-blown table? Got anything for me?
A:	A: There are a bunch of ways to do this. First, even though you want a piece or two of data, you can still create a pivot table for it. Just be judicious in your use of grouping and filtering, and you can get the answers you need quickly.
Q:	Q: I was thinking more along the lines of a pivot formula.
A:	A: Check out the GETPIVOTDATA() function. You’ll need to create a pivot table, but once you have it you can call it from another worksheet using that function.
Q:	Q: What else?
A:	A: You can also replicate a lot of the functionality of pivot tables using structured references. The more advanced structured reference syntax enables you to group data in ways you can’t using conventional references. Yet another reason to deepen your skills as a formula master.
Q:	Q: Nice. Another question: so I liked the fact that I can use different functions to calculate the “Data” portion of the pivot table. But what if I want to do something really fancy—say, use a function that compares my data field to some other data field?
A:	A: Not a problem. Under your field’s settings, click “Show values as...” for some options to compare the data to other fields.
Q:	Q: Impressive. Here’s another question: say I wanted to use pivot tables that aren’t actually in my document. Like, say they’re inside a corporate database.
A:	A: Again, this is no problem. Excel uses a technology called OLAP (Online Analytical Processing) that enables it to plug into (potentially huge) databases. This usage of pivot tables is beyond the scope of this book, but using pivot tables to access and evaluate external data is one of the powerful uses of Excel.
Q:	Q: Hmm.
A:	A: You know, you’re throwing a lot of questions at pivot tables and seem to be having a hard time stumping the feature.
Q:	Q: Yeah, pivot tables sound pretty powerful.
A:	A: It’s a good idea to use them every time you can, and to stay on the lookout for opportunities to use them. Chances are, if it’s possible at all to do a task with pivot tables, doing that task with pivot tables is faster than the alternatives.
Q:	Q: Got it. OK, so here’s a question. How does this filtering thing work? With all the power of pivot tables, what does filtering get me?
A:	A: Let’s take a look....

Your editor is impressed!

You need to filter.

Pivot tables have yet another dimension: filtering. Filters allow you to take the elements you’ve assigned to your Values box and calculate only the ones that meet your criteria. In this case, you want to look at average MPG only for cars with a reliability of 5. Let’s take filters for a spin....

Exercise

Go back to your pivot table and tell it to show the summary only for highest reliability cars. What box do you use?

Exercise Solution

You just created your first filtered pivot table. What did you find?

Pivot Tables Exposed

This week’s interview: Are they really all that?

Pivot Table: I must say that I’m delighted to be here. It’s always such a joy to spread the word about me and what I do. People need to recognize that there is nothing inside of Excel that matches my raw analytic power.

Head First: Well, we’re happy you’re here and you’re welcome any time. But I have to ask, is it true that there is nothing in Excel that matches your analytic power? Actually, let me rephrase the question. Isn’t the analytic power something the analyst brings to the game, not something you do?

Pivot Table: OK, touchÉ. The user needs to be a good analyst in order to use me to come up with good analytic results. But I’m a big deal.

Head First: Just to play devil’s advocate here, are you? It seems that all you can do is group data on two dimensions and run a calculation.

Pivot Table: Two dimensions? You should think bigger than that. Try dragging more than one field to my column, row, data, and filter blanks. You can insert as many levels of dimensionality into your pivot table analysis as you like.

Head First: Yep, that’s pretty powerful.

Pivot Table: Told you so.

Head First: But wouldn’t that clutter up the spreadsheet big time to have four or five or ten dimensions of summary?

Pivot Table: I would point you to your previous observation. It takes a good analyst to do good pivot table-based analysis. Yes, dragging a whole mess of fields into a pivot table makes the resulting table full of fields, but if a user has the analytic chops for it, then creating such a table may be exactly what is in order.

Head First: But there must be something to be said for keeping things simple.

Pivot Table: There is indeed. Even the most brilliant analyst is going to have to present results to someone who isn’t a brilliant analyst, so keeping things simple for an audience’s sake is a good idea.

Head First: Well, suppose that brilliant analyst wanted to make a chart rather than a table. They’d need make to a chart off the pivot table—they can’t make a pivot chart.

Pivot Table: Au contraire, mon frère. I can make pivot charts. The feature is called Pivot Charts, and it’s right under the Pivot Table button under the Insert tab. Charts are no problem.

Head First: What if we want to do some more subtle formatting of our pivot table? Can we just format the pivot table directly as if it were a regular spreadsheet?

Pivot Table: You can, but a lot of people will copy and Paste Special > Values the result of a pivot table to another sheet and then format the pasted results. The downside of that approach is that you lose your formulas. But the upside of the approach is that your formatting doesn’t mess up if you want to change your pivot table to look at another set of summaries.

Head First: Last question. This is possibly a weird one. You’ve been dropping some interesting terminology: touchÉ and au contraire, for example. Are you French?

Pivot Table: Let’s just say my versatility in processing different types of data has enabled me to cultivate a certain cosmopolitanism. But no, I’m not French. I’m a feature in a computer program, silly.

You’re ready to finish the magazine’s data tables

Time to wrap this analysis up and execute the pivot tables your client needs.

Long Exercise

Create the data summaries that your client needs using Excel’s pivot tables.

1. Implement the tables that the editor describes on the facing page. You might have to get a little creative....

   

2. In order to create a presentation, copy and Paste Special > Values each to a new sheet. Format the results as you see fit.

Long Exercise Solution

With your new knowledge of pivot tables, you created the tables the Head First Automotive Weekly needed for its review issue. How’d it go?

1. Implement the tables that the editor describes on the facing page. You might have to get a little creative....

2. In order to create a presentation, copy and Paste Special > Values each to a new sheet. Format the results as you see fit.

   
   

Your pivot tables are a big hit!

Head First Automotive Weekly released a particularly illuminating annual car review this year, all thanks to your well-crafted pivot tables. Reader response has been overwhelmingly positive.