Pivot, swivel, and roll: Data management with the stars

Crabby Office Lady: (c) Microsoft Crabby Office Lady

Knowing how to compile all your data into one huge spreadsheet is one thing; making sense of it is another. Let's get an overview of PivotTables, those flexible little contortionists of the data world.

Crabby Office Lady columns
Crabby’s blog
Crabby’s podcasts

You need to learn about and use PivotTable reports. Period. If you're a business owner, a parent, a coach, or anyone else who deals with data, you're doing yourself a disservice by not employing this handy tool. So...it's time to jump in and figure out just what the heck this mysterious (and therefore often maligned) PivotTable report is...and why we should care.

 Note    While this column takes an introductory look at what PivotTable reports are, I've included some useful links in the See also section near the top right of this column for those of you already well-versed on the topic.

The Tao of data

How do you stay on top of everything you need to know? For example, take Steve, the owner of the salon where I get my Crabby locks spiffed up for the week. How does Steve remember all those details about his customers, the services he provides, and the money he takes in? How does he manage to keep everything working so smoothly? How does he know I'm coming in for a color and not a cut? How does he just know it's time for a complete overhaul? And how does he know how much to overcharge me? When I asked Steve, he smiled conspiratorially and murmured two words: PivotTable reports.

PivotTable reports are hair to stay

While my colorist was working her magic, Steve explained. "A PivotTable report is an interactive table — a cross-tabulated Excel report — that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest."

At this point, I nodded off.

When I came to, my hair was the lovely shade of blue you see now. To redirect my attention, Steve started to tell me more about PivotTable reports, those clever little graphs that let him view his business data and information in a variety of ways, and why his work depended on them. In Steve's case, the kind of data he needed to view were things such as:

  • Customer name
  • When the customer came in
  • Service the customer needed (cut, color, weave, style, and so on)
  • Cost of service
  • Tip paid
  • Total cost
  • Steve's cost
  • Steve's net profit

"So," you may be saying, "If Steve could track all that information in a spreadsheet, why does he need to create a PivotTable report?" Think, people! It's like comparing the bearded lady, the tightrope walker, and the contortionist at the circus: All are exciting and unique, but only one is shockingly flexible.

Consider how Steve wants to look at all this information. While a spreadsheet just shows him lists of data, a PivotTable report compartmentalizes the various pieces of information (such as customer name, the services rendered, and tips paid) and lets Steve literally move those pieces around to see how they all relate to each other.

Steve can view, for example:

  • The average tip amount left by each customer, per month, and per service. (This information would be helpful to know if Steve were the vengeful type of hairdresser — not that he is.)
  • The amount each customer is spending each month (minus tip).
  • The net profit that Steve — as the salon owner and operator — makes per customer per service in a given month. (See this in action below, in the little movie.)

And from this information, Steve can create personalized summaries and reports, and get a real, tangible snapshot of how the business is doing.

Sure, Steve could do all this with a spreadsheet, an abacus, and a mathematical savant, but this is faster, easier, and decidedly prettier.

An example: The long, short, and curly of PivotTable reports

Let's see a beauty shop PivotTable report in action. This little short is going to show how much you, as the owner, made per customer per service per month. This particular PivotTable report displays a whole year's worth of information.

Play Demo

Style your way to better data analysis

Ever play "beauty salon" as a kid? We're going to do that now, but with data instead of the family dog or your little sister.

Here's how we can recreate Steve's PivotTable report:

Step 1: Round up your data in an Excel spreadsheet like this one:

The starting data, in an Excel spreadsheet

Step 2: Insert a blank PivotTable report into your spreadsheet. (This is how you do this in Excel 2010, Excel 2007, and Excel 2003.)

Blank PivotTable

The Field List, where the column headers of Jim's data are listed.

The blank PivotTable report, just waiting to run amok with data.

Now what? Let's move stuff around and see what we can come up with.

Step 3: Click the Customer field in the PivotTable Field List, drag it up to the area of the PivotTable report that says Drop Row Fields Here, and then let go of it.

As you can see, it automatically populates the area with the Customers from your original data.

Adding the Customer field

Drag the Customer field...

...to the Drop Row Fields Here area.

Step 4: Click the Service field in the PivotTable Field List, drag it up to the area of the PivotTable report that says Drop Column Fields Here, and then let go of it.

As you can see, it automatically populates the area with the Services from your original data.

Adding the Service field

Drag the Service field...

...to the Drop Column Fields Here area.

Step 5: Click the Year field in the PivotTable Field List, drag it up to the area of the PivotTable report that says Drop Page Fields Here, and then let go of it.

If you click the arrow next to Year, you can choose the individual months from your original data.

Adding the Year field

Drag the Year field...

...to the Drop Page Fields Here area.

Step 6: Click the Net profit field in the PivotTable Field List, drag it up to the area of the PivotTable report that says Drop Data Items Here, and then let go of it.

As you can see, it automatically populates the area with the actual Net profit data from your original data.

Adding the Net profit field

Drag the Net profit field...

...to the Drop Data Items Here area.

And voila! Now if you really want to go crazy, try moving some of the data around. For example, to see how much each customer tipped per service per month, click and drag the Net profit field off the table and replace it with the Tip paid field.

 Note   Don't get too hung up on the names of the various areas of the PivotTable report. You can drag any item in the Field List to any part of the PivotTable report. It just takes a bit of practice hauling and dragging stuff around to see what works for you. Think of it as rearranging furniture: Sometimes you don't know what you really want until you see exactly what you don't want.

More information

There are so many other things you can do with PivotTable reports, including formatting, reusing the data, and making it interactive in a Web browser.

Next up: PivotChart reports

I'd be completely remiss if I didn't talk about PivotChart reports and how they're related to Pivot Table reports. A PivotChart report allows you to visualize all the data you massaged in your PivotTable report. It's also interactive, allowing you to sort and filter underlying data.

Next week I'll go over the basics of PivotChart reports. After that, you should be on your way to really getting to know these limber and lithe data handlers.

"It's an ill wind that blows when you leave the hairdresser." — Phyllis Diller

About the author

Annik Stahl, the Crabby Office Lady columnist, takes all of your complaints, compliments, and knee-jerk reactions to heart. Therefore, she graciously asks that you let her know whether this column was useful to you — or not — by entering your feedback using the Did this article help you? feedback tool below. And remember: If you don't vote, you can't complain.

Crabby Office Lady columns
Crabby’s blog
Crabby’s podcasts