Chapter 35: Analyzing Data with Pivot Tables

Another Pivot Table Example

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

Figure 35.28 shows part of a table with 3,144 data rows, one for each county in the United States. The fields are

•County: The name of the county

•State Name: The state of the county

•Region: The region (Roman number ranging from I to XII)

•Census 2000: The population of the county, according to the 2000 Census

•Census 1990: The population of the county, according to the 1990 Census

•LandArea: The area, in square miles (excluding water-covered area)

•WaterArea: The area, in square miles, covered by water

On the CD

This workbook, named county data.xlsx, is available on the companion CD-ROM. n

FIGURE 35.28

This table contains data for each county in the United States.

Figure 35.29 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.

739

Part V: Analyzing Data with Excel

FIGURE 35.29

This pivot table was created from the county data.

I created three calculated fields to display additional information:

•Change (displayed as Pop Change): The difference between Census 2000 and Census 1990

•Pct Change (displayed as Pct Pop Change): The population change expressed as a percentage of the 1990 population

•Density (displayed as Pop/Sq Mile): The population per square mile of land

Tip

To view (or document) calculated fields and calculated items in a pivot table, choose PivotTable Tools Options Calculations Fields, Items & Sets List Formulas. Excel inserts a new worksheet with information about your calculated fields and items. Figure 35.30 shows an example. n

740

Chapter 35: Analyzing Data with Pivot Tables

FIGURE 35.30

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 Advanced tab, and click Edit Custom Lists. Click New List, type your list entries, and click Add. Figure 35.31 shows the custom list I created for the region names.

FIGURE 35.31

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

741

Part V: Analyzing Data with Excel

Producing a Report with a Pivot Table

By using a pivot table, you can convert a huge table of data into an attractive printed report. Figure 35.32 shows a small portion of a pivot table that I created from a table that has more than 40,000 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 filename, the file size, and the duration.

FIGURE 35.32

A 132-page pivot table report.

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

742

Chapter 35: Analyzing Data with Pivot Tables

On the CD

This workbook, named music list.xlsx, is available on the companion CD-ROM. n

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

1.I selected a cell in the table and chose Insert Tables PivotTable.

2.In the Create PivotTable dialog box, I clicked OK to accept the default settings.

3.In the new worksheet, I used the PivotTable Field List and dragged the following fields to the Row Labels area: Genre, Artist, and Album.

4.I dragged these fields to the Values area: Track, Size, and Duration.

5.I used the Data Field Settings dialog box to summarize Track as Count, Size as Sum, and Duration as Sum.

6.I wanted the information in the Size column to display in megabytes, so I formatted the column using this custom number format:

###,###, “Mb”;;

7.I wanted the information in the Duration column to display as hours, minutes, and seconds, so I formatted the column using this custom number format:

[h]:mm:ss;;

8.I edited the column headings. For example, I replaced Count of Track with Tracks.

9.I changed the layout to outline format by choosing PivotTable Tools Design Layout Report Layout Show In Outline Form.

10.I turned off the field headers by choosing PivotTable Tools Options Show

Show Field Headers.

11.I turned off the buttons by choosing PivotTable Tools Options Show

+/- Buttons.

12.I displayed a blank row after each artist’s section by choosing PivotTable Tools Design Layout Blank Rows Insert Blank Line after Each Item.

13.I applied a built-in style by choosing PivotTable Tools Design PivotTable

Styles.

14.I increased the font size for the Genre.

15.I went into Page Layout view and adjusted the column widths so that the report would fit horizontally on the page.

Note

Step 14 was actually kind of tricky. I wanted to increase the size of the genre names, but leave the subtotals in the same font size. Therefore, I couldn’t modify the style for the PivotTable Style I chose. I selected the entire column A and pressed Ctrl+G to bring up the Go To dialog box. I clicked Special to display the Go To Special dialog box. Then I selected the Constants option and clicked OK, which selected only the nonempty cells in column A. I then adjusted the font size for the selected cells. n

743