Excel Annoyances

PIVOTTABLE ANNOYANCES

LEARN TO USE PIVOTTABLES

The Annoyance:

I’ve heard Excel aces talk about PivotTables as if they were some great gift to spreadsheet users. Then, the owner of a chain of local pet stores asked me in a job interview if I knew how to make and manipulate the darned things. I didn’t know and, rather than lie, I told him no, but that I was willing to learn. He hired me, but if I want to get past my probation period, I’m gonna have to learn how to use them. So, I ask: what the heck is a PivotTable, and how do I create one?

The Fix:

A PivotTable is a dynamic data table (sort of a report, actually) that you can manipulate to emphasize different views of a data list stored in Excel. As an example, consider the worksheet shown in Figure 4-24.

Figure 4-25. This configuration emphasizes days over hours.

This PivotTable shows a sampling of the hourly sales for the departments in your boss’s four pet stores. The rows are grouped by department and then by day, while each column represents an hour of the day. You could produce exactly the same worksheet without using the PivotTable feature, but then you wouldn’t be able to change it without rearranging all the data by hand to produce a different look, such as the one shown in Figure 4-25.

The PivotTable configuration in Figure 4-25 shows each department’s sales, but instead of arranging it by day, it emphasizes sales during specific ...