Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

The pivot table example in this section demonstrates some useful ways to work with pivot tables. Figure 18-37 shows 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–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

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

• Water Area: The area, in square miles, covered by water

Figure 18-37: This table contains data for each county in the United States.

Figure 18-38 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 18-38: 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.

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 18-39 shows an example.

Figure 18-39: 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 required 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 18-40 shows the custom list I created for the region names.

Figure 18-40: This custom list ensures that the Region names are sorted correctly.