Laying Out a Pivot Table
To get a better understanding of how to create a pivot table, it helps to follow along with a basic example. These steps lead you through the process of creating a summary that compares the products and shipping locations shown in Figure 4.
From the PivotTable Field List, drag the Ship Country field to the Drop Column Fields Here region.
When you drop the field, Excel fills in the names of all the countries from the list from left to right, in alphabetical order. In other words, each country is listed in its own column.
TIP: If dragging and dropping is a little too awkward, there's another way to lay out a pivot table. In the PivotTable Field List window, simply select the field you want to add to the pivot table, and then, in the Add To list underneath, choose where you want to place the field. Then, double-click the field name to add it.
Figure 7. In this example, the list of products has already been added to the row area (column A), and the second grouping criteria (the list of columns for each country) is being dragged onto the chart. Notice that once a field is linked, its name is listed in boldface in the PivotTable Field List.
Now you need to choose what data you want to examine. Drag the Quantity field over the Drop Data Items Here region.
This step is designed to actually fill the table with data—specifically, the numbers of products that were ordered by customers in various countries. It's helpful to remember what's in the table just prior to this step: a list of products in column A and, from column B on, each column is labeled with a different country.
Once you complete this step, Excel generates the pivot table. However, Excel doesn't necessarily perform the calculations you want. In this case, Excel's standard choice is to perform a row count in each cell, which calculates how many order records it found for a given product and shipped to a given country. However, in this case, you actually want to count the total number of units shipped, not just the number of orders. That means that instead of counting the number of matching rows, you want to add the Quantity value for each row in the group. You'll make this change in the next step.
Double-click the Count of Quantity field header, which appears at the top-left corner of the table. In the "Summarize by" list, choose Sum instead of Count.
This option tells Excel to total the Quantity rows instead of counting how many there are.
NOTE: Excel provides support for a wide range of automatic subtotaling calculations. You can use counts, averages, sums, or find the maximum value, minimum value, or standard deviation.
Excel refreshes the pivot table with the new information, as shown in Figure 8.
Figure 8. Top: When you drag a data item onto your pivot table, Excel makes a quick decision about what type of subtotaling it thinks makes most sense. In this example, it chooses to simply count the number of orders for a given product and sent to a given country. In other words, if you look in the Argentina country column and the Boston Crab Meat product row, you'll see that there's one order. Bottom: In this case, it may make more sense to total the quantity of units in every order. Once you make this change—by double-clicking the Count of Quantity field header and, in the "Summarize by" list, choosing Sum instead of Count—you'll see that 20 units of Boston Crab Meat were shipped to Argentina.
NOTE: Pivot tables also calculate row and column subtotals. For example, if you want to find the total number of units shipped for a given product across all countries, scroll to the far right end of the chart. If you want to find the total number of units sold in a given country, scroll to the totals at the bottom of the chart.
This example built a fairly sophisticated two-dimensional pivot table. Most of the pivot tables you'll see in real life are two-dimensional, which means that they compare two different groupings (one represented with rows, and the other represented with columns). Of course, there's no reason you can't create simpler one-dimensional pivot tables. All you need to do is leave out either the column or row fields. For example, Figure 9 shows a pivot table that simply totals the number of units sold for particular products.