|
I remember my introduction to Excel's PivotTables well. I have nightmares about it. It was a dark and stormy day near the Chicago lakeshore, and the wind was blowing, though I could not feel it because I was in a cubicle castle. I was innocent and naive in the ways of Excel's true power, and I knew it.
I sat across from my boss and his big boss-like desk in his office. He asked me for some kind of subordinate-like status report--I can't remember exactly what. I only know the information was in a spreadsheet. I told him where he could find it on the network.
He spun around in his chair, went to his keyboard, and opened my spreadsheet in one fluid motion. I could see my data on his screen. "OK," he began, like a surgeon staring down at a patch of skin, twirling a scalpel in his gloved hand, "Let's see what we've got." He did some menu commands, I saw some dialog boxes open, he pointed and clicked, and a mysterious Excel creation appeared in gray right where my helpless spreadsheet once had been.
A chill went down my spine. I knew what it was. It was a PivotTable (one word).
My boss pointed and clicked, dragged and dropped, and within 60 seconds he had generated a whole new table that had appendages and drop-downs and weird numbers I had never seen before but which I knew existed somewhere in the universe--numbers I had never figured out how to get. I was amazed, enthralled, and intimidated. And he had done nothing illegal.
That was many years ago. Ever since the therapy, I have read many explanations of PivotTables--what they are, what you can do with them, all their many options. Although I've gotten over my fear of them and do use them with a little bit of skill, I have never found a clear, concise description that bridges the gap between ignorance and understanding for those who still fear them. Therefore, I will try to provide one.
Where's the Pivot?
The first thing I'd like you to know is that nothing pivots in a PivotTable. A pivot, to quote Merriam-Webster, is "a shaft or pin on which something turns." There is no shaft or pin in a PivotTable, nor does anything turn. So don't ponder the concept of a pivoting table in an effort to grasp what a PivotTable is. It will not help you.
But in an effort to salvage the metaphor, it might help to imagine laying a string over a spreadsheet column, from the column name on down through the data in that column. Anchor the string at the top (in the header row, where the column name is), then grab the bottom of the string and pivot it up toward the header row (where the column names are) until it lies across the row like a strikethrough. Imagine that action sweeping all the data in that column up into the header row and the data becoming a new set of column names. That's what happens when you create a PivotTable. Sort of.
If that imagery helps you understand what a PivotTable is, great. In my experience, it sort of helps and sort of hurts. You see, PivotTabling is way more violent than gently herding data preschoolers with string and a pushpin.
A Revolution in Data
Before going any further, there are three terms that I want you to focus on, and get it in your head what they mean. Their difference is important to understanding PivotTables. Even though this may seem elementary or even imprecise, bear with me.
The first term is column. In Excel, a column is like a database field. Each one represents a distinct type of information. Example: Name. It would be unusual to have two Name columns in a spreadsheet. But you might have an Address column in addition to the Name column.
The second term is row. In Excel, a row is like a database record. Each represents an instance of what the columns call for, but only one instance. Example: Bob, 1357 Main Street. Thus, as with columns, each row represents a distinct type of information. It would be unusual to have two rows of Bob, 1357 Main Street--unless you wanted to have both and still make a distinction between them, in which case you would create another column--a primary key--by which you could do so.
The third term is data (the plural of datum). In Excel, a datum is like a database value. All the data goes in the cells below the column names. Unlike both columns and rows, data does not have to be unique. You can have more than one Bob. You can have more than one 1357 Main Street. And in almost all spreadsheets, that's exactly what you do have.
To the Bastille!
The creation of a PivotTable is like a data insurrection. The data assassinates the column names and replaces them with names of their own. They wipe out the rows and appoint some of their own to serve as primary keys on rows of their own creation. In a PivotTable, the data takes over the spreadsheet; it becomes preeminent over the columns and rows.
To show you how this works, I'll use one of my job search worksheets as an example (you can read more about this in my article on how to use Excel in Your Job Search ). Let's say you have a worksheet with job-search communication details like this:
You've been slogging away at this since the middle of January, and now it's the middle of February. Your mother calls. She wants to know how the job search is going. "Pretty good," you say. "Are you making enough phone calls?" she asks. "Uh, sure," you reply. "Are you going to networking meetings?" she continues. "Yeah, yeah, I'm doing that," you reply. "Well, who have you talked to?" she presses. "Different places," you reply. It's clear your mother is hungry for information, and it has only been a month. What's going to happen if this stretches into several months? She'll be demanding status reports.
This is where you want a PivotTable. To show you what I mean, follow these steps (from Excel 2003).
1. Copy or enter the above table into a worksheet.
2. Make sure one of the cells containing data is selected.
3. Choose Data, PivotTable and PivotChart Report.... The PivotTable and PivotChart Wizard should start.
4. Step 1 asks you for the source of the data. Accept the defaults: the source of data is an Excel list or database, and you want to create a PivotTable. Click Next.
5. Step 2 asks you to tell Excel where the data are. By default, it should select the table you just entered. Accept the default and click Next. (You can also specify a named range.)
6. Step 3 asks where you want to put the PivotTable. Again accept the default (new worksheet) and click Finish. The wizard will close, and you should get a new worksheet that looks something like this:
7. In the PivotTable Field List, select Date and drag it onto the area labeled Drop Row Fields Here. The data from the Date column will take over the rows as a primary key for the new table. The revolution has begun. You should see something like this:
Note that even though you have more than one entry for 2/2/2007 in the original table, you have only one row for it in the PivotTable.
8. Go back to the PivotTable Field List, select Type, and drag it onto the area labeled Drop Column Fields Here. The data from the Type column will take over the columns and generate a set of fields, one for each unique item, from the data it found in that column of the original table. With the columns adjusted for width, you should see something like this:
Note that even though you have more than one entry for Phone call live in the original table, you have only one column so named in the PivotTable. You get one, and only one, column for each unique datum from the source table.
9. Go back to the PivotTable Field List once more, select Type again, and drag it onto the area labeled Drop Data Items Here. With the columns adjusted for width, you should see something like this:
What you have is a count of each type of communication (E-mail, interview, etc.) for each day you recorded some job-search activity. The data in the PivotTable reflect the intersection of the columns and rows you chose.
The curse and the blessing of this, of course, is that you have many more options than the few we chose. Just to give you a taste, we could have chosen to:
- Use a different field for the Row Fields area. For example, you could use Name instead of Date; this would let you see how many times you've communicated with each person:
- Add a field to the Page Fields area. This would let you filter the table by any of the fields in the original table. You could easily find out, for example, how many conversations you've had with Bob Dylan by adding the Name field to the Page Fields area and selecting his name in the drop-down:
- Add another field to the Row Fields area or Column Fields area. This would let you combine criteria by which to show the rows. You could show, for example, what Company you talked to each day by adding the Company field to the Row Fields area. Or you could display only the communications on which you need to follow up by adding the Requires Follow-up field to the Column Fields area and selecting Yes from the drop-down:
- Change what's displayed in the Data Fields area. For example, you could change the raw counts to running totals by right-clicking on Count of Type, selecting Field Settings..., clicking on Options >>, and changing Show data as from Normal to Running Total in:
This list of options--the ones we did not use--doesn't tell all of what is possible. My goal is not to give you a full list of options or to explain everything there is to know about PivotTables, but to give you a basic understanding of what a PivotTable is and how it works. Once you have that, you can start getting useful work out of the beasts almost immediately. Simple curiosity will drive you to learn more as you go.
Conclusion
I encourage you to experiment with PivotTables. Despite the revolutionary new information they can generate for you, they do not alter the data behind them, so you can't ruin your spreadsheets no matter how far you take the rebellion. Besides, fomenting little data insurrections is more like fun than work. And in this particular game, there are no losers.
|
