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

1. Lab 5:Pivot 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”

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 => calculated field

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 => Function => 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-EST2005-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.

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

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/tables/NST-EST2005-01.xls