Pivot Tables for Institutional Researchers

WhatisaPivotTable?

APivotTableisaninteractivetablethatenablesyouto 

groupandsummarizeeitherarangeofdataoranExcel

tableintoaconcise,tabularformatforeasierreporting

andanalysis

PivotTableallowsyouto…

y Turnthesameinformationaroundto examineitfromdifferent

angles,orperspectives

y Quicklysummarizelonglistsofdata

y Calculatesummaryinformationwithoutwritinganyformulasor

copyinganycells

y Createasummaryusingrawcensusdata

y AnswerspecificquestionsaskedbyAdministratorsandFaculty

y Easilyrearr ang e thepivottablesothatitsummarizesthedata

basedongender,agegroupingsor geographiclocationwiththe

dragofamouse

Advantages

y Interactive, Dynamic,Easyto update

y PivotTableswillnotusealotofmemoryfromyourPC

y Informationcanbeupdated eachtimewhena

Workbookisopenand/orby clickingRefresh

y Youcanmovethedataaround,hideanddisplay

differentcategoryfieldsto providealternativeviewsof

thedatawithoutchangingthestructureofyour

originaltableinanywayatall,soyoucandono

harm!

CapturingtheData

y Youcanuseoneofyourreportingtoolstoextractthe

datafromyourLegacysystem,oruseStudent,

Personnel,FacilitiesData files;anylistortableforthat

matter 

y ExportthedataintoanExcelspreadsheet

y Decidewhat informationyouwillneedforthetable:

y Whatfieldstogroupby?

y Whatdataitemtosummarize?

y Whatsummaryfunctiontouse?

Ensurethatyourtablehas…

y DatainaTabularlayout

y Columnlabelsinthefirstrow andthattheyare

meaningful

y No emptyrowsorcolumns

y Onekindofdataineachcolumn;textinonecolumnand

numericvaluesinaseparatecolumn

y Rowsrepresentarecordofrelateddata

y Applyappropriatetypeformattingto yourfields

y Noneofthecolumnnamesdoubleasdataitemsthatwill

beusedasfiltersorquerycriteria,ex.Months,Dates,

namesofLocations…….

CreatingaPivotTable

y ClickintheExceltableorselecttherangeofdatafor thePivotTable

y IntheTablesgroupontheInserttab,clickthePivotTablebutton

y ClicktheSelectatableorrang eoptionbuttonandverifythereferenc e

intheTable/Rangebox

y ClicktheNewWorksheetoptionbuttonorclicktheExistingworksheet

optionbuttonandspecifyacell

y ClicktheOKbutton

y Clickthecheckboxesforthefieldsyouwantto addto thePivotTable

(ordragfieldstotheappropriateboxinthelayoutsection)

y Ifneeded,dragfieldstodifferentboxesinthelayoutsection

ClassicPivotTableView

2007PivotTableView

SwitchingbetweenViews

y MakesuretheactivecellisinsidethePivotTable

y ClickontheoptionstabontheRibbon,thenclickon

theoptionsiconintheActive Fieldgroupofthe

optionstab.

y ThenlaunchthePivotTableoptionsdialogbox. Click

ontheDisplaytab,thenchecktheClassicPivotTable

Layoutbox.(Enablesdraggingoffieldstogrid).

LayoutofPivotTable

y RowArea– datadisplaysvertically,oneuniqueitemperro w .Youcanhave

nestedrows.TypesofdatayouwoulddropintheRowareaincludethosethat

youwantto groupandcategorize–forexample,Gender,Race,Location…..

y ColumnArea– datadisplayshorizontally,Thefieldsyouwanttodisplayas

columnsatthetopofthePivotTable.Oneuniquevaluepercolumn.Youcan

havenestedcolumns.Thetypesofdatayouwoulddrophereincludethoseyou

wantto trendorshowsidebyside–forexample,Month,Periods,Years….

y ReportFilt erArea–Afieldusedto filterthereport by selectingoneormore

items,enablingyouto displayasubsetofdata.Thetypesofdatayouwould

