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.

A pivot table example in Excel

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.

A pivot table example in Excel

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!

Tip: You can view (or document) of calculated fields and calculated items in a pivot table. To do so choose PivotTable Tools ➪ Analyze ➪ Calculations ➪   Fields, Items & Sets ➪   List Formulas Excel. This will insert a new worksheet with information about your calculated fields and items. Following two figures show the command and output of selecting this command.
A pivot table example in Excel

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

A pivot table example in Excel

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.

A pivot table example in Excel

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.

A pivot table example in Excel

Click on Edit Custom List. This option is under 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.

A pivot table example in Excel

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 🙂


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply