A Pivot Table Example in Excel with Real Data

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.

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

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.

This pivot table has been created from the county data.

We have created three calculated fields to display additional information in the pivot table:

1. Population Change (displayed in the pivot table as Pop Change): The difference between Census 2000 and Census 1990
2. Pct Pop Change (displayed as Pct Change): The population change expressed as a percentage of the 1990 population
3. Pop/Square Mile (displayed as Pop per Sq Mile): The population per square mile of land

Read More: How to Insert a Calculated Item into Excel Pivot Table!

List Formulas command under PivotTable Tools ⇒ ANALYZE ⇒Field, Items, & Sets.

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

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.

We have selected a state name and right-clicked. This shortcut menu has appeared. Choose how you want to sort the list.

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.

Click on Edit Custom List. This option is under the Excel Options ⇒ Advanced tab ⇒ Edit Custom List

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.

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

Read More…

How to Use Pivot Table Data in Excel Formulas

Excel Pivot Table Calculated Field (How to Insert & Edit)

Download Pivot Table Example Data

Happy Excelling 🙂