managers, financiers and other excel...

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:info@excel-vba.guru

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