Software

Demystify Excel's PivotTable feature with this simple method

If you can see your data as groups, values, and filters, you can create a useful pivot table in just a couple of minutes.

Pivot tables have been around for a long time, and yet, they're probably one of Excel's least-used features. The reason is simple, users simply don't know how easy they are to create using this methodology: think groups, values, and filters, in that order. Now, let's work through a simple example, applying that methodology.

Using the data set shown below, let's suppose you want to see which categories have items on order. Furthermore, you want to filter by the products. You could use advanced filters and complex formulas, which would take a bit of time (even for the experts). Or, you can spend a minute or two creating a pivot table.

Before we do anything, let's map the pivot table by groups, values, and filters:

  • We'll group by the Category field because that's the field we're summarizing.
  • Our summarized values are in the Units On Order field.
  • We'll filter the categories by the Product Name field.

In this case, applying the three pivot table elements is pretty easy - and if you employ this strategy to more complex requirements, you'll find the process just as easy. Now that we know the table's structure, let's insert a table, as follows:

  1. Select any cell in the data range.
  2. Click the Insert tab and choose PivotTable from the PivotTable option in the Tables group. In Excel 2003, choose PivotTable and PivotChart Report from the Data menu.
  3. In the resulting dialog box, you can change the data source and specify where to insert the table. In this case, click OK to accept the defaults.  Word 2003 will present a quick wizard for setting these options.

At this point, you've created the table. The resulting sheet contains a template. The pane to the right lists the data range in pivot table terms. At the top, the pane lists the data fields. You'll drag fields to the lists below. The Row Labels and Column Labels lists determine your groups; the Values and Report Filter lists represent your values and filters. Before moving on, let's apply our earlier field analysis to the template:

  • Groups = Category = Column Labels
  • Values = Units On Order = Values
  • Filters = Product Name = Report Filter

Armed with the above breakdown, you can create the pivot table in seconds, as follows:

  1. Drag the Category field to the Row Labels list. (This simple example doesn't have a Column Labels field, but many tables will.)
  2. Drag the Units On Order field to the Values list.

At this point, you have a simple pivot table that sums the units on order by category. It's useful, but you can make it more useful by summarizing actual products. To do that, drag the Product Name field to the Report Filter list. Using the resulting filter, you can view units ordered, by product. For instance, simply choose Chang and click OK to learn that you have 40 units in the Condiments group, on order.

If you don't find that arrangement to be adequate, try again. The following table groups by Product Name and filters by Category. With the Product Name filter set to All, you can quickly determine which canned meat products are on order.

Pivot tables are easy to generate once you relate the data fields to the table elements-that's why defining the table's groups, values, and filters up front helps. Pivot tables are a great interactive tool to add to dashboards because they allow the user to summarize the data in different ways, with almost no effort.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

23 comments
rtroy56
rtroy56

I'd love to comment on the article - if only I could read it in Chrome (latest version).

CharlieSpencer
CharlieSpencer

"Furthermore, you want to filter by the products." I don't understand what you mean by 'filter by product'. Is that like sorting by product? I understand filtering to be a way of specifying specific items that I want to see out of all others; say, not all Condiments, just Aniseed Syrup. I didn't see a mechanism to do this in the example, but I'm sure we're not using the word to mean the same thing. I've long considered 'PivotTable' to be Microsoft's single most poorly chosen name. For years I ignored it, based on my assumption that it 'pivoted the table' (rotated the rows and columns around the diagonal axis). The first time I actually wanted to turn rows into columns and vice versa, I tried it and found that wasn't what it did at all. I was so disappointed when it wasn't what I wanted that I didn't care what it really did, and discarded it in pursuit of a function that would swap columns and rows. (By the way, I never found one, but I don't remember which version I was using. Maybe that feature is in newer versions.) It was several more years before something brought it back to my attention. I'm positive my second look at it was unsuccessful due to the arrangement of data in my table not being compatible with the function, along with a strong helping of ignorance. I hadn't worked up the motivation to take another look at it until this article, but I'm still not clear on what it does or how to do it.

