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

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!

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.

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.

How to Use Pivot Table Data in Excel Formulas

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

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

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

Advanced Excel Exercises with Solutions PDF