Analysis of School Assessment Data Using Pivot Charts



Analysis of School Assessment Data

Using Pivot Charts



GaryM.Baker

AssociateProfessor

DepartmentofChemistryandBiochemistry

NorthernIllinoisUniversity

DeKalb,IL60115



Abstract



OneofthemostpowerfulfeaturesofMicrosoftExcelisitsabilitytocreatepivottablesand

charts.Evenso,itremainsoneoftheleastutilizedfeaturesinacademicsettings.Thisposter

illustrateshowpivotcharts canprovideadynamiclookingglassintoassessmentdataon

specialneedsgroups,andadditionallyproposesthattraininginthistechnologyshouldbe

integratedintoteacherpreparationprogramsaspartofaTWSrequirement.

TheStaticAnalyst

AcommonuseofMicrosoftExcel(andotherspreadsheetsoftware)istogeneratestatic(non‐

interactive)chartsfromdatathathasbeenenteredintooneormoreworksheets.For

example,Figure1showsachartofthenumberofgrade3students,organizedbygenderand

subgroup,whichtooktheGates‐MacGinitiereadingachievementtest.Astanine performance

scoreforeachstudent,availableintheworksheet,hasnotbeenexposedinthechart.



Number



Figure1



Inanotherexample,showninFigure2,Gates‐MacGinitierea ding achievementdataforgrade

7students,basedonstaninescores,issummarizedagainstsimilardistrictandstatedata.



Figure2



Creatingachartfromspreadsheetdatatoreportdemographic,performance,andother

studenttrendsiscommonpractice.Thedatainaworksheet(orsetofworksheets)typically

providearichreposito ryofinformation,butthedimensionsthatcanbe(andshouldbe)

chartedoftenneedtobemany.Forexample,aschoolmayneedtolookatperformancein

differents ubjectareas(math,insteadofreading),orindifferentstrandareas(comprehension,

insteadofvocabulary),orindifferentgradelevels.Thenumberofstaticchartsneededto

viewthesemanydimensions canquicklybecomecumbersome.Additionally,manyteacher‐

trainingprogramsoftenrequirepre‐andpost‐assessmentcomparisonsbyteachercandidates

todocumentstude ntlearning.Studentteacherswilltypicallycreatenumerousstaticchartsto

showthis.



CanweChangeourStaticWays?



Oneoftheleast‐usedfeaturesinMicrosoftExcelturnsouttobeoneofitsmostpowerful

features:Pivotcharts.Theconceptistogenerateonechartandthento“pivot”around

differentdatadimensionstoinstantlyupdatethechartwithanewview.Apivotchartgives

youtheabilitytoquicklybrowsemanydifferentgraphicalviewsofyourdata,andtoquickly

respondto“whatif”questionsusingonlyafewsecondsofpoint‐and‐clickordrag‐and‐drop.A

significantadvantageofstaticchartsisthattheyareeasilypublishedtotheweb.Thiscanalso

bedonewithpivotcharts,butitrequirestherightback‐endservices.Forexample,running

ExcelServicesinMicrosoftOfficeSystemSharePointisonewaytodoit.Forpresentationsto

schooladministrators,pivotchartsareeasilydistributedine‐mailorPowerPointcontainers.



Toillustratethepoweroftheconcept,let’sturnthestaticchartinFigure1intoapivotchart

usingthetoolsinExcel2007(Figure3).Anewelementappearsinthespreadsheetview,called

apivottable,withmovabledrop‐downfieldsthatcanbeclickedtofiltertheworksheetdata

(gender,subgroup,andstaninescore)andinstantlyupdatethechart.





Number

Figure3



Forexample,whatifweneedtoviewallgrade3studentsthatdidnotmeetthereading

achievementstandard?Inotherwords,canweviewonlythosestudentsthatscoredstanine



valuesof1,2,or3?Todothis,dropdow nthestaninefield,asshowninFigure4,anddeselect

values4through9.WhentheOKbuttonisclicked,boththetableandchartareinstantly

filteredtogivetheviewshownbelow:



Number



Figure4



ThenewchartshowsthatonlytwoTitlestudentsdidnotmeetthestandard.Whatifwenow

wantedtoviewallstudentsintheTitlesubgroup,brokendownbygenderandstanine?Thisis