FoxDev
FoxDev

What about 2007 or 2010? There is a drastic difference between 97/2003 and 2007/2010. Today, most people that subscribe to TechRepublic would be using at least a version behind current (2007 instead of 2010). The heading should have read "Demistifying Excel 2003 Pivot Table Creation." I expected 2007. Your description of the process is fragmented and not helpful. When reading these tips & tricks at TechRepublic I expect a certain level of professionalism.

RU7
RU7

When people try to simplify but instead add to confusion. Why say: Groups = Category = Column Labels Then say without further explanation: Drag the Category field to the Row Labels list

zimmerwoman
zimmerwoman

This is a good way to look at it. I still get confused sometimes because of the 1997 help that seems to say that the data you want in columns will be row headers and vice-versa. This is an easier translation.

ssharkins
ssharkins

I'm using Chrome (17.0) and not having a problem. Let us know what version you're using. If there's a problem, we'll definitely want to know about it.

ssharkins
ssharkins

When filtering, you see only those records that fulfill the condition. Sorting, you see everything. The PivotTable feature can save a lot of time, once you learn how to use it.

RU7
RU7

Paste Special Transpose More explicitly (and using the keyboard) Highlight the range to be swapped CTRL+c Click in the cell to start the paste ALT+e s e Enter Tada!

ssharkins
ssharkins

The instructions are for 2010, with additional instructions for 2007 and 2003, as required. Where are you getting lost?

shepmeyster
shepmeyster

It's not real clear, but these are the instructions for 2007 and 2010. There is an additional line with instructions IF you have 2003.

ssharkins
ssharkins

It's not difficult, but if there's a lot of data, it can be a bit overwhelming. I often create a few before I settle on just the right arrangement.

CharlieSpencer
CharlieSpencer

I misread the paragraph where you gave the filtering instructions. Sorry.

CharlieSpencer
CharlieSpencer

I haven't needed it since that one time. Based on it being a 'Paste Special' option, I suspect it didn't exist in whatever version I was using years ago.

ssharkins
ssharkins

All articles on this blog are written in 2010. I include additional instructions for 2007 and 2003, occasionally, if they are so different that they're necessary.

butlerbl1
butlerbl1

Instead of saying "Groups = Category = Column Labels," it would have been clearer if Susan had said "Groups = Category = Row or Column Labels." I was expecting her to drop the data field "Category" into the Column Labels bucket, but it threw me a bit at first when she put this field in the Row Labels bucket instead. Once you understand that either bucket is used to group or aggregate things it's not a biggie, but the way she set it up could have been a bit clearer. I think that is the point that ru7of9 was trying to make. For me the article was good but would have been better if Susan had also included a way to download the actual file she was using so that we could play with it. Pivot tables are a bit esoteric at first, so the hands on really helps newbies. I often find myself just dragging and dropping until I see something I like. Her "group / value / filter" methodology definitely helped me in understanding how to think about using pivot tables before I just dive in.

spofcher
spofcher

It would have been a big help if the chart was included as an Excel spreadsheet.

CharlieSpencer
CharlieSpencer

I'm an idiot. Thanks for reminding me of something that should have been obvious. It's been so long since I installed any of the sample data and templates that I'd forgotten they existed.

Full Tao-er
Full Tao-er

...sample templates are available in Excel. File - New - Sample templates - Sales Reports has data and Pivot Tables already included.

RU7
RU7

The other two are: Groups = Category = Row Labels Drag the Category field to the Row Labels list and Groups = Category = Column Labels Drag the Category field to the Column Labels list

ssharkins
ssharkins

You can apply the breakdown to any data.

CharlieSpencer
CharlieSpencer

I'm not motivated enough to key that data in for demonstration purposes. Those days are long behind me. I would have appreciated more screenshots of the results of each operation, to compare with the final one.