20.2. 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.
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.
|
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.
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.
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. Chapter 13 tells you everything you need to know about data lists.
Select Data PivotTable and PivotChart Report.
The first step of the PivotTable and PivotChart Wizard appears. (Figure 20-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."
Choose PivotTable for the type or report.
You can also create a pivot chart and pivot table in one step by selecting "PivotChart report (with PivotTable report)." The end of this chapter explains pivot charts .
Click Next to continue to step 2.
The second step of the wizard differs slightly depending on the type of data source you chose in step 3. If you selected "Microsoft Office Excel list or database," Excel shows the current selection range, which it will use for the pivot table.
If you chose to retrieve data from an external data source, you need to click the Get Data button to select the data connection you want to use. (You'll only see this button if you choose an external data source in step 3.) For more information, see Chapter 22, which explains how to define a data connection to your favorite database.
If you choose to retrieve data from multiple ranges, Excel prompts you to enter the cell reference for each range.
Click Next to continue to step 3.
In the final step, you can choose a location for the pivot table.
Select "New worksheet" to create a new worksheet for your pivot table, which is usually the easiest .
Pivot tables are fairly complex creations, so the easiest way to manage them (and keep them separate from the rest of your data) is to pop them into a new worksheet. Alternatively, you can choose "Existing worksheet" to insert your pivot table on a worksheet that's already in your workbook. In this case, you need to specify the cell reference for the top-left corner of the pivot table. If there's data under this cell or to the right of it, Excel may overwrite it as it generates the pivot table. Usually, the best approach is to place a new pivot table on a separate worksheet.
The last step of the wizard also gives you the chance to set some miscellaneous options (by clicking the Options button) or start laying out your pivot table (by clicking the Layout button). Stay away from these options for now. You'll find that it's easier to organize a pivot table on your worksheet after you create it.
Click Finish.
Excel inserts the new pivot table. Because you haven't defined the columns it should use for grouping and analyzing rows, this pivot table appears as a group of empty rectangles, as shown in Figure 20-6.
|
To build a pivot table, you simply drag columns from the PivotTable Field List on the right side of the Excel window, and drop them into one of the regions on the pivot table. As you work, Excel generates the pivot table, updating it dynamically as you add, rearrange, or remove columns.
To understand how to fill up a pivot table with data, you need to know how each region works. Altogether, a pivot table includes four regions:
Data Items . These are the fields that you want to subtotal . Usually, this is the numeric information you use to build averages and totals.
Row Fields . The row fields let you break down the data into categories. The pivot table uses each field you drag here to create a separate group and inserts the information for that group into one row. For example, using the product list in Figure 20-4, you could group products into categories by dropping the Category field here.
Column Fields . Often, you want to subdivide your data in more than one way at once. Like row fields, column fields create groups. Excel adds one column for each group, according to the field you choose. For example, if you use Category for a row field and Ship Country for a column field, you'll wind up with a table that divides sales figures into rows by product category, and then divides each category row into columns by country.
Page Fields . Page fields play the same role as filter conditions do in a data list (Section 13.3.4), in that they limit what data the pivot table displays. For example, if you want to look at a breakdown of sales by category, but you only want to consider U.S. sales, you could add Ship Country as a page field and configure it accordingly.
It really doesn't matter whether you use a field for row grouping or column grouping. The pivot table shows the same data either way, but one approach may be more readable than another. For example, if you have a field with extremely long names , it will probably work better as a row field than as a column field (where it would stretch out the width of the whole column).
Also, consider how many different groups you want to create. For example, if you want your pivot table to compare sales by category and country, and your list features 5 categories and 20 countries , you'll probably be best off if you use the country field as a row field and the category field as a column field. That keeps the table long and narrow, which is easier to read and print.
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 20-4.
From the PivotTable Field List, drag the Product field onto the Drop Row Fields Here region.
When you drop the field, Excel fills in the names of all the products from the list from top to bottom, in alphabetical order (see Figure 20-7). When you finish this step, Excel creates one row containing the subtotals for each product.
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.
|
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 dataspecifically, 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.
Click OK.
Excel refreshes the pivot table with the new information, as shown in Figure 20-8.
|
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 20-9 shows a pivot table that simply totals the number of units sold for particular products.
Unlike most other elements in Excel, pivot tables don't refresh themselves automatically. That means that if you change the source data, the pivot table may show out-of-date totals. To correct this problem, you can refresh the pivot table by moving to one of the cells in the pivot table and selecting Data Refresh Data (or by clicking the exclamation mark on the PivotTable toolbar). This action tells Excel to scan the source data and regenerate the pivot table.
|
So far, you've seen how to use a pivot table to quickly build complex summaries. However, pivot tables have another key benefit: flexibility. There's no limit to how many times you can move fields or recalculate your summary so that it performs different calculations.
To change a pivot table, you can use the following techniques:
To remove a field from a pivot table, click the field header (which looks like a gray box containing the name of the field) and drag it back to the PivotTable Field List window. The mouse pointer changes to an X symbol to indicate you're removing the field.
To move a field from one position to another, just drag the field header to the correct region. For example, you can reverse the example shown earlier by dragging the column field (Country) to the row region, and dragging the row field (Product) to the column region.
Figure 20-10 shows one way you could rearrange the pivot table shown in Figure 20-8.
|