atwostepprocess.First,dropdownthesubgroupfield,asshowninFigure5,anddeselect

IEP,RTI,andREG.ClickingtheOKbuttoninstantlygeneratesthefilteredviewshownbelow:



Number

Figure5

Thesecondstepistoaddthestaninescoretothex‐axistoseehowallTitlestudents

performed.Thisiseasilydoneusingasimpledrag‐and‐drop,anotherimportantfeatureof

pivotchartsthatletsyouchangetheaxesfields.Forexample,toaddthestaninescoretothe

x‐axis,simplyusethe“FieldList”(whichisvisibleinExcelwheneverthepivotchartortableis

clicked)anddrag‐and‐drop,asshowninFigure6.



Dragthe

Staninefieldto

theAxisFields

box

Figure6



ThechartisinstantlyupdatedtogivetheviewshownbelowinFigure7.Itisnowclearthat19

outof21Titlestudentsmetthereadingachievementstandardwithstaninescoresof4,5,or

6.NoneoftheTitlestudentsexceededthestandar dwithscoresof7,8,or9.



Number



Figure7



Summary…andaRecipeforChange.



• Creationofstatic(non‐interactivecharts)iscommonpracticeinbothschooldistrictsand

teacher‐trainingprogramstoanalyzeandreportstudentassessmentdata.



• Thepivotcharthasbeenaroundinspreadsheetsoftwareforalongtime.Evenso,it

remainsoneoftheleastutilizedfeaturestoanalyzeandreportstudentassessmentdata.



• Datatrendsarelesslikelytobemissedwhenusingpivotcharts,giventhatdatacanbe

filteredandchartaxesupdate dwithasimplepoint‐and‐clickordrag‐and‐drop.Inaddition,

questionsthatariseduringa presentation(e.g.“Doyouhaveagraphthatshows…”) can

nowbeaccommodated“onthefly”.



Asacase‐in‐point,thisposterpresentationusesfourstatic charts(Figures3,4,5,and7)to

displayReadingachievementdatainaTitlesubgroup. Manymorechartscouldbecreated

tolookatotherdimensions,suchastheRTIsubgroup.Onepivotchart(ratherthanmany

staticcharts)letsyoudynamicallycreatemanydifferentviewsofthedata, greatlyreducing

timespentdiscoveringhiddentrendsoridentifyingbestreportingformats,andto address

“whatif”questionswhenevertheyarise(thedayofapresentation,orseveralmonths

later).



Thefollowingrecommendationsareoffered:

1. Universitiesthathavepartnershipswithlocalschooldistrictsshouldhostworkshopson

pivotchartsthatfocusontheiruseinanalyzingandreportingstudentassessmentdata.

Standardizeddata‐gatheringtemplatesshouldbeprovidedtoworkshopattendeesthat

includeworksheetformatsoptimizedforpivotchartgeneration.Thesetemplates

shouldbedesignedforusebyschoolpersonneltogatherrealstudentdatathatcanbe

analyzedinafollow‐upworkshop.

2. Revisetechnologytraininginteachercertificationprogramstointegratepivotchart

trainingintheanalysisofpre‐andpost‐assessmentdataforwholeclass,subgroups,or

individuals. Suchdataaretypicallygatheredduringstudentteaching,aspartofa

TeacherWorkSample,todocumentimpactonstudentlearning.Suchtrainingcouldbe

implementedpriortothestudentteachingtermaspartofaTWSsimulationproject.



FromtheEducationalMeasurementGroupofPearson

“Astanineisatypeofscaledscoreusedinmanynorm‐referencedstandardizedtests.Thereareninestanineunits(thetermisshort

for"standardnine‐pointscale"),rangingfrom9to1.Typically,staninescoresareinterpretedasaboveaverage(9,8,7),average(6,

5,4),andbelowaverage(3,2,1).Usingonlyninenumbers,staninescoringisusuallyeasiertounderstandthanotherscoring

models.”

“Staninescoresareusefulincomparingastudent'sperformanceacrossdifferentcontentareas.Forexample,a6inMathematics

andan8inReadinggenerallyindicateameaningfuldifferenceinastudent'slearningforthetworespectivecontentareas.While

staninescoresaregoodatsignifyingbroaddifferencesinperformance,theyshouldbeusedcautiouslywhenmakinganyfiner

distinctionsaboutperformance.”