A Pivot Table Example in Excel with Real Data

Get FREE Advanced Excel Exercises with Solutions!

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) 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 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.

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 🙂

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF