Most pivot tables are created from numeric data, but pivot tables are also useful with some types of non-numeric data. Because you can’t sum non-numbers, this technique involves counting.

Figure 35.1 shows a table and a pivot table generated from the table. The table is a list of 400 employees, along with their location and gender. As you can see, the table has no numeric values, but you can create a useful pivot table that counts the items rather than sums them. The pivot table cross-tabulates the Location field by the Sex field for the 400 employees and shows the count for each combination of location and gender.

Figure 35.1. This table doesn’t have any numeric fields, but you can use it to generate a pivot table, shown next to the table.

Following are the settings I used for this pivot table:

Figure 35.2 shows the pivot table after making some additional changes:

Figure 35.2. The pivot table, after making a few changes.

One of the most useful features of a pivot table is the ability to combine items into groups. You can group items that appear as Row Labels or Column Labels. Excel offers two ways to group items:

A manual grouping example

Figure 35.3 shows the pivot table example from the previous sections, with two groups created from the Row Labels. To create the first group, I held the Ctrl key while I selected Arizona, California, and Washington. Then I right-clicked and chose Group from the shortcut menu. I repeated the operation to create the second group. Then I replaced the default group names (Group 1 and Group 2) with more meaningful names (Eastern Region and Western Region).

Figure 35.3. A pivot table with two groups.

You can create any number of groups and even create groups of groups.

Viewing grouped data

Excel provides a number of options for displaying a pivot table, and you may want to experiment with these options when you use groups. These commands are on the PivotTable Tools Design tab of the Ribbon. There are no rules for these options. The key is to try a few and see which makes your pivot table look the best. In addition, try various PivotTable Styles, with options for banded rows or banded columns. Often, the style that you choose can greatly enhance readability.

Figure 35.4 shows pivot tables using various options for displaying subtotals, grand totals, and styles.

Figure 35.4. Pivot tables with options for subtotals and grand totals.

Automatic grouping examples

When a field contains numbers, dates, or times, Excel can create groups automatically. The two examples in this section demonstrate automatic grouping.

Grouping by date

Figure 35.5 shows a portion of a simple table with two fields: Date and Sales. This table has 730 rows and covers the dates between January 1, 2005, and December 31, 2006. The goal is to summarize the sales information by month.

Figure 35.5. You can use a pivot table to summarize the sales data by month.

On the CD-ROM

A workbook demonstrating how to group pivot table items by date is available on the companion CD-ROM. The file is named sales by date.xlsx.

Figure 35.6 shows part of a pivot table created from the data. The Date field is in the Row Labels section and the Sales field is in the Values section. Not surprisingly, the pivot table looks exactly like the input data because the dates have not been grouped.

Figure 35.6. The pivot table, before grouping by month.

To group the items by month, select any date and choose PivotTable Tools Options Group Group Field (or, right-click and choose Group from the shortcut menu). You see the Grouping dialog box, shown in Figure 35.7.

Figure 35.7. Use the Grouping dialog box to group pivot table items by dates.

In the By list box, select Months and Years and verify that the starting and ending dates are correct. Click OK. The Date items in the pivot table are grouped by years and by months, as shown in Figure 35.8.

Figure 35.8. The pivot table, after grouping by month and year.

Note

If you select only Months in the Grouping list box, months in different years combine together. For example, the January item would display sales for both 2005 and 2006.

Figure 35.9 shows another view of the data, grouped by quarter and by year.

Figure 35.9. This pivot table shows sales by quarter and by year.

Grouping by time

Figure 35.10 shows a set of data in columns A:B. Each row is a reading from an instrument, taken at one-minute intervals throughout an entire day. The table has 1,440 rows, each representing one minute. The pivot table summarizes the data by hour.

Figure 35.10. This pivot table is grouped by Hours.

On the CD-ROM

This workbook, named hourly readings.xlsx, is available on the companion CD-ROM.

Following are the settings I used for this pivot table:

• The values area has three instances of the Reading field. I used the Data Field Setting dialog box (Summarize By tab) to summarize the first instance by Average, the second instance by Min, and the third instance by Max.

• The Time field is in the Row Labels section, and I used the Grouping dialog box to group by Hours.

Excel provides a number of ways to create a frequency distribution (see Chapter 14). But none of these methods is easier than using a pivot table.

Figure 35.11 shows part of a table of 221 students and the test score for each. The goal is to determine how many students are in each 10-point range (1–10, 11–20, and so on).

Figure 35.11. Creating a frequency distribution for these test scores is simple.

The pivot table is simple:

The Grouping dialog box that generated the bins specified that the groups start at 1, end at 100, and are incremented by 10.

Figure 35.12 show the frequency distribution of the test scores, along with a pivot chart. (See “Creating Pivot Charts,” later in this chapter).

Figure 35.12. The pivot table and pivot chart shows the frequency distribution for the test scores.

Perhaps the most confusing aspect of pivot tables is calculated fields versus calculated items. Many pivot table users simply avoid dealing with calculated fields and items. However, these features can be useful, and they really aren’t that complicated once you understand how they work.

First, some basic definitions:

The formulas used to create calculated fields and calculated items aren’t standard Excel formulas. In other words, you don’t enter the formulas into cells. Rather, you enter these formulas in a dialog box, and they’re stored along with the pivot table data.

The examples in this section use the worksheet table shown in Figure 35.13. The table consists of five fields and 48 rows. Each row describes monthly sales information for a particular sales representative. For example, Amy is a sales rep for the North region, and she sold 239 units in January for total sales of $23,040.

Figure 35.13. This data demonstrates calculated fields and calculated items.

Figure 35.14 shows a pivot table created from the data. This pivot table shows Sales (Values area), cross-tabulated by Month (Row Labels) and by SalesRep (Column Labels).

Figure 35.14. This pivot table was created from the sales data.

The examples that follow create

Creating a calculated field

Because a pivot table is a special type of range, you can’t insert new rows or columns within the pivot table, which means that you can’t insert formulas to perform calculations with the data in a pivot table. However, you can create calculated fields for a pivot table. A calculated field consists of a calculation that can involve other fields.

A calculated field is basically a way to display new information in a pivot table. It essentially presents an alternative to creating a new column field in your source data. In many cases, you may find it easier to insert a new column in the source range with a formula that performs the desired calculation. A calculated field is most useful when the data comes from a source that you can’t easily manipulate—such as an external database.

In the sales example, for example, suppose that you want to calculate the average sales amount per unit. You can compute this value by dividing the Sales field by the Units Sold field. The result shows a new field (a calculated field) for the pivot table.

Use the following procedure to create a calculated field that consists of the Sales field divided by the Units Sold field:

1. Select any cell within the pivot table.

2. Choose PivotTable Tools Options Tools Formulas Calculated Field. Excel displays the Insert Calculated Field dialog box.

3. Enter a descriptive name in the Name box and specify the formula in the Formula box (see Figure 35.15). The formula can use worksheet functions and other fields from the data source. For this example, the calculated field name is Avg Unit Price, and the formula is

   =Sales/'Units Sold'

   

   Figure 35.15. The Insert Calculated Field dialog box.

4. Click Add to add this new field.

5. Click OK to close the Insert Calculated Field dialog box.

Note

You can create the formula manually by typing it or by double-clicking items in the Fields list box. Double-clicking an item transfers it to the Formula field. Because the Units Sold field contains a space, Excel adds single quotes around the field name.

After you create the calculated field, Excel adds it to the Values area of the pivot table (and it also appears in the PivotTable Field List). You can treat it just like any other field, with one exception: You can’t move it to the Row Labels, Column Labels, or Report Filter areas. It must remain in the Values area.

Figure 35.16 shows the pivot table after adding the calculated field. The new field displayed Sum of Avg Unit Price, but I changed this label to Avg Price. I also changed the style to display banded columns.

Figure 35.16. This pivot table uses a calculated field.

Tip

The formulas that you develop can also use worksheet functions, but the functions can’t refer to cells or named ranges.

Inserting a calculated item

The preceding section describes how to create a calculated field. Excel also enables you to create a calculated item for a pivot table field. Keep in mind that a calculated field can be an alternative to adding a new field to your data source. A calculated item, on the other hand, is an alternative to adding a new row to the data source—a row that contains a formula that refers to other rows.

In this example, you create four calculated items. Each item represents the commission earned on the quarter’s sales, according to the following schedule:

• Quarter 1: 10% of January, February, and March sales

• Quarter 2: 11% of April, May, and June sales

• Quarter 3: 12% of July, August, and September sales

• Quarter 4: 12.5% of October, November, and December sales

Note

Modifying the source data to obtain this information would require inserting 16 new rows, each with formulas. So, for this example, creating four calculated items may be an easier task.

To create a calculated item to compute the commission for January, February, and March, follow these steps:

1. Move the cell pointer to the Row Labels or Column Labels area of the pivot table and choose PivotTable Tools Options Tools Formulas Calculated Item. Excel displays the Insert Calculated Item dialog box.

2. Enter a name for the new item in the Name box and specify the formula in the Formula box (see Figure 35.17). The formula can use items in other fields, but it can’t use worksheet functions. For this example, the new item is named Qtr1 Commission, and the formula appears as follows:

   =10%*(Jan+Feb+Mar)

   

   Figure 35.17. The Insert Calculated Item dialog box.

3. Click Add.

4. Repeat Steps 2 and 3 to create three additional calculated items:

   • Qtr2 Commission: = 11%*(Apr+May+Jun)

   • Qtr3 Commission: = 12%*(Jul+Aug+Sep)

   • Qtr4 Commission: = 12.5%*(Oct+Nov+Dec)

5. Click OK to close the dialog box.

Note

Note that a calculated item, unlike a calculated field, does not appear in the PivotTable Field List. Only fields appear in the field list.

Caution

If you use a calculated item in your pivot table, you may need to turn off the Grand Total display for columns to avoid double counting. In this example the Grand Total includes the calculated items, so the commission amounts are included with the sales amounts. To turn off Grand Totals, choose PivotTable Tools Design Layout Grand Totals.

After you create the calculated items, they appear in the pivot table. Figure 35.18 shows the pivot table after adding the four calculated items. Notice that the calculated items are added to the end of the Month items. You can rearrange the items by selecting the cell and dragging its border. Another option is to create two groups: One for the sales numbers and one for the commission calculations. Figure 35.19 shows the pivot table after creating the two groups and adding subtotals.

Figure 35.18. This pivot table uses calculated items for quarterly totals.

Figure 35.19. The pivot table, after creating two groups and adding subtotals.

A Reverse Pivot Table

Excel’s Pivot Table feature creates a summary table from a list. But what if you want to perform the opposite operation? Often, you may have a two-way summary table, and it would be convenient if the data were in the form of a list.

In the figure, range A1:E13 contains a summary table with 48 data points. Notice that this summary table is similar to a pivot table. Column G:I shows part of a 48-row table that was derived from the summary table. In other words, every value in the original summary table gets converted to a row, which also contains the region name and month. This type of table is useful because it can be sorted and manipulated in other ways. And, you can create a pivot table from this transformed table.

The companion CD-ROM contains a workbook, reverse pivot.xlsm, which has a macro that will convert any two-way summary table into a three-column normalized table.

In some cases, you may want to create a formula that references one or more cells within a pivot table. Figure 35.20 shows a simple pivot table that displays income and expense information for three years. In this pivot table, the Month field is hidden, so the pivot table shows the year totals.

Figure 35.20. The formulas in column F reference cells in the pivot table.

Column F contains formulas and this column is not part of the pivot table. These formulas calculate the expense-to-income ratio for each year. I created these formulas by pointing to the cells. You may expect to see this formula in cell F5:

=D5/C5

In fact, the formula in cell F5 is

