Lab 5: Pivot Tables / Pivot Charts Simon: Chapters 7

1
Lab 5Pivot Tables / Pivot ChartsSimon
Chapters 7 8

• URBPL 5/6010 Urban Analysis
• University of Utah
• Pam Perlich
• Rev. 09/27/2006

2
Files for this Lab

• CreatePivotTable.xls
• AddCalcField.xls
• GetPivotTable.xls
• Optional PivotChartDB.mdb
• Project 4

3
Pivot Tables

• Summarize and analyze database files
• Database files
• Record based
• Every column must have a title
• Maximum size 8,000 total items and 256 fields
• Data ? Pivot table
• Cross tabs of data
• Row by Column for Data

4
Create a Pivot Table

• Open CreatePivotTable.xls
• Study the rectangular data set
• Notice the columns with headings
• Select the entire data block
• Data ?PivotTable and PivotChart
• Pivot Table / Chart Wizard appears

5
Pivot Table Wizard

• Data source is an excel database
• Objective is to create a pivot table
• Make sure you have the entire data block selected
  (including headings)
• Step 3
• Put data in a new worksheet
• Click Layout to specify configuration

6
Click Layout
7
(No Transcript)
8
Double Click Here to Change Computation
9
Click Options
10
Insert a Calculated Field

• Open AddCalcField.xls
• Select a specific location (cell) for calculation
  (e.g., F6)
• Insert ?Calculated Field
• Within dialogue box
• Name the field
• Specify the formula
• Insert variable names if necessary
• Notice how Excel automatically adds fields to all
  products and areas

11
Insert a calculated field 1) Select cell 2)
Insert gt calculated field
12
(No Transcript)
13
Link Pivot Table Results to Table

• Open GetPivotTable.xls
• Specify desired location of value
• Insert ?Function ?GETPIVOTDATA

14

• Retrieve value from pivot table
• 1) Insert gt Function gt GETPIVOTDATA
• Specify function arguments
• GETPIVOTDATA(PivotTable!C4,PivotTable!A5)

15
Pivot Chart

• Create with same principles
• Capabilities
• Change chart type
• Change computation (from sum to average, etc.)
• Add and alter fields
• Format charts

16
Pivot Chart from Database(Optional time
permitting)

• Open a new blank excel workbook
• Data?Pivot Chart?External data source
• Dialogue box opens ?Get Data
• MS Access Database
• Navigate to and select PivotChartDB.mdb
• Select and import all fields
• Drag fields onto graph to create crosstabulations

17
Project 4 - Overview

• Review state estimates (from the Bureau of the
  Census) to establish control totals
• Retrieve and review data and documentation
• Prepare data set for pivot work
• Build pivot tables/charts
• Reformat and prepare final presentation
• Interpret results

18
Note state control totals and categories Race
and ethnicity are distinct
2,469,585 is 7/1/2005 estimate for Utah
19
Project 4 Data Retrieval

• http//www.census.gov/popest/estimates.php
  Estimates Data ? Counties ? County estimates by
  demographic category ? Download entire data set ?
  County estimates by demographic characteristics -
  age, sex, race, and Hispanic Origin ? Race and
  Hispanic Origin ? 6 race groups - 5 race alone
  groups and one multiple race group ? State
  datasets ?
• http//www.census.gov/popest/counties/asrh/CC-EST2
  005-RACE6.htm

20
Retrieve and Review Layout and Documentation Files

• CC_EST2005_6RACE_layout.txt
• compraceho.html
• Note The original data set has been reclassified
  to eliminate the Some Other Race category and
  distribute this across all race categories.

21
(No Transcript)
22
Reformat the Data Set For Pivot Table Analysis

• Change the time labels to match those in the file
  layout document (e.g., time POPESTIMATE2001 gt
  July 1, 2001 etc.) Note that there are two
  entries for April 1, 2000 the decennial
  enumeration and an adjusted estimates base. These
  will vary in some cases.
• Use search and replace operations (or some other
  method) to change codes to labels in the sex,
  origin, and race columns (e.g., search and
  replace 0 in the sex column with Total etc.)

23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
2,469,585 is 7/1/2005 estimate for Utah It
matches our result.
Why is grand total double this amount?
27
Data Set Tips

• Note that if you include all race categories and
  Hispanic, not Hispanic, and Total, and male,
  female, and total, that you get a number that is
  MUCH too large.
• Grand total will be 4 times the state total
  population estimate.
• Select only a subset of fields to extract.

28
Error Check Totals

• Make sure you totals and grand totals for all
  counties match the reported state population
  estimates http//www.census.gov/popest/states/tab
  les/NST-EST2005-01.xls

Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005 Table 1 Annual Estimates of the Population for the United States and States, and for Puerto Rico April 1, 2000 to July 1, 2005
Geographic Area Population estimates Population estimates Population estimates Population estimates Population estimates Population estimates April 1, 2000 April 1, 2000
Geographic Area July 1, 2005 July 1, 2004 July 1, 2003 July 1, 2002 July 1, 2001 July 1, 2000 Estimates base Census
.Utah 2,469,585 2,420,708 2,378,696 2,336,673 2,287,736 2,243,136 2,233,198 2,233,169