Transcript Pivot Tables / Pivot Charts

Lab 5:
Pivot Tables / Pivot Charts
Simon: Chapters 7 & 8
URBPL 5/6010: Urban Analysis
University of Utah
Pam Perlich
Files for this Lab
CreatePivotTable.xls
AddCalcField.xls
GetPivotTable.xls
PivotChartDB.mdb
Pop_Age_Sex_GOPB.xls
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
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
Change Crosstabulations / Format
Experiment with the features
available
Change chart types
Change data elements
Notice how many of the standard
charting features are available (e.g.,
data tables, chart type, etc.)
Demographic Data – Pivot Tables
Open Pop_Age_Sex_GOPB.xls
Create a pivot table with various
crosstabulation and filtering
Example: School age populations for
a selection of counties in 2050
Explore pivot chart possibilities