=GETPIVOTDATA("Sum of Expenses",$A$3,"Year",2004)/GETPIVOTDATA("Sum of
Income",$A$3,"Year",2004)

When you use the pointing technique to create a formula that references a cell in a pivot table, Excel replaces those simple cell references with a much more complicated GETPIVOTDATA function. If you type the cell references manually (rather than pointing to them), Excel does not use the GETPIVOTDATA function.

The reason? Using the GETPIVOTDATA function helps ensure that the formula will continue to reference the intended cells if the pivot table changed. Figure 35.21 shows the pivot table after expanding the years to show the month detail. As you can see, the formulas in column F still show the correct result, even though the references cells are in a different location. Had I used simple cell references, the formula would return incorrect results after expanding the years.

Figure 35.21. After expanding the pivot table, formulas that use the GETPIVOTDATA function continue to display the correct result.

A pivot chart is a graphical representation of a data summary displayed in a pivot table. A pivot chart is always based on a pivot table. Although Excel lets you create a pivot table and a pivot chart at the same time, you can’t create a pivot chart without a pivot table.

If you’re familiar with creating charts in Excel, you’ll have no problem creating and customizing pivot charts. All of Excel’s charting features are available in a pivot chart.

Excel provides two ways to create a pivot chart:

A pivot chart example

Figure 35.22 shows part of a table that tracks daily sales by region. The Date field contains dates for the entire year (excluding weekends), the Region field contains the region name (Eastern, Southern, or Western), and the Sales field contains the sales amount.

Figure 35.22. This data will be used to create a pivot chart.

On the CD-ROM

This workbook, named sales by region.xlsx, is available on the companion CD-ROM.

The first step is to create a pivot table to summarize the data. Figure 35.23 shows the pivot table. The Date field is in the Row Labels area, and the daily dates have been grouped into months. The Region field is in the Column Labels area. The Sales field is in the Values area.

Figure 35.23. This pivot table summarizes sales by region and by month.

The pivot table is certainly easier to interpret than the raw data, but the trends would be easier to spot in a chart.

To create a pivot chart, select any cell in the pivot table and choose PivotTable Tools Options Tools Pivot Chart. Excel displays its Create Chart dialog box, from which you can choose a chart type. For this example, select a standard line chart and click OK. Excel creates the pivot chart and also displays the PivotChart Filter pane, shown in Figure 35.24.

Figure 35.24. The pivot chart uses the data displayed in the pivot table.

The chart makes it easy to see an upward sales trend for the Western division, a downward trend for the Southern division, and relatively flat sales for the Eastern division.

When you select a pivot chart, the Ribbon displays a new tab: PivotChart Tools. The commands are virtually identical to those for a standard Excel chart, so you can manipulate the pivot chart any way you like.

If you modify the underlying pivot table, the chart adjusts automatically to display the new summary data. Figure 35.25 shows the pivot chart after I changed the Date grouping to quarters.

Figure 35.25. If you modify the pivot table, the pivot chart is also changed.

The pivot table example in this section demonstrates some useful ways to work with pivot tables.

Figure 35.26 shows a table with 3,144 data rows, one for each county in the U.S. The fields are

Figure 35.26. This table contains data for each county in the U.S.

Figure 35.27 shows a pivot table created from the county data. The pivot table uses the Region and State Name fields for the Row Labels and uses Census 2000 and Census 1990 as the Column Labels.

Figure 35.27. This pivot table was created from the county data.

I created three calculated fields to display additional information:

A new feature in Excel 2007 lets you document your calculated fields and calculated items. Choose PivotTable Tools Options Tools Formulas List Formulas, and Excel inserts a new worksheet with information about your calculated fields and items. Figure 35.28 shows an example.

Figure 35.28. This worksheet lists calculated fields and items for the pivot table.

This pivot table is sorted on two columns. The main sort is by Region, and states within each region are sorted alphabetically. To sort, just select a cell that contains a data point to be included in the sort. Right-click and choose from the shortcut menu.

Sorting by Region requires some additional effort because Roman numerals are not in alphabetical order. Therefore, I had to create a custom list. To create a custom sort list, access the Excel Options dialog box, click the Personalize tab, and click Edit Custom Lists. Click New List, type your list entries, and click Add. Figure 35.29 shows the custom list I created for the region names.

Figure 35.29. This custom list ensures that the Region names are sorted correctly.

By using a pivot table, you can convert a huge table of data into an attractive printed report. Figure 35.30 shows a small portion of a pivot table that I created from a table that has 25,664 rows of data. This data happens to be my digital music collection, and each row contains information about a single music file: The genre, the artist name, the album, the file name, the file size, and the duration.

Figure 35.30. A 119-page pivot table report.

The pivot table report created from this data is 119 pages long, and it took about five minutes to set up (and a little longer to fine-tune it).

Here’s a quick summary of how I created this report: