O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Building Pivot Tables

Now that you've learned the role that pivot tables play in summarizing data, it's time to create your own. Before you begin, you need to have a long list of raw data that you want to summarize. You could use the customer list from the previous example, but it's too small to really demonstrate the benefits of pivot tables. A better example is something like the list of order information shown in Figure 20-4.

Tip

You can also build a pivot table based on records that you select from an external database. But before you take this step, you must configure your database as an Excel data source. To learn how to do so, see Section 22.4.1.

Not all data is suited for a pivot table. To work well, your data needs to meet a few criteria:

  • It must include at least one column that has duplicate values. For example, in the order table shown in Figure 20-4, there are multiple records with the same value in the Customer column. Accordingly, you can create a separate group of ordered items for each customer.

This worksheet shows some entries from a list of 2,155 grocery store items that have been ordered. Lists like this make for great pivot table candidates.

Figure 20-4. This worksheet shows some entries from a list of 2,155 grocery store items that have been ordered. Lists like this make for great pivot table candidates.

  • It must include some numeric information. This is the information you'll use to create subtotals. Often, you'll be interested in generating a simple count, total, or average, although you can also find maximum, minimum, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required