drophereincludethosethatyouwant toisolateandfocuson–forexample,

Employee’s,Classification,Regions…..

y ValuesArea–Thisisthecalculationarea,wherenumericaldataisshownand

summarized.Theitemsyouwoulddropherearethosethatyouwantto

measureorcalculate.Youcoulddropafieldinthevalueareamorethanonce,

butwithdifferentcalculations.Youmightneedminimum,maximum,meanof

salaries…..

LayoutofPivotTablecont…

Values

Area

RowArea

Column

Area

Reportfilter

area

LimitationofPivotTableReports

Catego ry Excel 2003 Excel2007

Row Fields Limitedbyavailable

memory

1,048,576

Column Fields 256 16,384

Pag e Fields 256 16,384

DataFields 256 16,384

Unique Itemsina

SinglePivotField

(couldbelimitedby

availablememory)

32,500 1,048,576

CalculatedItems Limitedbyavailable

memory

Limitedbyavailable

memory

PivotTable reportson

oneworksheet

Limitedbyavailable

memory

Limitedby available

memory

ThenextfewslidesareillustrationsofPivotTablesthatour

officeusedto completeAd‐Hocrequests.

DataRequest

y Howmanystudentsrec eiv edadegreeinArt

duringthepastthreeacademicyear(Fall2006–

Spring2009)?

y WhatwastheaverageGPAperperiod?

y Pleasebreakdowndataby degree.

CompletingtheRequest

1. Groupfieldsby Years(first),Month

(Second)andDegree(Third)

2. IDisthedataitemthatwillbeusedto 

Summarize

3. Subtotalby Month(first)andYear(Second)

4.SummaryFunctionused:Count (ID)and

Average(GPA)

DataList

1. Choosethe

Datathatyou

wantto analyze

2. Choosewhere 

youwantthe

PivotTable

Reportto be

placed

CreatePivotTable

PivotTable

Report 

Area

Layoutof

PivotTable

Columns

inthe

Table

PivotTableLayout

AddingFieldstotheReport

Fields canbeaddedby (1)

placingacheckinthe

checkboxor(2)dragging

thefieldtothedropzone

Note:ReportshouldCount IDinsteadofSum

ChangingtheValueFieldSettings

NewValueforIDfield

OldValue(Sum)

NewValue(Count)

GroupingFields

1. Movefieldto

desiredlocation

(ColumnorRow)

2. Selectfielditem

3. Optionsribbon>

GroupField

4. Filterthe

groupingas

needed

GroupbyMonths

andYears

NewGroupingbyYears

YearsGroup

YearandMonth

ApplyDateFilter

1. Rightclickonfield

2. Filter>DateFilters

Report currentlyshowdatafromFall2003,butthe

requestwouldlikedatafromFall2006.Datefilterwill

beappliedto changetheDate rang e.

ApplyDateFiltercont…

SummarizevaluefieldbyAverage

RoundGPA:

RightClick>Number

Format>Number

ModifiedReportwithDateFilter,

DegreeandAverageGPA

ReportLayout

y ReportLayoutcanbemodifiedwithDesignRibbon

y ReportDesign:

y ReportLayout>ShowinTabularForm

y Subtotals>ShowallSubtotalsatBottomofGroup

y BlankRows>InsertBlankLineafterEachItem

y PivotStyleMedium2

FinalReport

TabularForm

SubtotalsatBottomofGroup

BlankLineafterEachItem

CreatingaPivotChart

y APivotChartisagraphicalrepresentationofthedata

inaPivotTable

y APivotChartallowsyoutointeractivelyadd,remove,

filter,andrefreshdatafieldsinthePivotChartsimilar

to workingwithaPivotTable

y ClickanycellinthePivotTable,then,intheTools

grouponthePivotTableToolsOptionstab,clickthe

PivotChartbutton

y Note:

y Y‐axiscorrespondsto thecolumnarea

y X‐axiscorrespondsto therow area

PivotChart

y InsertRibbon>PivotTable>PivotChart

