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.