We are going to work with a pivot table example in this section. We shall learn some useful ways to work with pivot tables.
The following figure shows part of a data table with 3,144 data rows. Each row displays information about a state and its population.
There are 7 fields in this table. The fields are:
- County: The name of the county of United States.
- State Name: The state of the county of United States.
- Region: The region (Roman numeral system, ranging from I to X)
- 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 of the state, in square miles (excluding water-covered area)
- WaterArea: The area, in square miles, covered by water
The following figure shows a pivot table created from the county data table. To create this pivot table, We have placed the Region and State Name fields in the Rows area and placed Census 2000 and Census 1990 in the Values section.
We have created three calculated fields to display additional information in the pivot table:
- Population Change (displayed in the pivot table as Pop Change): The difference between Census 2000 and Census 1990
- Pct Pop Change (displayed as Pct Change): The population change expressed as a percentage of the 1990 population
- Pop/Square Mile (displayed as Pop per Sq Mile): The population per square mile of land
You can sort this pivot table based on two fields. The main sort is by Region, and states within each region are sorted alphabetically. To sort, just select a cell and right-click and choose from the shortcut menu. If you want to sort the Region, click a cell that contains a Region, and if you want to sort states, then select a cell that contains a state.
Sorting by Region sometimes may require some additional effort because Roman numerals are not always in alphabetical order. In my example, I did not have to do this additional effort to sort Region. But you may need. First of all, you have to create a custom list. To create a custom sort list, access the Excel Options dialog box, select the Advanced tab, and then click Edit Custom Lists.
Custom List dialog box will open. Click New List, type your list entries, and click Add. The following figure shows the custom list I created for the region names.
Download Pivot Table Example Data
Happy Excelling 🙂