managers, financiers and other excel...
TRANSCRIPT
Managers,financiersandotherExcelusers:
Doyourdatatablescontaintoomuchinformation?
Canyouseepatternsandtrendsataglance?
Canyoufigureoutthemeaningofthedata?
Wouldyoulikeyourreporttolooklikethis:
Ittakeslessthan10minutes!Apivottableisasimple,yetpowerfultechnique,thatenablesExcel’susersto
transformdataoverloadintomeaningfulandorganizedknowledge.
Withpivottablesyoucan:
Seethedataindozensofdifferentwayswithasimplemousedrag
Performfastcalculationswithnoneedforformulas
Focusonanotherpartofthedataeachtimeandgetaclearpicture
Showmoretrendsandpatterns
Createdozensofreportsandchartstoanalyzeyourdata
TableofContentsIntroductionDisclaimerWhatisaPivotTable?BasicConceptsConditionstoCreateaPivotTable
Necessaryconditions
Desirableconditions
Limitationsofapivottable
CreatingaPivotTableCreatingthetable
SourceofData
Selectingfromadatatableinthecurrentfile
SelectingfromadatatableinanotherExcelfile
DataLocation
Structureofthepivottable
Listoffields
Areas
Filter
MultivaluedReportFilter
Changingthedatasource
Creatingapivottablebasedonadynamicdatatable
Refreshingthepivottable
Refreshingasinglepivottable
Refreshingallthepivottablesinafile
Exercises
PerformingCalculations
ChangingCalculations
Datadisplayoptions
Percentageofacolumntotal
Percentageofarowtotal
Percentageofagrandtotal
Runningtotal
Percentageof
DataGrouping
Groupingnumericdata
DateGrouping
Groupingtextualdata
MultivaluedReport
CalculatedField
Calculationbasedonasinglefield
Calculationbasedonseveralfieldsinapivottable
Deletingacalculatedfield
CalculatedItem
Exercises
ChangingTheReportStructureSwitchingcolumnsandrows
Addingcolumnsorrows
Addingapagebreakbetweenitems
Exercises
FormattingPivotTablesStructureFormat
Subtotals
Sorting
Filtering
Slicers
Creatingfiltersusingslicers:
ManipulatingSlicers
Removingitemswhichweredeletedfromthedatasource
TheTimeline
InsertTimeline:
Changingthetimeperiod:
ChangingtheTimeline’sappearance:
Exercises
DesignTabLayoutcategory
Pivottablestyleoptions
Quickdesignstylesofpivottable
Choosinganewstyle
Exercises
ChartsAddingacharttoanexistingpivottable
Simultaneouslycreatingapivottableandachart
Exercises
TipsandTricksUsingRecommendedPivotTables
Quicklycreatingapivottable
Obtainingthedatasource
CreatingTabsfromFilters
FilterLocations
GetPivotData
Sortingthefieldlist
Repeatingtheitemlabels
AdvancedUsesofPivotTablesFindinguniquerecordsandduplicaterecords
HumanResourcesPlanning
CashFlow
ProfitandLoss
Appendix
Selectingdatafromotherfiletypes
PivotTablesWizard
MultipleConsolidationRanges
TheDataModelBasictermsindatabases
Relationships
TypesofRelationship
Primarykey
Foreignkey
Creatingapivottablebasedontwoormoretables
Creatingthepivottable
CreatingRelationships
Formingarelationship(OnetoMany):
Formingarelationship(OnetoOne):
CreatingtheRelationship
Draggingthefields
DistinctCountFunctions
Thedatamodellimitations
Grouping
Calculatedfieldsandcalculateditems
Refresh
Displayingthedatasource(DrillDown)
Epilogue
IntroductionThisbookteachesexperiencedExcelusershowtousepivottables,oneofMicrosoftExcel’smostpowerfultools,forquickandefficientdataanalysisandtominimizeformulaeusage.
ThisbookwaswrittenforExcel2013users.However,sincethedifferencesbetweenExcel2013andExcel2010orExcel2007arenegligibleandmainlymanifestedbythedifferentgroupsortabsintheribbon,usersoftheolderversionscanusethisbookaswell.
Wewouldliketothankallofthosewhotookthetimetoreadthebook’sdraft,examiningitsreadabilityandthereforeensuringthatitoffersnewpossibilitiestoExcelusers.
ThemaindifferencebetweenpivottablesinExcel2013andtheolderversionsconcernsthe“DataModel”whichwillbedealtwithintheappendix.
DisclaimerThisbookwaswrittenspecificallyforMicrosoftExceluserswhoseektoextendandenhancetheirabilitytoanalyzedatafromvarioussources.
Manyeffortsweremadetowriteacompleteandreliablebookaboutpivottables.However,theauthorsarenotresponsibleforanyconsequences,lossordamagetoanyindividualororganizationwhichmayresultbyusingtheinformationprovidedinthisbook.
Werecommendbackingupyourdatabeforeanychangesarecarriedoutusingthisbook.
WhatisaPivotTable?Databasescontainrawdataonvarioustopics,andareusuallyarrangedinatabularform.Inmanycases,dataoverloadmaymakeitdifficulttousetheinformationandconvertitintorelevantknowledge.
Apivottableisasimple,yetpowerful,techniquewhichenablesExceluserstoturnthedataoverloadintowell-organizedandmeaningfulknowledge.
Byusingapivottable,userscanperformvariouscalculationsontheirdata,suchascalculatingtheaverage,counting,findingtheminimumandthemaximumvaluesandsoon.
Furthermore,thepivottableenablesustofilterandsortthedataeasilyandquickly.
Usersmayfocusonsomeorallpartsofthedata,evenwhenthedatatablesarehuge(somedatabasesmaycontainamillionormorerecords);thususerscanobtaintheirdesireddataclearlyandconcisely.
Asingledatatablecanbeusedtocreatedozensofreportsandchartsforanalyzingthedata,withmanycross-sections,simplybydraggingfieldstotheappropriatelocations.
Thus,thepivottableenablesustobetterunderstandprocessesandtrends.Itisalsoausefultoolfordecisionmaking.
ThepivottabledatacanbebasedonanexistingExcelfileoronotherdatabases(i.e.AccessoranSQL-baseddatabase).
Sinceapictureisworthathousandwords,herearesomeexamplesofpivottables,derivedfromthesamedatabase,whichshowthedetailsoffactoryemployees:
Thefollowingpivottableswerederivedfromthedatabaseabove:
Numberofemployeesineachdepartment:
Distributionofgendersineachdepartment:
Averagesalaryineachdepartment:
Averagesalaryineachsection,byrole:
BasicConceptsThischapterpresentsbasicconceptsrelatingtopivottables.Whilestudyingandpracticing,thefollowingconceptswillbecomeclearer:
DataTable:Arawdataset,arrangedinatable.Thiscanbeusedasthesourceofapivottable.
PivotTable:Atablethatdisplaysdataindifferentintersections,asdescribedinthisbook.
Column:Averticalsectionofthetableconsistingofdataofthesametype,i.e.firstname,ID,cityetc.
Field:Thecolumn’sheaderiscalleda“field.“
Cell:Thecellistheintersectionofarowandacolumn,andcontainsthedataofthetable.
Item:Thedatainacell.Forexample,NewYorkandDetroitareitemsinthe“City”field.
Record:Adatacollectionwhichappearsinonerowandbelongstothesameentity,e.g.allthetabledatawhichdisplaysinformationregardingoneperson:
NameSt.andNo.
City
JohnSmith
1Lexington
NewYork
ConditionstoCreateaPivotTableNecessaryconditions
Eachcolumnmusthaveatitle.
Thetitleshouldbewritteninasinglerow.
Inacolumn,alltheitemsshouldbeofthesamedatatype(numbers,datesorstrings).
Thedatatableshouldnotcontainanymergedcells.
Thedatatableshouldnotcontainsubtotalsorgrandtotals.
Emptyrowsorcolumnsshouldnotremainwithinthetable(ifanemptyroworcolumnremains,Excelwilltreatthetableastwodifferentones).
Aftercreatingapivottable,donotchangethetitlesofthefields,otherwisethepivottablevalueswillbedeleted.
Desirableconditions
Uniquenamesforeachcolumn(whentwofieldsaregiventhesamename,thetitleofthesecondfieldwillbeappendedatendwith2,i.e.“salary2”).
Completedataforallrecords(whendataismissing,thecalculationswillonlybeappliedtotheavailablerecords.Thiscanbeobservedinthecasesofcalculationssuchasaverages,etc).
Limitationsofapivottable
Numberofpivottablereportsintheworksheet:Limitedbytheavailablememory
Uniqueitemsforeachfield:ý1,048,576
Rowfieldsorcolumnfieldsinthepivottablereport:Limitedbytheavailablememory
Reportfilterinthepivottablereport:256(Maybelimitedbytheavailablememory)
Valuefieldsinthepivottablereport:256
Formulasforcalculateditemsinthepivottablereport:Limitedbytheavailablememory
Pleasenote:
Duetothelimitationsofthepivottable,anddependingonyourpersonalcomputerdata,youmayprefertosavetheexercisesappearinginthisbookinaseparatefileorworksheetforeachchapter.
CreatingaPivotTableCreatingapivottableisafastprocedure,consistingofthreesimplestages:
1.Selectingthedatasourceandtablelocation.
2.Dragginginthedesirablefields.
3.Usingthetoolsintheribbonforcalculationsandformatting(mostofthemcanbeoperatedbyright-clickingthecorrespondingareainthepivottable).
Creatingthetable
1.Placethecursorinacellwithinthedatatable.
2.IntheINSERTtab,clickonthe“PivotTable”button:
3.Thefollowingwindowwillappear:
4.Inthiswindow,select:
Thedatasourcetobeanalyzed(seethe“SourceofData”sectionbelow).
Thedesiredlocationofthepivottablereport(seethe“DataLocation”section).
5.Click
SourceofData
Thedatasourceforthepivottablecanbe:
ThecurrentExcelfile
AnotherExcelfile
Otherfiletypes,suchas:
oAccess
oSQLdatabase
Selectingfromadatatableinthecurrentfile1.OnceyouhavepressedINSERT PivotTable,theentiretablewillbe
selectedautomatically.
2.Therangecanbemodifiedbydraggingandselectinganotherrange.
SelectingfromadatatableinanotherExcelfile1.Beforecreatingapivottable,makesurethatthefilethatcontainsthe
datatableisopen.
2.UsingtheWindowstaskbar,selectthedatafile.
3.Selectthedesiredrange.
Toreadaboutcreatingpivottablesfromotherdatatypes,seehere.
DataLocation
Thereportcanbeplacedinthesameworksheetasthedatatable,orinanotherworksheet.
Pleasenotethatifyouchoosetoplacethepivottableonthesamesheetasthedatatable,youwillnotbeabletodeleteanentirerow,andthefollowingmessagewillappear:
Structureofthepivottable
Afterselectingthedatasourceandthelocation,anemptypivottablewillappearintheworksheet.
Nowyoucandragtheappropriatefieldsin,andperformthedesiredcalculations,asshowninthefollowingfigure:
Thescreenisdividedintothreeareas:
Thelistoffields
Thepivottableareas
Thepivottable
ListoffieldsThelistoffieldscontainsthecolumntitlesoftheselectedrange.
Tip:
§Itispreferabletogivethefieldsshortnames,sincetheywillappearastitlesinthepivottable.
§Avoidusingthewords“sum”,“average”,“minimum”,or“maximum”inthe
titles,sincetheyareaddedautomaticallywhencalculationsareperformed.Thiswillpreventtitleslike“SumofSumofSalaries”fromappearing.
AreasThepivottableisdividedintofourareasthatthedesiredfieldscanbedraggedinto:
COLUMNS-wherefieldstobeshownincolumnsaredragged.
ROWS-wherefieldstobeshowninrowsaredragged.
VALUES-wherefieldsonwhichcalculationsaretobeperformed(average,minimum,maximum,count,standarddeviation,etc.)aredragged.
FILTERS–wherefieldstobeusedasafilteraredragged.
TheCOLUMNSandROWSformthepivotsofthepivottable,asshowninthenextfigure:
Inthefigureabove,theDepartmentappearsintheROWS,andtheGenderappearsintheCOLUMNS(nocalculationisperformedatthisstage).
Valuesaredatawhichenablecalculationsandaredraggedintothe“VALUES”region,asinthefollowingexample:
Inthisexample,the“MonthlySalary”fieldisdraggedintoVALUES.
Thepivottablenowdisplaysthesalariespaidineachdepartment(thedatawasformattedwithnodecimalplacesandwithathousandthsseparator).
Notethatbydefault,Excelsummarizesnumericdataandcountstextualanddatedatatypes.
Animportantnote:
DraggingthefieldstotheROWSorCOLUMNSofthepivottable,createsthetitle“RowLabels”or“ColumnLabels”,respectively,asshowninthefollowingfigure:
Todisplaytheactualfieldnames(andnotthetitle“RowLabels”or“ColumnLabels”),followthesesteps:
1.Clickthe“ANALYZE”Tab.
2.Click“Options“:
3.Inthenewwindow,selectthe“Display”tab:
4.Checkthe“ClassicPivotTablelayout”option.
5.Thefieldnamewillnowappearinsteadof“rowlabel”:
Pleasenotethatcheckingthe“ClassicPivotTableLayout”optionenablesdirectdraggingoffieldsintothepivottableitself,orintothedesiredareaatthebottomofthescreen,asshowninthefollowingfigure:
Inthisexample,the“City”fieldisdraggedintotheROWS.
Thecitynameslistedunderthe“City”fieldappearinthepivottable,andeachitemappearsinadifferentrow.
OtherfieldscanbedraggedintotheROWSaswell(andalsointotheCOLUMNS).Inthefollowingexample,the“Gender”fieldwasdraggedunderthefield“City.“
Citynameslistedunderthefield“City”areshowninthepivottable,andeach“City”fieldisdistributedbygender.
Filter
Aswehavelearnedbefore,thefieldsthatformthepivotsofthedataneedtobeplacedintheROWSorCOLUMNS.Excelalsoallowsustousethosefieldsasanadditionalfilterforthepivottable.
Intheexampleabove,the“Section”fieldwasdraggedintotheFILTERS.
Nowwearefreetodisplayanyoftherelevantdatainthepivottable:
Intheexampleabove,wesoughttoshowthenumberofemployeesineachcityand,usingtheFILTER,wereducedtheinformationtodisplaythemanagementemployeesonly.
Pleasenotethattheiconnexttothefilterchangesto ,toindicatethatthedatadisplayedinthetableisfiltered.
MultivaluedReportFilterBydefault,onlyoneitemcanbeselectedusingthefilter.
Toselectmultipleitems,checkthe“SelectMultipleItems”box.
Wecannowselectmultipleitemstobedisplayedinthepivottable.
Changingthedatasource
Aftercreatingthepivottable,theoriginaldatarangemaychange.
Toapplythenewrangetothepivottable,select“PIVOTTABLETOOLS”tab”ANALYZE”tab “ChangeDataSource”.
Thefollowingwindowforselectinganewdatarangewillappear:
Selectthedesiredrangeandclick
CreatingapivottablebasedonadynamicdatatableIfthedatarangethatthepivottableisbasedonmightchangeinsize,Itisrecommendedtobasethepivottableonadynamictable:
1.Placethecursoronthedatatable.
2.Select“INSERT” “Table“.
Thedatatableisnowchangedintoadynamictable:
Thepivottablebasedonthisdynamicdatatablewillbeupdatedwheneverthedynamictablechangesinsize(youmayneedtorefreshthepivottable).
Refreshingthepivottable
Afteraddingorupdatingthedatatable(e.g.changingthedataoraddingnewrecords),thepivottablebasedonithastoberefreshed,sinceitisnotupdatedautomatically.
Youcanrefreshasinglepivottableorallthepivottablesintheworkbook.
Refreshingasinglepivottable1.Placethecursoronthepivottableyouwanttorefresh.
2.Selectthe“ANALYZE”tabfrom“PIVOTTABLETOOLS”tab.
3.Click“Refresh“.
Refreshingallthepivottablesinafile1.Placethecursoronanypivottable.
2.The“PIVOTTABLETOOLS”tabwillappear.
3.Selectthe“ANALYZE”tab.
4.Selectthearrowunderthe“Refresh”icon.
5.Select“RefreshAll”.
Alternatively,inordertoskiptheabovesteps,youmayaddthe“RefreshAll”icontothequickaccesstoolbar.
Exercises
Forpractice,usethe“cars.xlsx”file.
Youcandownloaditfromhere
Pleasenotethatyoucandraganyofthefieldstoanyoftheareas.Theresultinglayoutswilldisplaythecorrectdata,buttheymightbedifficulttoreadandanalyze.Itisthereforerecommendedto“play”withthefieldlocations,tofigureoutwhichofthelayouttypesistheeasiesttounderstand.
Creatingpivottablereportswithacolumnandarow
1.Displaythenumberofcarsofeachmake(thenumberofcarswillbedisplayedbycountingthelicensenumbers).
2.Displaythenumberofcarsofeachmake,bycolor.
3.Displaythenumberofcarsofeachmake,bycolorandyearofmanufacture.
4.Displaythenumberofcarsofeachmake,bycolor,yearofmanufactureandgeartype.
Creatingpivottablereportswithareportfilter
1.Displaytheaveragesalesprice,bygeartype.Addafilterwhichcontainsthe“CountryofManufacture”field.
2.Inthefiltercreatedabove,displayonlythecarsmanufacturedintheUSA.
3.Inthefiltercreatedabove,displayonlythecarsmanufacturedinEuropeancountries(remembertocheckthe“SelectMultipleItems”box).
4.Displaythedesirableaveragepricebyyearofmanufacture,when“TypeofGear”isusedasareportfilter.
Refreshingthepivottabledata
1.Displayamaximumrequestedsalepricebymodel.
2.Changethepriceofthefirstcarintheworksheetto50,000USD.Hasanychangeoccurredinthepivottable?(Ifnot,didyouremembertorefresh?)
PerformingCalculationsChangingCalculations
Aswehavelearnedbefore,apivottablesummarizesnumericdatabydefault,andcountstextualdataanddates.
However,youmaychangethetypeofcalculation,accordingtoyourneeds.
Lookingatthisexample,wecanseethatthepivottablesummedupthesalariesoftheemployeesineachdepartment.Althoughsuchacalculationiseffectivewhenthedesireistoplanthecompanybudgetforthenextyear,wemayalsowanttocomparedifferentdepartments.Inordertodothat,weneedtofindtheaveragesalaryofeachdepartment.
Tochangethecalculationtype:
Placethecursoronanycellofthe“MonthlySalary”column.
Right-clickandselect“SummarizeValuesBy”.
Selectthedesiredcalculationtype,asshowninthefollowingimage:
Thefollowingpivottablewillappear:
Pleasenotethattherowatthebottomisalwayslabeledas“GrandTotal”,nomatterwhatcalculationtypeisbeingusedforvalues.
Datadisplayoptions
Inadditiontothebasiccalculations,Excelenablesyoutodisplaydatainadvancedforms,suchasdistribution,runningtotalandmore.
Todisplaythedata,simplyright-clickanitemintherelevantfieldofthepivottableitself.
Thefollowingwindowwillappear,enablingyoutochoosebetweenvariousoptions:
Percentageofacolumntotal
Thisshowsthedistributionofeachitemoutofthetotalcolumn:
Fromherewecanlearn,forexample,thatthesalaryintheEngravingDepartment
constitutes36.66%ofthetotalsalariesofthefactoryemployees,whilethesalaryintheDivisionHeadquartersisonly0.57%.
Percentageofarowtotal
Thisdisplaysthedistributionofanitemoutoftheentirerow.
Thefollowingexamplesillustratethedifferencebetweenthepercentageofacolumntotalandthepercentageofarowtotal.
Wehavecreatedapivottablewhichdisplaysthenumberofemployeesineachrole,dividedbygender:
Asthetableshows,amongtheemployeesthereare106saleswomenand97salesmen.Itisalsoevidentthatthefactoryemploys9womenand2meninmarketing.
Butwhatifweneedtoknowwhatpercentageofmeninthefactoryaresalesmen?Orwhatpercentageofthewomenaresaleswomen?
Wehavetodisplaythedataasapercentageofacolumntotal:
Wecannowseethatsaleswomenconstitute21.33%ofthetotalnumberofwomenandsalesmenare19.28%ofthetotalnumberofmen.
Wemayaskanotherquestion:amongsalespeople,whatisthepercentageofmenandwhatisthepercentageofwomen?
Forthispurpose,wepresentthedataasa“percentageofacolumntotal”
Wecanseethatamongallsalespeople,womenconstitute52.22%andmenconstitutetheremaining47.78%.
Percentageofagrandtotal
Thisdisplaysthedistributionoutoftheentiredata.
Inthefollowingexample,wecanseethepercentagesofmenandwomen,dividedbyrole,outoftheentireworkforce.
Wecansee,forexample,thatoutofthetotalnumberoffactoryemployees,saleswomenconstitute10.60%,whilesalesmenconstitute9.70%.
RunningtotalPivottablesallowustocalculatetherunningtotalofthedatatable.
Inthenexttable,wecanseethebusinesscashflow,includingrevenuesandexpenses(includingfuturerevenuesandexpenses)ondifferentdates.
Pleasenotethatthereisnoneedtosortthetablebydates.
Dragthefieldstotheproperareas,asshownbelow:
Thefollowingpivottableappears:
Pleasenote:atthisstage,thetwosumcolumnsareidentical.Wenowwanttoturnthesecondsumcolumnintoarunningtotal.
Right-clickaniteminthesecondsumcolumn.Select“ShowValuesAs“,andthenselect“RunningTotalIn”.
Selectthefieldthatwillbeusedasacumulativebasis(thedatefield):
Thefollowingpivottablewillappear:
Thistableshows,forexample,thaton02/09/2012,anamountof329USDwasobtainedandtheaccruedbalanceforthatdateis(-8022)USD.
PercentageofOneofthemoreinterestingoptionswehaveistodisplaythedatacomparedtoothergivendata.Wehavecreatedapivottablethatdisplaystheaveragesalaries,sortedbycity.
Wedraggedthesalaryfieldtothevaluesareatwice,andchangedthecalculationtoaverage.
NowwewanttoseetheaveragemonthlysalarycomparedtoNewJersey.
Inthesecondsalaryfield,select“%of.“
Inthenewwindow,weareaskedtoselectthebasefield(theCityfield)andtheiteminthefield(NewJersey):
Thefollowingpivottableiscreated:
WecanseethatthecityNewJerseyconstitutesthebasis,andthusisdisplayedas100%,whileothercitiesarepresentedincomparisontoit.
Youcansee,forexample,thattheaveragesalaryinDetroitis99.41%oftheaveragesalaryinNewJersey(i.e.,about0.6%lower),whiletheaveragesalaryinMiamiisabout3%higher.
DataGrouping
Apivottableenablesustogroupdatainvariousways.
Wemaygroup:
Numericdata,e.g.groupingheightdataatintervalsof2inches
Dates,e.g.displayingthedatesofbirth,groupedbymonths
Textualdata
GroupingnumericdataInthefollowingexample,wehavecreatedapivottablethatshowsthenumberofpeoplewhoearnacertainsalary:
Usingtheaboveexample,wecanseethat3employeesearnamonthlysalaryof1,651USDwhileotheremployeesearngreatersalaries.
Althoughthedataiscorrect,itisactuallymeaninglessuntilwegroupit:
1.Placethecursoronthepivottable,inoneofthecellsthatcontainsthesalarydata.
2.Underthe“ANALYZE”tab,select“GroupField“,orsimplyright-clickaniteminthesalaryfield,andselect“Group“:
3.Thefollowingwindowwillappear:
Bydefault,thelowestvalueisdisplayedasthe“Startingat”andthehighestvalueisdisplayedasthe“Endingat”value,butwecanchangethevaluesaswewish,asshowninthefollowingwindow:
4.Selecttheunitsorintervalsbywhichyouwishtogroupthedata(inthiscase,weselectedgroupingby1000USDintervals).
5.Click
6.Thepivottablewilllookliketheonebelow:
Wecannowsee,forexample,that27employeesearnmonthlysalariesrangingfrom2000to2999USD.
DateGroupingInthefollowingexample,wesoughttoexaminethenumberofpeople(identifiedbytheirID)hiredtoworkoneachdate.
Thefollowingtableappears:
Tofacilitateourunderstanding,weneedtogroupthedatasowecanseethenumberofpeoplehiredeachmonth:
1.Placethecursoronthepivottableinoneofthecellsthatcontainsadate.
2.Underthe”ANALYZE”tab,select“GroupField”(youcanalsorightclickonaniteminthedatesfield Group):
3.Thefollowingwindowwillappear:
4.Selectthedesiredoptionsforgrouping:
Pleasenotethatyoushouldselectnotonlymonths,butalsoyears;otherwise,thereportwillshowthenumberofpeoplehiredtoworkeachmonth,regardlessoftheyearofacceptance.Forexample,employeeshiredinJanuary2001willappearinthesamerowasemployeeshiredinJanuary2002.
GroupingtextualdataAsseenabove,itiseasytogroupnumericdataordates.However,itfrequently
becomesnecessarytogrouptextualdata.
Thetextgroupingisdonemanually,asexplainedbelow:
1.Inthepivottableselectthedatayouwouldliketogroup.Inthisexample,wewanttocreateagroupofmanagers,soweselectthemanagerandthedepartmentmanager:
Note:sequentialdatacanbeselectedbydragging.Non-sequentialdatacanbeselectedbyusingtheCtrlkey.
2.Inthe“ANALYZE”tab,under“PIVOTTABLETOOLS“,select“GroupSelection”:
3.Anewgroupappears,whichcontainsthetwogroupsselectedbefore:
4.Ifwechoosetopresentthesubtotalsaswell,wecanseethatthetwogroupsaremergedintoone:
MultivaluedReport
Everynowandthenweneedtoperformalargenumberofcalculationsforthesamefield,e.g.calculationofminimum,maximumandaveragesalary.
WecandragthesalaryfieldontotheVALUESareaseveraltimes,andchangethecalculationtypeforeachone.
Inthefollowingexample,wedraggedthesalaryfieldintotheVALUESareafourtimes:
Thefollowingpivottableappeared:
Now,allthatremainsistochangethecalculationforeachcolumn,aswelearnedinthe“ChangingCalculations”section.Thiscreatesthefollowingpivottable:
CalculatedField
Acalculatedfieldenablesustoperformcalculationsbetweendifferentfieldsofthepivottable,orbetweenafieldandaconstant.
CalculationbasedonasinglefieldOneexampleofthisisataxcalculation.Wecouldaddacolumnthatcalculatesthetaxinthedatatableitself,orwecanadditdirectlytothepivottable.Thisformofcalculationisespeciallyeffectivewhenusingdatafromexternaltables(forexample–AccessorSQL)andnotfromourcurrentExcelfile.
Italsosavesmemory,sincethecalculationisperformedongroupeddata,andnotforeachrecordinthedatabase.
1.Placethecursoronthepivottable.
2.Selectthe“ANALYZE”tab.
3.Select“Fields,ItemsandSets”.
4.Select“CalculatedField”:
Thefollowingwindowwillappear:
4.1.Namethefield.
4.2.Selectthefieldinwhichthecalculationwillbeperformed.
4.3.Clickthe button.
4.4.Continuetocreatetheformula:
4.5.Click
Thepivottablewilllooklikethis:
CalculationbasedonseveralfieldsinapivottableWemaysometimesneedtoperformacalculationbasedonseveralfieldsinapivottable.
Thefollowingexampleisinventorymanagement,basedonatablecontainingdataaboutthenumbersofingoingandoutgoingproducts:
WewanttocreateareportthatwilldisplaythedifferencebetweentheincomingandtheoutgoinginventoryforeachSKU,acrossalldates.
ThefirststepistocreateapivottablethatcontainsthedataofincomingandoutgoinginventoryforeachSKU:
Tocreatethecalculatedfield:
1.Placethecursoronthepivottable.
2.Selectthe“ANALYZE”tab.
3.Select“Fields,ItemsandSets”.
4.Select“CalculatedField”:
Thefollowingwindowwillappear:
4.1.Namethefield.
4.2.Selectthefieldinvolvedinthecalculation.
4.3.Clickthe button.
4.4.Typethearithmeticoperations(add/multiplyetc.).
4.5.Selectthesecondrelevantfield.
4.6.Clickthe button.
4.7.PressOK.
5.Thefollowingtablewillappear:
DeletingacalculatedfieldTodeleteacalculatedfield,selectitfromthedrop-downlistandclick“Delete”
Pleasenotethatthecalculatedfieldshouldnotbedirectlyremovedfromthepivottableareas!
CalculatedItem
“CalculatedItem”enablesustoperformcalculationswithinafield(asopposedto“Calculatedfield”,whichenablesustoperformcalculationsbetweenfields).Inotherwords,thecalculationisperformedbetweenthedifferentitemsofthefield.
Asanexample,wecanusetheinventorytable.Rememberthatthetablehadtwocolumnsrepresentingtheinventory;onecolumnforincominginventoryandanothercolumnforoutgoinginventory.However,informationcanbeorganizeddifferently.Insteadoftwocolumns,wecancombinethetwofieldsinonecolumn,andadda“Direction”field,sothatthedatatablewilllooklikethefollowing:
Inthiscase,contrarytothe“Calculatedfield”,whichisperformedbetweenthetwofields,weseektomakeacalculationonitemsinthesamefield.Thatis,tosubtracttheoutgoingitemsofinventoryfromtheincomingitems,usingthe“Direction”field.
1.Fromthedataabove,createapivottablelikethefollowing:
2.CanceltheGrandTotalwhich,inthiscase,displaysanincorrectresult(duetothedatastructure,theincomingandoutgoingdatashouldbesubtractedand
notadded).
3.Placethecursorononeofthetitlefieldsdesignatedforcalculation(“In”or“Out”).
4.Select“Fields,ItemsandSets” ”CalculatedItem”:
5.Constructtheformula:
5.1.Inthenamefield,typeameaningfulname(e.g.“Inventory”).
5.2.Inthenamefield,typeameaningfulname(e.g.“Inventory”).
5.3.Selectthe“Direction”field,ontheleft-handsideofthescreen.
5.4.Itemsof“Direction”willappearontheright-handsideofthescreen,ascanbeseeninthefollowingfigure:
5.5.Buildtheformulabyusingitems(itemsaredatawithinafield):
5.6.Selectthefirstitemandthen“InsertItem”(inourexample,select“In”).
5.7.Typethedesiredaction(inourexample,subtraction).
5.8.Selecttheseconditemandthen“InsertItem”(inourexample,select“Out”).
5.9.Theformulawillappearatthetopofthescreen,asshowninthefollowingfigure:
5.10.Click
6.Thefollowingpivottablewillappear;itincludesthedifferencebetweentheincomingandoutgoinginventoryforeachSKU:
Notethattheitemnameisacomponentintheformulaofthecalculateditem.Therefore,changingtheitemnamesinthedatatable(forexample,replacing“Inventory”with“InventoryIncome”)mayaffecttheresultofthecalculatediteminthepivottable.
Exercises
1.Displaythenumberofcarsbydesiredprice.Displaythequantitygroupedtounitsof500USD.
2.Displaytheaveragedesiredpricebyenginecapacity.Changeittomaximumpricebyenginecapacity.
3.Displaythenumberofcarsbydateofsale.Groupthedatesbyyears,quartersandmonths.
4.Displaytheaveragedesiredpricebymake.GrouptheJapanesecarsonly(SubaruandSuzuki).
5.Displaytheaverage,minimumandmaximumprice,bygeartype.
6.Displaythesumofsalesbygeartype.
7.Addtax(4%)tothesalepriceofthepreviousreportusingCalculatedField.
8.Displaytheamountofthedesiredpricebypublicationdate.Addacolumnandcalculatetherunningtotalbypublicationdate.
9.CreateareportthatdisplaysthecountryofmanufactureintheROWS,thetypeofgearintheCOLUMNandthenumberofcars(counting)intheVALUES.Displaythedifferencebetweenthenumberofcarswithautomaticgearsandthenumberofcarswithmanualgears,usingCalculatedItem.
ChangingTheReportStructureThepivottableenablesustomakechangesinthereportstructure.Thechangeswillbedisplayedimmediatelyby:
Switchingbetweencolumnsandrows
Insertingcolumnsorrows
Switchingcolumnsandrows
Whencreatingareport,thelocationsoffieldscanbechangedbydraggingthemtoadifferentarea.
Inthefollowingexample,wecreatedapivottablethatshowstheaveragemonthlysalaryforeachcityandrole:
Thefollowingtableappears:
However,sincethenumberofcitiesissmallcomparedtothelargenumberofroles,readingthedatabecomesquitedifficult.Tomakeitclearer,wecanswitchtherowsandcolumnsofthepivottablearound.
Allwehavetodoistodragthe“Role”fieldtotheCOLUMNSandthe“City”fieldtotheROWS:
Thefollowingpivottablewillappear:
Notethatbothtablesdisplaythesamedata.Selecttheonewhichismorereadableforyou.
Addingcolumnsorrows
1.AdditionalcolumnsorrowscanbeeasilyaddedtothereportbydraggingtheappropriatefieldstotheCOLUMNSorROWS.
Inthisexample,weaddedthe“Gender”fieldtotheROWS,belowthe“Role”field:
Thefollowingtabledisplaystheaveragesalaryineachcity,dividedbyrole,thengender:
WithintheROWSarea,wecandragthe“Role”fieldbelowthe“Gender”field.
Wegetthefollowingpivottable,whichdisplaystheaveragesalaryineachcity,dividedbygender,thenrole:
Addingapagebreakbetweenitems
Wemaysometimesneedtoprinteachitemofthepivottableonaseparatepage.Suchaneedarises,forexample,ifthedepartmentmanagerrequiresonlythedetailsoftheirowndepartment.
Toinsertapagebreakbetweenitems,followthesesteps:
1.CreateapivottablewithatleasttwofieldsintheROWS(“Department”and“Role”,forexample).
2.Placethecursorontheupperfield(inourcase,“Department”),andpressthearrow.
3.Clickon“FieldSettings”:
4.Select“LayoutandPrint”tab.
5.Checkthe“Insertpagebreakaftereachitem”box:
6.Atthesecondstage,gotothe“PAGELAYOUT”tab ”PrintTitles”:
7.Selectthetabletitlesas“Rowstorepeatattop”:
Now,thedataforeachdepartmentwillbeprintedonaseparatepage.
Exercises
1.CreateapivottablereportdisplayingthecarcolorintheROWSandthegeartypeintheCOLUMNS.Calculatetheaveragedesiredprice.
2.SwitchtheROWSandCOLUMNS.
3.CreateapivottablereportdisplayingthecarcolorintheROWSandthecountryofmanufactureintheCOLUMNS.Calculatethenumberofcars.
4.SwitchtheROWSandCOLUMNS.
FormattingPivotTables
StructureFormat
Pivottableshaveformatdefaults,setbythesoftware.However,wecancustomizethetablesaccordingtoourneeds.
SubtotalsIfthepivottablehasatleast2fieldsintheROWSorintheCOLUMNS,datainthepivottablewillappearwithsubtotalsaftereachchangeofaniteminafield,asshowninthefollowingfigure:
Subtotalformat:
Wecanformatthesubtotalsinordertodistinguishmoreeasilybetweenthemandthedata.
Formattingcanbedonebycellcolor,fontcolor,highlights,fontsize,etc.:
Placethecursorinthetotalrow,ononeofthetitles.Inourexample,youcanplacethecursoron“DetroitTotal”.
Movethecursortotheleftuntilitturnsfromthe shapetotheshape.
Theentiretotalrowisnowselected,asshowninthefollowingfigure:
Thecellscannowbeformattedaswewish(cellfilling,color,size,etc.).
RemovingorChangingthesubtotalslocation:
1.Placethecursoronthepivottable.
2.Selectthe“DESIGN”tab “Subtotals”:
3.Selectthedesiredoption.
SortingPivottablesarecreatedalreadysortedinascendingorder(alphabeticalornumeric).
Thesortingordercanbechangedbyfollowingthestepsbelow:
1.Clickonthearrowatthetopofthefield.
2.Selectthedesiredtypeofsorting:
Furthermore,thepivottableenablesustoarrangethedatabymovingthefieldstothedesiredpositions:
1.Right-clickontheitemtoberepositioned.
2.Fromthedrop-downlist,select“Move“.
3.Choosethenewposition.
Notethatthefieldscanalsobedraggedwithinthepivottableinordertochangetheirlocation.
FilteringBydefault,thepivottabledisplaysallthedatafieldsdraggedintoit.Ifwewantto
viewonlysomeofthedata,weusetheFilteroption:
1.Clickthearrowatthetopofthefield.
2.Selectthevaluestobedisplayed:
3.Click
4.Thefollowingpivottablewillappear:
Notethatthefiltericonhaschangedfrom to ,inordertoindicatethatthedatahasbeenfiltered.
Removingfiltering:
1.Clickonthefilteringarrow.
2.Select“ClearFilter”:
ValueFilters:
Pivottableshaveotherwaystofilterdata,suchasdisplayingvaluesthataregreaterthan,orlessthan,acertainvalue:
1.Clickthefilteringarrow.
2.Select“ValueFilter”.
3.Selectthedesiredfilteringoption:
LabelFilters:
Excelenablesustofiltertextualdatainmanydifferentways,forexample–by
displayingtextualdatathatbeginswith,endswith,contains,ordoesnotcontaincertaincharacters.
1.Clickonthefilteringarrow .
2.Select“LabelFilter”.
3.Selectthedesiredfilteringoption:
Inthefollowingexample,wewanttoseeallthecitynamesbeginningwiththeletterN:
Thefollowingpivottablewillappear:
Removingdatawhichhasbeendeletedfromthefilterlist
Likeanydatabase,thetablesonwhichthepivottablesarebased,aresubjecttochange.Oneofthemostcommonchangesisthedeletionofdata.
However,afterthedatahasbeendeleted,itwillstillappearinthelistofthefields.
Toremovedeleteddatafromthefilter,followthenextsteps:
Selectthe“Options”buttonunderthe“ANALYZE”tab:
Inthefollowingwindow,Selectthe“Data”tab:
In“Numberofitemstoretainperfield“,select“None”.
SlicersTheslicersareanewoptionthatwasintroducedintheExcel2010version.Itdisplayson-screenbuttons,enablingustofilterthepivottabledata.
Thisnewoptioniseasytooperate,anddisplaysthecurrentfilteringstateclearly,thusmakingthereportdatamorereadablefortheuser.
Usingtheslicersreplacestheuseofdrop-downlistsforselectingtheitemstobefiltered(asdonewiththeotherfiltercomponents).
Inordertousetheslicerstool,makesurethatthefileisinthe2010formatat
least.
Ifthefileformatisanolderversion,thentheslicersoptionwillbedisabledandthefilewillhavetobeconvertedtothenewversionbyselectingtheFILEtab INFOcategory CONVERT.
Notethataftertheconversion,youwillhavetocloseandre-opentheworksheet.
Afterreopening,refreshthepivottables.
Creatingfiltersusingslicers:Creatingthefilter
1.Createapivottable.
2.Underthe“ANALYZE”tab,select“InsertSlicers”(itcanalsobefoundinthe“INSERT”tab):
3.Thefollowingwindowwillappear:
4.Selectthefieldsyouwanttoaddasslicerstothepivottable.
5.Click
6.Thefollowingslicerwindowwillappear:
7.Oncewehaveselectedtheitemswewant,onlythefilteredrecordswillbedisplayed:
Note:Toselectmultiplefilters,useCtrl:
ManipulatingSlicers1.Selectaslicer.
2.The“Slicers”tabwillappear.
Changingtheslicer’sname
1.Thecaptionthatwillappearasthewindowtitlecanbechanged:
Creatingconnections:
Ifthereareanumberofpivottablesbasedonthesamedata,theslicerscanbelinkedtomorethanonetable:
1.Click
2.Thefollowingwindowwillappear:
3.Selectthedesiredtables.
Changingtheslicer’sappearance:
1.Differentstylescanbeselectedfortheslicers:
2.Thenumberofcolumnsintheslicerwindow,andthesizeofthebuttonscanbeset:
Youcanseethisinthefollowingfigure:
RemovingitemswhichweredeletedfromthedatasourceTheslicerscancontainitemswhichweredeletedfromthedatasourceaftertheywerecreated.
Theseitemscanberemovedasfollows:
1.SelecttheSlicerswindow.
2.Under”SLICERTOOLS” ”OPTIONS”tab,click
3.Thefollowingwindowwillappear:
4.Uncheck“Showitemsdeletedfromthedatasource”.
TheTimeline
AnothertoolthatenablesustofilterthedataistheTimeline,whichhelpsusfiltertherecordsbydates.
InsertTimeline:1.Createapivottable.
2.Underthe“ANALYZE”tab,select“InsertTimeline”:
3.Awindowthatcontainsdatefieldswillappear:
4.Selectthedesiredfield.
5.Click
6.Thetimelinewillappear:
7.ChoosethedesiredperiodbyselectingitontheTimeline:
Inordertoselectacontinuousperiod,simplydragthecursoroverit:
Changingthetimeperiod:1.Clickonthearrow,asshowninthefollowingimage:
2.Selectthedesiredtimeperiod.
ChangingtheTimeline’sappearance:DifferentstylescanbeselectedfortheTimeline:
Selectthe“OPTIONS”tabunderthe“TIMELINETOOLS”tab:
ChangetheTimelinecaption:
Selectastyle:
SelecttheTimelinesize:
(YoucanalsochangethesizeoftheTimelinebydraggingitsborders.)
ShoworhidetheHeaders,SelectionLabelandTimeLevel,ortheappearanceofthescrollbar:
Connectittonumerouspivottablesbypressingthe button,andselectingthedesiredpivottables.
Exercises
1.Displaythedesiredaveragesalepriceby:enginecapacity,yearofmanufactureandtypeofgear.Paintthesubtotalsinyellow.
2.Cancelthesubtotalsfromthepreviousreport.
3.Displaythedesiredpriceaveragebydateofsale.Sortthetableindescendingorder,bydate.
4.Displaytheaveragesalepricebycolor.
5.Movethewhite-coloredcarstothebottomofthetable.
6.Displaythenumberofcarsbycountryofmanufacture.Usingafilter,displayEuropeancarsonly.
7.Displaythenumberofcarsbymake.Usingafilter,displaythecarnamesstartingwith“S”only.
8.CreateapivottablereportinwhichthemakewillappearintheROWSandtheaveragedesiredpricewillappearintheVALUES.Displaythecountryofmanufactureusingslicers.
DesignTabPivottablescanbeformattedbyusingthecellformattingoptions,andalsobyvariousothertools.
Formattingcommandscanbefoundinthe“PIVOTTABLETOOLS” ”DESIGN”tabandaredividedintodifferentcategories:
Layoutcategory
Subtotals-thisenablesustocancelsubtotals,ortoshowthemaboveorbelowthegroup.
Totals-thisenablesustoshoworhidethetotalsinROWSandCOLUMNS.
Reportlayout-thisenablesustoviewthepivottableinacompact,outlineortabularform.Wecanalsouseittorepeatorcanceltherepetitionoflabels.
Blankrows-thisenablesustocreateorremoveblankrowsbetweenitems.
Pivottablestyleoptions
RowHeaders-thisenablesustodisplayorcanceltheboldingofitems.
ColumnHeaders-thisenablesustodisplayorcanceltheboldingoffields.
BandedRow/BandedColumn–thispaintstherowsorcolumnsalternately.
Quickdesignstylesofpivottable
Thisenablesustochoosebetweendifferentstyles,asshowninthetab.
ChoosinganewstyleTocreateanewdesignforthepivottable:
1.Click“NewPivotTableStyle”:
2.Thefollowingwindowwillappear:
3.Namethenewlydefinedstyleanddesignthetablecomponentsasdesired.
4.Thenewstylewillappearatthetopofthecustomizationlist,inthegallery.
Exercises
1.Displaytheaveragesaleprice,dividedbymake,colorandcountryofmanufacture.
2.Cancelthesubtotalsandthegrandtotal.
3.Switchthereportlayouttoatabularform.
ChartsSofar,wehavelearnedhowtocreatepivottablestodisplaydataindifferentintersections.Excelalsoenablesustocreatechartsbasedonthepivottable,foramorevisualdisplayofthedata.Achartcanbeaddedtoanexistingpivottable,orcreatedatthesametimeasapivottable.
Addingacharttoanexistingpivottable
1.Placethecursorinthepivottable.
2.Selectthe“ANALYZE”tab “PivotChart“:
3.Thefollowingwindowwillappear:
4.Selectthedesiredcharttype.
5.Click
6.Theselectedchartwillappear:
Note:Youcanalsocreateachartbasedonthepivottable,usingthe“INSERT”tabtoselectthedesiredcharttype.
Simultaneouslycreatingapivottableandachart
Placethecursoronthedatatable.
Selectthe“INSERT”tab.
ClickthearrowbelowthePivotChartbutton.
Select“PivotChart&PivotTable“:
Thepivottableandthepivotchartwillappearinthesamewindow:
DraggingthefieldstothedesiredareaswilldisplaytheminthePivotTableandthePivotChartsimultaneously.
Exercises
1.Displaytheaveragesalepricebymakeandcolor.
2.Createachartbasedonthepivottableyoucreated.
TipsandTricksUsingRecommendedPivotTables
Excel2013hastheabilitytoanalyzethedata,andrecommendthemostsuitablepivottablesforit.
Tousethisfeature,locatethecursorinacellinyourdatatable,andunderthe
“INSERT”tab,click
Excelwillsuggestalistofpivottables:
Selecttheonethatsuitsyourneeds.
Quicklycreatingapivottable
Apivottablecanbecreatedquicklybycopyinganexistingpivottable,pastingandcustomizingit,accordingtoyourneeds.
Obtainingthedatasource
Apivottableiscreatedfromrawdatatodisplaygroupeddata.
Thereverseactioncanbeperformedaswell:displayingtheconstitutingrecordsofaniteminthepivottable(Drilldown).
Inthisexample,thepivottabledisplaysthenumberofemployeesineachrole:
Inordertoseetheoriginalrecords,double-clickthedesiredvalue.Intheexampleabove,double-clickingonthenumber3nextto“Accountant”willdisplayanewworksheetcontainingtherecordswhichcreatedtheresult3.
CreatingTabsfromFilters
Apivottableenablesustosplittheprimarypivottabledataintotabs,byusingthefilteringfield.
Forexample,fromapivottablecountingthenumberofemployeesineachrole,wecancreateatabforeachsection,asexplainedbelow:
1.Createapivottable.
2.DragtherelevantfieldintotheFILTER,asshowninthefollowingfigure:
3.Selectthe“ANALYZE”tab ”Options”button.
4.Select“ShowReportFilterPages“:
5.Thefollowingwindowwillappear:
6.Selectthefilterwiththedatatobedisplayedinthetabsandclick
NowthefilecontainstabsbythenamesofitemsdraggedtotheFILTER.Eachofthetabsconsistsonlyoftherelevantdata:
FilterLocations
Bydefault,thepagefiltersaredisplayedinonecolumn,asshowninthefollowingexample:
Excelenablesustoseparatethefiltersintoseveralcolumns,byfollowingthestepsbelow:
1.Clickthe“ANALYZE”Tab.
2.Click“Options”:
3.Thefollowingwindowwillappear:
4.Inthe“Layout&Format”tab,change“Reportfilterfieldspercolumn”tothedesiredvalue.
5.Thefiltersintheresultingpivottablearenowdividedintocolumns:
GetPivotData
TheGetPivotDatafunctionisdesignedtofindtheintersectedvaluebetweenfields(i.e,CityandGender).
WheneverweusedatafromapivottableinourExcelformulas,thefunctioniscreatedautomatically.
However,sincethedefaultoftheGetPivotDatafunctionisanabsolutereference(asopposedtoExcel’sfunctions,wherethedefaultisarelativereference),youmaywanttocanceltheGetPivotDatafunctionwhenapplyingittothepivottables.
Tocancelthisfeature:
1.Select“PIVOTTABLETOOLS” “ANALYZE”tab ”Options”button.
2.Uncheck“GenerateGetPivotData”.
IfyouprefertokeeptheGetPivotDatafunction,youcanusearelativereferencebytypingthereferencetothecell(insteadofpointingatthecellwiththecursor).
Sortingthefieldlist
Whencreatingapivottable,theFieldListappearsinthesameorderasinthedatatable:
Wecansortthefieldlistalphabetically,asfollows:
1.Selectthe“ANALYZE”tab ”Options”button.
2.Inthe“Display”Tab,select“SortAtoZ“:
3.Thefieldlistwillbesortedaccordingly:
Repeatingtheitemlabels
Wehavecreatedapivottablethatcountsthenumberofemployees,dividedintodepartmentandsection,andformatteditasatable:
Althoughthispivottableisreadablefortheuser,itmakesitdifficultforExceltoperformcalculationsdesignedtoretrieveinformationfromit(e.g.INDEX,MATCH).
Tonormalizethetable,followthestepsbelow:
1.Placethecursorinsidethepivottable.
2.Under“PIVOTTABLETOOLS“,selectthe“DESIGN”tab:
3.Under“ReportLayout“,select“RepeatAllItemLabels”:
4.Thefollowingpivottablewillappear:
Nowwecanusethetableasasourceofdesiredcalculations.
AdvancedUsesofPivotTablesFindinguniquerecordsandduplicaterecords
Apivottableenablesustofindduplicaterecordsinadatabase.
Inthisexample,wehaveobtainedlistsofregisteredmembersoftwopoliticalparties,andwewanttocheckiftherearepeoplewhoareillegallyregisteredtobothparties,andwhichpeopleareregisteredtoonlyoneparty.
WehavecreatedapivottableinwhichwedraggedtheIDrecordstotheROWSandthepartyregistrationfieldtotheCOLUMNS.WealsodraggedtheIDfieldtotheVALUES:
Thefollowingpivottableappears:
Fromthepivottableabove,itappearsthattheownerofID1650wasregisteredtobothpartiesAandB,whiletheownerofID1213wasregisteredtopartyAonly.
HumanResourcesPlanning
ThedirectoroftheHumanResourcesDepartmentconductsanannualstaffingplan.Heisprovidedwithdepartmentalrequestsaboutspecificneeds,movespeoplefromonedepartmenttoanother,outsourcesandfiresemployees,etc.
Therawdataisshowninthefollowingtable:
Asshowninthedatatable,10employeesaretobetransferredfromthePurchaseDepartmenttotheSalesDepartment,and15employeesaretobetransferredfromthePurchaseDepartmenttotheHumanResourcesDepartment.
Inordertoknowmoreaboutthetransfersbetweendepartments,wecancreateapivottable,wherethesourcewillappearinrowsandthetargetwillappearincolumns.Thenumberofemployeeswillberepresentedinthevalues:
Nowitiseasiertoreviewthetransfersofemployeestoandfromdifferentdepartments.
Forexample,itiseasytoseethat25employeesmovedfromtheSalesDepartmenttotheHumanResourcesdepartment,20employeesmovedfromtheSalesDepartmenttothePurchaseDepartmentand45employeeswerefiredfromtheSalesDepartment.
Wecanalsoseethat60employeeswererecruitedfortheSalesDepartment,65employeeswererecruitedfortheHumanResourcesdepartmentand55were
recruitedforthePurchaseDepartment.
Fromthetotalrow,welearnthat105employeesmovedtotheSalesDepartmentand90arenolongeremployedatthefactory.
CashFlow
Abusinessownerentershiscustomers’paymentdataintotheExceltablebelow:
Thetabledisplaysthecustomer’sname,dateofpayment,expectedpayment,andtheactualpayment.ThecashflowcanbederivedfromthedatatablebydraggingthedateofpaymenttotheROWSandtheexpectedpaymenttotheVALUES:
Fromthepivottablewecanconcludethaton3/1/2014,asumof2,000USDisexpectedtobedepositedinthebusinessaccount.
Thesamedatatablecanbeusedtoviewthestatusofcustomerpayments,withdetailsoftheexpectedpaymentandtheactualpayment.
Thebalanceduewascalculatedusing“CalculatedField”
ProfitandLoss
Afactorymanagerentersthefactoryrevenueandexpensestothefollowingtable:
Inordertocreateaprofitandlossstatement,wehavetocreatethefollowingpivottable:
InordertoviewtheProfit&LossStatement,wehavetore-orderthedatabydraggingtherevenuedatafromsalesandplacingitbeforetheexpensesdata:
Wecannowuse“CalculatedItem”inordertoperformthecalculationsofgrossprofit,operatingprofit,andnetprofit:
Thefollowingpivottableappears:
Nowallwehavetodoistomovethecalculateditemstotheappropriateposition:
Thepivottableaboveshowsthefactory’sProfit&LossStatement.
Notethattheboldcolumnsaretheresultofacalculateditemfromdataintheoriginalpivottable.
Appendix
Selectingdatafromotherfiletypes
Youmaysometimesneedtocreatepivottablesusingdatafromotherfiletypes,suchasMicrosoftAccess,SQLdatabase,etc.
Inordertocreatethedatatable,wefirstneedtoconnecttothedatabaseasfollows:
1.Inthe“INSHeadersERT”tab,select“PivotTable“.
2.Thefollowingwindowwillappear:
3.Clickthe button.
4.The“ExistingConnections”windowwillappear:
Note:Thewindowmaylookdifferentonyourcomputer,dependingonthecurrentconnections.
5.Click
6.The“SelectDataSource”windowwillappear:
7.Navigatetothefolderwherethedatafileislocatedandselectthefile.
8.Click
9.InthefollowingexampleweselectedanAccessdatabase.
10.The“SelectTable”windowwillappear:
11.Selecttherelevanttable.
12.Click
13.Anemptypivottablethatcontainsthefieldsoftheselectedtablewillappear.
PivotTablesWizard
InolderversionsofExcel,userscouldbuildthepivottablestepbystep,usingthePivotTablesWizard.
Thewizardincludedotheroptionsaswell,suchas“multipleconsolidationranges”,whichdonotappearintheRibbonVersions(Excel2007andhigher).
Userswhoareaccustomedtousingthewizard,orusingtheoptionsavailable,canaddittotheQuickAccessToolbar:
1.ClickthearrowintheQuickAccessToolbar.
2.Select“MoreCommands”:
3.Thefollowingwindowwillappear:
4.From“CommandsNotintheRibbon“,select“PivotTableandPivotChartWizard”.
5.Click
6.Click
7.ThePivotTablesWizardwillbeaddedtotheQuickAccessToolbar:
MultipleConsolidationRanges
YoumightsometimesneedtocreateaPivotTablereportbasedondifferenttables.
Itisrecommendedtounitethetablesintoasingletable.
However,ExcelallowsustocreatepivottablesthatarebasedonMultipleConsolidationRanges,thatis-onanumberoftablesthatcontaindatainthesamestructure.
Beforecreatingatable,makesurethatthetitlesinthetablesareallidentical.
Inthefollowingexample,wehaveanExcelfilethatcontainstwosheetswithanidenticalstructure.Onecontainsthedataaboutfemalesandtheothercontains
dataaboutmales:
1.OntheQuickAccessToolbar,clickonthePivotTablesWizard(toaddaPivotTableWizard,seehere).
2.Thefollowingwindowwillappear:
3.Select“MultipleConsolidationRanges”.
4.Click
5.Select“Createasinglepagefieldforme”:
6.Click
7.Thefollowingwindowappear:
8.Selectthedesiredrangeinthefirstworksheetandclick
9.Repeattheoperationforeachoneoftheranges:
10.Click
11.Selectthedesiredlocationforthepivottable:
12.Click
13.Anewpivottablethatcontainsthedatafrombothtableswillbecreated.
14.Pleasenotethatthepivottablecreatedwillbeverylimitedcomparedtoapivottablethatwascreatedfromasingletable.
TheDataModelSofar,wehavedealtwithcreatingpivottablesbasedonasingledatatable.
Whenwewantedtocreateapivottablefrommultipletables,wehadtomergethemintoonetable,usuallyusingtheVLOOKUPfunction.
Excel2013introduces,forthefirsttime,theuseofthe‘DataModel’,atermthatcamefromthefieldofbusinessintelligence(BI)andallowsustocreatepivottablesfrommorethanonetable.
Sincewearetalkingaboutmultipletablesandtherelationshipsbetweenthem,thefirstpartofthischapterwillbededicatedtoabriefacquaintancewithsomeimportanttermsregardingdatabases.
Inthesecondpartofthechapter,wewilllearnhowtocreateapivottablethatisbaseduponmultipletables.
Basictermsindatabases
RelationshipsInordertounderstandwhatrelationshipsare,wewillusethe“Employees”database.However,thistimethedatacomesfromtwodifferenttables(asopposedtotheformerchapters,whenthedatacamefromasingletable):
1.Employeestable-atablethatcontainsthedataoftheemployeesandthedepartmenttowhichtheybelong:
2.DepartmentsandSectionstable-thisservesasanauxiliarytable,andspecifiestowhichdepartmenteachsectionbelongs:
IfwewantedtocreateapivottableinExcel2010whichwillshowthetotalsalariesineachsection,wewouldhavetoimportthesectioncolumnfromtheauxiliarytabletoourPrimaryDataTableusingVLOOKUP,andonlythencreateapivottablebasedonthecombinedtable.
Excel2013displaysasignificantimprovement,whichisbasedontheconceptoflinkingtwotables,usingacommonfield.
Itusesalogicalrelationbetweentwotablesandenablesustotakedatafromonetableand,throughthecommonfield,associateitwithcorrespondingrelevantdatafromanothertable.
IfyouarefamiliarwiththeVLOOKUPfunction,itwillbemucheasierforyoutounderstand,becausethisfunctionlocatescorrespondingdetailsfromonetablethroughthecommonfield,anddeliversthemtotherelevantrecordsinanothertable.
Inourcase,thecommonfieldisthedepartmentfieldthatlinksthetwotables.
Note:theemployeestablecontainsthedataof1,000employees,butthetableofdepartmentsandsectionscontainsashortlistof7recordsonly,asthatisthenumberofdepartments.
IfweusedtheVLOOKUPfunctiontocreateonetable,therewouldbe1000additionalfunctionstothedatatable(afunctionforeachrowinthetable).
Whenwelogicallyconnecttables,wesavethememoryfrombeingoccupiedbythislargenumberofformulas.
TypesofRelationshipIngeneral,therearethreemaintypesofrelationshipbetweentables:
1.One-to-one-a‘one-to-one’relationshipmeansthatonefieldofthefirsttablelinkstoonlyonerecordinthesecondtable.Similarlyonefieldfromthesecondtablelinkstoonlyonerecordinthefirsttable.Anexampleofsucharelationshipisadatatablesplitintotwodifferenttables-theemployeestablethatcontainsemployees’IDandtheirdepartment,andthesalariestablethatcontainsemployees’IDandtheirsalaries.Therelationshipbetweenthesetwotablesisaone-to-onerelationship,sincetheybothcontainthesamelistofemployees,andeachemployeeappearsonlyonceineachofthem.Thedivisionintotwotablesisdoneforreasonsthatareunrelatedtotheessenceofworkingwithdatabasesandcanbe,forexample,securityconcerns(whilethedepartmentnameoftheemployeecanbevisible,theirpaydataisconfidential,andtheyshouldnotbekeptinatablethatisvisibletoeveryone),orfortheease-of-useinhugetableswithmanyfields.
2.One-to-manyrelationship-thisiswhenoneiteminafieldintableAcanberelatedtonumerousrecordsintableB,whereasonefieldintableBcanberelatedtoonlyonerecordintableA.Agoodexampleofthiscouldbetherelationshipbetweentheemployeestableandatabledocumentingtheemployees’vacations.Theemployeestablecontainsrelevantinformationabouttheemployeesandeachofthemappearsthereonlyonce.However,employeescanappearinthevacationtablemanytimes,accordingtothenumberofvacationseachofthemtook.Therelationshipbetweenthetablesisaone-to-many,sinceforanyemployeedataintheemployeestable,therecanbemultiplerecordsinthevacationstable(eachemployeecangoonvacationseveraltimes),butforanyemployeedatainthevacationstable,therecanbeonlyonerelatedrecordintheemployeestable,becauseeveryemployeeisdocumentedthereonlyonce.Infact,thisisthemostcommontypeofrelationship.
3.Many-to-manyrelationship-arelationshipinwhichforeachrecordinonetable,therecanbemultiplecorrespondingrecordsinthesecondtable,andforeachrecordinthesecondtable,therecanbemanycorrespondingrecordsinthefirsttable.Thebestexampleofthisisstudentsandcourses-eachstudentcanlearnalargenumberofcourses,andoneachcoursetherearealargenumberofstudents.Arelationshipofthistypeiscreatedthroughtwoone-to-manyrelationships.
Thepossibletypesofrelationshipsbetweenthedatamodelareone-to-oneandone-to-manyrelationships.
PrimarykeyAprimarykeyisanidentifierofarecord.Itappearsonlyonceinthetable,andtherecordcanbeidentifiedbyit.
CommonprimarykeysareID,employeenumber,licensenumber,catalognumber,andsoon.
Theprimarykeyintheemployeestableistheemployeenumber,butprimarykeyscanalsobetextual,asinthedepartmentstable,wheretheprimarykeyisthenameofthedepartment.
ForeignkeyAforeignkeyisaprimarykeyofonetable,whichappearsinanothertable.
Incontrasttotheprimarykey,whichcanappearonlyonceinthetable,aforeignkeycanappearinthetablemorethanonce.Inourcase,adepartmentfieldthatistheprimarykeyinthedepartmenttable,andthusappearsonlyonce,servesasaforeignkeyintheemployeestable,andappearsmultipletimes.
Thefollowingillustrationwillhelpyouunderstand:Everydepartmentappearsonlyonceinthedepartmentstable:
Whereasthedepartmentsappearmultipletimesintheemployeestable:
Therefore,theemployeestablecontainstwokeys:aprimarykey,whichistheemployeenumber,thatappearsonlyonce,andaforeignkey,whichisthedepartmentname,thatcanappearseveraltimesintheemployeestable(butonlyonceinthedepartmentstable).
Thecreationofrelationshipsbetweentablesisdonethroughthecommonkey,whichistheprimarykeyinthefirsttable,andtheforeignkeyinthesecondtable.Inourcase,thisisthedepartmentfield.
Creatingapivottablebasedontwoormoretables
Thecreationofapivottablethatisbasedonmorethanonetableisdoneasfollows:
1.Createdynamictablesfromalltherelevanttablesinyourworkbook(seehere).
2.Createanemptypivottablefromoneofthetables,byusingtheDataModel.
3.Createtherelationships.
4.Dragthefieldstothedesiredpivottableareas.
Creatingthepivottable
Afterwehaveconnectedthetwotables,wecancreateapivottablethatisbasedonboth.
Inthefirststage,wehavetoturnthedataintoadynamictable,aswelearnedhere(itispreferabletoprovidethetableswithmeaningfulnames).
NowwecanselecttheemployeestableandclickINSERT PivotTable.
ThebigchangeintheExcel2013versionistheoptiontoaddthetabletothedatamodel.Forthispurpose,wecheckthe“AddthisdatatotheDataModel”checkbox,asshowninthefollowingwindow:
Apivottablewillbecreated,whichatfirstglanceseemslikearegularpivottable.
Takingacloserlook,wecanseethattwotabsappearunderthepivottablefields:“Active”and“All”.
Inthe“Active”tab,wecanseethetablethatwasthesourceofourpivottable,ascanbeseeninthefollowingimage:
Inthe“All”tabwecanseethatExcelhasautomaticallycreatedalistofallthedynamictablesthatareinourworkbook.
Clickingonthetrianglearrownexttothenameofthetablewillexpandittodisplaythetablefields:
CreatingRelationships
Beforedraggingthefieldstotherespectiveareas,wehavetocreatearelationshipbetweenthetwotables:
Formingarelationship(OnetoMany):Afterwehaveidentifiedthekeys,weneedtoconnectthetwotablesbyformingrelationsfromtheforeignkeytotheprimarykey(inotherwords,fromthe“Many”sidetothe“One”side).Inourcase,fromthedepartmentscolumnintheemployeestabletothedepartmentscolumninthedepartmentstable.
Formingarelationship(OnetoOne):Ifourdatatablesarebasedonaonetoonerelationship,forexample,atablethatcontainstheemployees’numbersandtheirsalaries:
Andatablethatcontainsotherdetailsabouttheemployees:
Thecreationofarelationshipwillbefromthekeyinthetablethatcontainstheitemsthatshouldbecalculated(inourcase–thesalary),tothekeyinthesecondtable.
CreatingtheRelationship
1.Clickonthe“PIVOTTABLETOOLS”tab.
2.Selectthe“ANALYZE”tab.
3.Clickon“Relationships”:
4.Thefollowingwindowwillopen:
5.Clickon
6.Therelationshipwindowwillopen:
7.Thewindowisdividedintotwoparts:
§Ina“OnetoMany”relationshiptheupperpartreferstothetablethatcontainstheforeignkey,andthelowerpartrelatestothetablethatcontainstheprimarykey.
§Ina“OnetoOne”relationshiptheupperpartreferstothetablethatcontainsthefieldstobecalculatedandthelowerpartreferstotheothertable.
8.Sincewehavea“OnetoMany”relationship,weselecttheemployeestableandthedepartmentcolumn(thatservesastheforeignkey)intheupperpart,whereasinthelowerpartweselectthedepartmentstable
andthedepartmentcolumn(whichservesasaprimarykeyinthistable):
9.Click
Draggingthefields
Aftercreatingtherelationshipsbetweenthe2tables,wecanstartdraggingthefieldstothepivottable.
Inourcase,wewilldragthesectionoutofthedepartmentstabletotheROWSandthesalariesoutoftheemployeestabletotheVALUES:
Wewillgetthesumofallmonthlysalariesineachsection,eventhoughthedataisfromtwodifferenttables!
Andwhatifwedidn’tcreatetherelationshipsinadvance?
Inthefollowingexamplewedraggedthefieldswithoutcreatingtherelationsbefore,andwecanseetwothings:
1.Thetotalpayrollineverysectionisthesame(thisisactuallythesumofthetotalpayroll,withoutdividingintosections):
2.Ayellowmessagebarpromptsustocreaterelationships:
Clickingon willopentherelationshipwindow,andtheircreationwillfixthevaluesinthetable.
DistinctCountFunctions
Inadditiontotheregularcalculations(sum,average,etc.),usingthedatamodelallowsustousethenewfunction,“DistinctCount”,tocountdistinctvalues.
Let’slookatthefollowingexample:
Supposewewanttoknowhowmanydifferent(distinct)rolesexistineachsection.
1.Dragthesectiontotherows.
2.Dragtheroletothevalues.
3.Thepivottablethatwascreateddisplaysthenumberofrolesineachsection,which,fornow,isequaltothenumberofemployeesineachsection:
4.However,inordertoknowhowmanydistinctrolesexistineachsection,wehavetoright-clickonthevaluesfieldandselect“Summarizevaluesby”“Moreoptions”:
5.Select“DistinctCount”:
6.Click
7.Apivottablethatshowsthenumberofprofessionsineachsectionwascreated:
8.Wecanseethatthereare6differentrolesinmanagement,4inmanufacturingand3differentrolesinsalesandmarketing.
Thedatamodellimitations
Sofar,wehaveseenthesignificantadvantagesofworkingwiththedatamodel,buttherearealsosomedisadvantagesthatwehavetopayattentionto:
GroupingThefirstdisadvantageisthatitemsthatarebasedontheDataModelcannotbegroupeddirectly.
Thewaytogroupthemistoaddthegroupingfieldstothedatatable.Thatis,ifwewanttogroupbymonthsandyears,wewilladdacolumntothesourcetableand,usingtheMonthandYearfunctions,pulloutthemonthandyearfromthedatefield.Nowwecanaddthesefieldstothepivottabletocreatethedesiredgrouping.
CalculatedfieldsandcalculateditemsCalculatedfieldsorcalculateditemscannotbecreatedifthepivottableisbaseduponthedatamodel(youcandoitusingthePowerPivotadd-in,whichisnotinthescopeofthisbook).
RefreshIfwehavepivottablesthatarebasedonthedatamodel(aswehavelearnedtocreateinthischapter),andpivottablesbasedonthecachememory(aswehavelearnedtocreateinpreviouschapters),wewon’tbeabletorefreshthemtogetherbyusingthe“Refreshall”button,andwewillhavetoaccesseachoneseparatelyandrefreshit.
Displayingthedatasource(DrillDown)Double-clickingonavalueintheVALUESfieldreturnsonlyalistofthefirst1000recordsfromthesourcetable,andnotallthedata.
EpilogueDearReaders,
Greateffortsweremadewritingthisbook,inordertoconveytoyoutheknowledgewehavegained.
However,ifthereareissuesnotexplainedhereinthebook,andwhichareofgreatimportancetoyou,pleaseletusknowbyemail,sowecanmakeevenmoreefforttointegratethemintothenextedition.
ThankYou.
MaayanPolegandSahronBarak
MaayanPoleg:[email protected]
Thebook“ExcelVBA:forNon-Programmers”(ProgramminginEverydayLanguage)
Overtheyears,MicrosoftExcelhasbecomedominantinthefieldofelectronicspreadsheets.
Thestrengthofthissoftwareservesthedemandsofusersoverthewholeworld.
However,withtheexpandinguseofthesoftware,someoftheend-userrequirementsarepossibleonlythroughprogramminginVBA.
Thebook“ExcelVBA:forNon-Programmers(ProgramminginEverydayLanguage)”waswritteninresponsetothegrowingdemandforadvanceduseofthesoftware.
Thebookwaswrittenfor:Thosewhowanttodevelopformsfortheirorganization’smanagement
ThosewhowanttomakeMicrosoftExcelapowerfulutilitythatfacilitatestheirdailywork
The“non-programmers”amongus,whohavetobuildthosereportsinMicrosoftExceloverandoveragain,andwanttoautomatethesesteps
ThebookwaswrittenbyMaayanPoleg,withanunderstandingoftheneedsofMicrosoftExcel’susers,andwiththeintention“todownloadthemagic”intoeverydaylanguage.
Theauthor’sexperienceinbothVBAprogrammingandtraining,providedanopportunitytobringthisworldofdevelopmenttotheinexperienced.
ThebookisdesignedforExcelusers,whodonothaveprogrammingbackgrounds,buthaveadesiretomakethemostofthesoftware.Forthisreason,thebookhasbeenwritteninaneverydaylanguage,reducingtheuseoftechnicalterms.
YoucanorderthebookviaAmazonatthislink