y PivotChartLayout

PivotChart

ThenextslidewillillustratethepreviousPivotTable

(excludingGPA)withaPivotChart.

y GroupfieldsbyYear,MonthandDegree

y IDisthedataitemthatwillbeusedto Summarize

y SubtotalbyYear

y SummaryFunction:Count (ID)

y Formappearsintabularform

y X‐axis:YearandMonth

ExamplewithPivotChart

PivotChart

Request:

Whatisthepercentagerateofacademicstandingfor

New Freshmen?

y Variables:

y AcademicStandingandID

y SummaryFunction:

y Count(ID),valueisshownaspercentoftotal

PercentageRateofAcademicStandingforNew

Freshmen

PercentageRatewithPivo tChart

PivotTable

PivotChart

Request:

PleasepullarandomselectionofstudentsfromCommunity

Collegesinthesurrounding area.

WhatistheaveragefirsttermGPA(ifavailable)anddid these

studentsgraduate?

y Variables:

y ID,GPA,School,GraduatedIndicator,StartTerm

y Report Filt er:

y Fall2003

y SummaryFunction:

y Count(ID),Percentage(ID),Average(GPA)

CommunityCollegesinthesurroundingareawithstudents

averagefirsttermGPA(ifavailable)andifthesestudents

graduate

NOTE:ValuesareinColumnLabel

Valuesmovedto theRowLabel

Request:

New FreshmenforFall2007andFall2008thatdidnot

returnforthefollowingFall(Retention).

y Variables:

y ID,HSGPA,SATV,SATM,SAT,AcademicStanding,

Term

y SummaryFunction:

y Count(ID),Average(HSGPA,SATV,SATM,SAT)

PivotTable

PivotChart

New FreshmenforFall2007andFall2008thatdidnot

returnforthefollowingFall.

Request:

AbreakdownofthetotalenrollmentforSpringsemester2009by

classification.

Forexample,howmanyfreshman,sophomore,junior,senior,

graduate,otherwereenrolledduringthatsemester. Categories

brokenintofull‐timevs.part‐timestudents.

y Variables:

y Level,Classification,TimeStatus,ID

y Groupby Level,Classification

y Subtotalby Level

y SummaryFunction:

y Count (ID)

TotalEnrollmentforSpring2009

Classic

View

Fall2010CourseListbyDepartment

AcademicDepartmentRequest

ProvidetoAcademic AffairsOfficetheTotal

StudentCreditHoursgeneratedbyLeveland

Campus.

Variables:

y Campus–Ext‐OffCampus–MA‐ MainCampus

y Level– GraduateandUndergraduate

y Enrollment(SUM)

y StudentCreditHours(SUM)

SUMbyCampus,LeveltheEnrollmentand

StudentCreditHours

AcademicDepartmentRequest

ProvidetheTotalStudentCreditHoursand Course 

SectionsGeneratedbyEachAcademicDepartment.

Variables:

y Department

y StudentCreditHours(Sum)

y Sections(Count)

ListbyDepartmentwithTotalStudentCredit

HoursandSections

AcademicDepartmentRequest

ProvidetotheAcademic AffairsOfficethetotalStudent

CreditHoursgeneratedbyDepartmentandFaculty

Member.

Variables:

y Department

y FacultyMember

y StudentCreditHours

BreakdownbyDepartment,Instructor,andTotal

StudentCreditHoursGeneratedbyeachInstructor.

PersonnelDataFile

AcademicDepartmentRequest

ProvidetotheAcademic AffairsOfficethetotalfaculty

inHomeDepartment.

Variables:

y HomeDepartment

y EmployeeID(COUNT)

y Filters ‐ OCAT(JobCategory)=20andFTE=100

Full‐TimeFacultybyHomeDepartment

AcademicDepartmentRequest

ProvidetotheAcademic AffairsOfficetheFull‐Time

FacultySalariesbyHomeDepartment,Total,Average,

MinandMax Salaries.

Variables:

y HomeDepartment

y TotalSalary(SUM,AVERAGE,MIN,ANDMAX)

y Filters –OCAT(JobCategory)=20andFTE=100

Full‐TimeFacultybyDepartmentandSalary

ProvidefortheAcademicResource OfficeaListofSPA

employeesby HomeDepartmentand BudgetCodeto

includetheTotalandAverageSalary

Variables:

y HomeDepartment

y BudgetCode

y TotSal (TotalSalary)

y Filter –Etpye=SPA

ListofSPAEmployeesbyDepartment,BudgetCode,

TotalandAverageSalary

RequestforalistofFacultythatareeligiblefor

PhasedRetirementtosendto Departmentchairs,so

theappropriatefacultycanbenotified.

1. Full‐timetenuredfacultymembers.

2. Participatingfacultymustbeatleast50yearsofage.

3. Ha veatleastfiveyearsoffull‐timeserviceatUNCP.

4.Beeligibleto rec eiveretirement benefitsthrougheithertheTeachers’andState

Employees’Retirement System(“TSERS”)

ortheOptionalRetirementProgram(“ORP”).

Facultymustmeetrequirementsbelow

DatafileforFacultyeligibleforPhasedRetirement

TheNumberEligibleforPhasedRetirement.

5filters

ECLAS=Full‐time

Tenure=‘T’

Age>=50

StateServiceatUNCP>=5

RetirementPlan=‘SRC’,’ORP’

Spring2010StudentDataFile

ProvidefortheSchoolofEducationaCOUNTofall

StudentsEnrolledinanEducationMajorbyCIP,

TeacherCertificationFlagandClassification.

• CIP

• TCHC(TeacherCertificationFlag–YorN)

• CLA(Classification)

• Banid (COUNT)

CountofEducationMajorsbyCIP(13),

TeacherCertificationFlagandClassification

ProvidefortheAdmissionsOfficeabreakdownof 

Student EnrollmentbyClassification,Race,and

Sex.

• Class(Classification)

• Race

• Sex

• Banid (COUNT)

CountofEnrollmentbyClassification,RaceandGender

Building/RoomInventoryData

ProvidefortheFacilitiesOfficeaBreakdownby

BuildingNameandRoomCodeforClassrooms 

andLabsONLY

• BLDGName

• RoomCode(DisplayandCount)

•Filter– RoomCode=110,210,220,250

•Classroom=110

•Labs=210,220,250

CountofRoomsbyBuildingand RoomCode

(ClassroomandLabs)

PivotTableWrap‐Up

y APivotTableallowsyoutocreateanintera ctiveviewofyour

dataset.

y YoucanlookatyourdatathroughaPivotTable,andsee

detailsinyourdata,thatyoumaynothavenoticedbefore.

y Thedatasetdoesnotchange,andisnotconnectedtothe

PivotTable.

y Youcanquicklyandeasilycategorizeyourdataintogroups.

y Summarizelargeamountsofdatainamatterofseconds.

y Youcaninteractivelydraganddropfieldswithinyourreport,

dynamicallychangingyourperspectiveandrecalculatingtotals

tofityourcurrentview.

ContactInformation

GingerBrooks DirectorofReporting

Ginger.brooks@uncp.edu

JeannieDavis ApplicationsAnalyst/Programmer

Jeannie.Davis@uncp.edu

Natasha Burden ApplicationsAnalyst/Programmer

Natasha.Wilson@uncp.edu

UniversityofNorthCarolinaatPembroke

InstitutionalEffectiveness

906DogwoodLane

MagnoliaHouse

PembrokeNC28372

http://www.uncp.edu/ie/resources/ncair2010.pdf

Resources

y Jelen,B.,&Alexander,M. (2007). PivottabledatacrunchingforMicrosoftOfficeExcel

2007. Indianapolis,IN: PearsonEducation,Informit.

y Parsons,J.J.,Oja,D.,Ageloff,R.,&Carey,P. (2008). NewperspectivesonMicrosoft

OfficeExcel2007comprehensive. Boston,MA: ThomsonCourse Technology.