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.

---

Subscribe to Crabby's columns
Read all the Crabby Office Lady columns
Get the Crabby Office Lady's book

---

Ah springtime, when a young man's fancy turns to....PivotTable reports. Some of you have suggested I do a column about this mysterious (and, therefore, often maligned) tool. Well, I've put it off long enough; it's time to jump in there and figure out just what the heck a PivotTable report is and why I should care. (Get in here — I'm not going it alone.)

 Note   Don't give me that superior look if you're already well versed in the "data shimmy" (my private term for PivotTable reports). 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.

The Tao of data

Everything is haywire this week. My hairdresser Jim, who is normally the model of restraint and good taste, found it in himself to add a "touch of color" to my locks this week. He said it would make me feel "fresh as a springtime rain." (Just between us, I feel like a large, fluffy stalk of celery.)

But while I sat in the chair thinking about what George Burns said about hair ("Too bad the only people who know how to run the country are busy driving cabs and cutting hair"), my mind started to drift to Jim's salon — 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? A weave instead of a braid? How does he just know it's time for a complete overhaul? When I asked Jim, he smiled his Cheshire cat smile, narrowed his eyes, bowed his head, and murmured two words: PivotTable reports.

PivotTable reports are hair to stay

As I sat under the dryer (as yet unaware of the springtime green making itself at home in my follicles), Jim explained what PivotTable reports are.

"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 dry (not to mention green). To redirect my attention, Jim 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. In Jim's case, the kind of data he needed to view were things such as:

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

"So," you say, "If Jim 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 Jim 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 Jim literally move those pieces around to see how they all relate to each other.

Jim 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 Jim were the vengeful type of hairdresser — not that he is, of course.)
• The amount each customer is spending each month (minus tip).
• The net profit that Jim — as the salon owner and operator — makes per customer per service in a given month. (See this in action below, in the film.)

And from this information, Jim can create things like personalized summaries and reports. The excitement! The joy! The things he'll be able to tell his grandchildren!

Sure, Jim could do all this with a spreadsheet, an abacus, and a mathematical savant, but this is faster, easier, and not so messy.

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

Let's see a beauty shop PivotTable report in action. No, it's not Hair or Shampoo or Hairspray. Get your popcorn, pour your peanut M&Ms® all over it (as we do at my house), and let 'er roll. (Please, no talking or I'll have to ask the usher to remove you.) After the movie, let's have a little audience discussion time.

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.

Style your way to better data analysis

"How in the sam hill did he do that?" you may be wondering.

Jim explained that he gets his data to "speak" to him in ways that my readers and I could not possibly understand. (Insulted, I clued Jim in to the intelligence and resourcefulness of my readers. Then I hopped off the chair, put him in a headlock, and told him to take it back — which, of course, he did.)

So, let's muck around with Jim's data, shall we? Ever play "beauty salon" as a kid? It's going to be like that, but with data instead of the family dog or your little sister. A little Dippity Doo over here, a flippy-bouffant thing going on over there....

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

Step 1: Gather ye data while ye may in an Excel spreadsheet like this one:

Step 2: Insert a blank PivotTable report into your spreadsheet. (To learn how to do this, look in Help from within Excel or on Office Online.

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.

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.

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.

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.

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. However, much has already been written about this topic by people much smarter (no!) and more pivot-esque than I. Check out those links I mentioned in the See also section of this column.

"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 Was this information helpful? tool below. And remember: If you don't vote, you can't complain.

Subscribe to Crabby's columns
Read all the Crabby Office Lady columns
Get the Crabby Office Lady's book