1. No category

Lab 5:
Pivot Tables / Pivot Charts
Simon: Chapters 7 & 8
URBPL 5/6010: Urban Analysis
University of Utah
Pam Perlich
Rev. 09/27/2006
Files for this Lab
CreatePivotTable.xls
AddCalcField.xls
GetPivotTable.xls
Optional : PivotChartDB.mdb
Project 4
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
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
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
Click “Layout”
Double Click Here to
Change Computation
Click “Options”
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
Insert a calculated field
1) Select cell
2) Insert => calculated field
Link Pivot Table Results to
Table
Open GetPivotTable.xls
Specify desired location of value
Insert Function GETPIVOTDATA
Retrieve value from pivot table
1) Insert => Function => GETPIVOTDATA
2)
Specify function arguments
3)
=GETPIVOTDATA(PivotTable!C4,PivotTable!A5)
Pivot Chart
Create with same principles
Capabilities
Change chart type
Change computation (from sum to
average, etc.)
Add and alter fields
Format charts
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
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
Note state control totals and categories
Race and ethnicity are distinct
2,469,585 is 7/1/2005 estimate for Utah
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-EST2005-RACE6.htm
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.
Reformat the Data Set For
Pivot Table Analysis
Change the time labels to match those in
the file layout document (e.g., time =
POPESTIMATE2001 => 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.)
2,469,585 is 7/1/2005
estimate for Utah – It
matches our result.
Why is “grand total” double
this amount?
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.
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/tables/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
Population estimates
Geographic Area
.Utah
April 1, 2000
July 1,
2005
July 1,
2004
July 1,
2003
July 1,
2002
July 1,
2001
July 1,
2000
Estimates
base
Census
2,469,585
2,420,708
2,378,696
2,336,673
2,287,736
2,243,136
2,233,198
2,233,169