Life with Pivot Tables
With a pivot table, you can create summary tables like the ones shown in Figure 2 and Figure 3 just by choosing the columns you want to compare. And once you've built your summary, you don't need to stick with it—instead, you can transform a purchase-by-education table into a purchase-by-city table just by dragging and dropping. You can even drill down into the details, apply filter criteria, and apply advanced subtotaling calculations. The following sections describe all of these techniques.
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 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.
Not all data is suited for a pivot table. To work well, your data needs to meet a few criteria:
Figure 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 at least one column that has duplicate values. For example, in the order table shown in Figure 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..
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, and standard deviation, and use your own formulas.
NOTE: It's technically possible to create subtotals without using a numeric column. In this case, the subtotals just count the number of values in the group. This approach is occasionally useful, but it's not as powerful as other types of subtotals.
The order information table is perfect for a pivot table because there are several columns you can use to group the order rows. These include:
Product and Category. Find out how well specific products are selling or what the hottest product categories are.
Customer. Find out who's making the most purchases.
Ship City and Ship Country. Find out where the majority of your customers reside.
Remember, a pivot table can handle all of these comparisons. You don't need to choose one or another before you start building it.
TIP: The best way to learn about pivot tables is to perform the steps in this chapter, and then start experimenting. If you don't happen to have a table with hundreds of records on hand, you can download the workbook shown in Figure 4 from the "Missing CD" page at MissingManuals.com. It gives you 2,155 rows to summarize and pivot to your heart's content.
Preparing a Pivot Table
Creating a new pivot table is a two-step process. First you need to run the PivotTable and PivotChart Wizard, which asks you to identify the data you want to summarize and select the location where you want to place the pivot table. The next step is to actually define the structure of the pivot table and try out different ways of organizing and grouping your data.
The following steps lead you through the first step in creating a new pivot table:
Select the range of cells that you want to use for your pivot table.
If you plan to add more rows later on, it might be worthwhile to use a data list instead. That way, when you add rows to the data list and refresh the pivot table, Excel automatically includes them in the pivot table.
Select Data --> PivotTable and PivotChart Report.
The first step of the PivotTable and PivotChart Wizard appears. (Figure 5 shows all three steps in the pivot table creation process.)
Select the type of data you're using. Usually, this is "Microsoft Office Excel list or database," which corresponds to a range of cells or a data list on the worksheet.
If you want to create a pivot table based on rows from an external database, choose "External data source." You can also create a pivot table that uses multiple separate ranges, which is occasionally useful if you need to combine reports in separate worksheets or workbooks. In this case, select "Multiple consolidation ranges."