![Page 1: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/1.jpg)
![Page 2: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/2.jpg)
Managers,financiersandotherExcelusers:
Doyourdatatablescontaintoomuchinformation?
Canyouseepatternsandtrendsataglance?
Canyoufigureoutthemeaningofthedata?
Wouldyoulikeyourreporttolooklikethis:
Ittakeslessthan10minutes!Apivottableisasimple,yetpowerfultechnique,thatenablesExcel’susersto
transformdataoverloadintomeaningfulandorganizedknowledge.
Withpivottablesyoucan:
Seethedataindozensofdifferentwayswithasimplemousedrag
Performfastcalculationswithnoneedforformulas
Focusonanotherpartofthedataeachtimeandgetaclearpicture
Showmoretrendsandpatterns
Createdozensofreportsandchartstoanalyzeyourdata
![Page 3: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/3.jpg)
![Page 4: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/4.jpg)
TableofContentsIntroductionDisclaimerWhatisaPivotTable?BasicConceptsConditionstoCreateaPivotTable
Necessaryconditions
Desirableconditions
Limitationsofapivottable
CreatingaPivotTableCreatingthetable
SourceofData
Selectingfromadatatableinthecurrentfile
SelectingfromadatatableinanotherExcelfile
DataLocation
Structureofthepivottable
Listoffields
Areas
Filter
MultivaluedReportFilter
Changingthedatasource
Creatingapivottablebasedonadynamicdatatable
Refreshingthepivottable
Refreshingasinglepivottable
Refreshingallthepivottablesinafile
Exercises
PerformingCalculations
![Page 5: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/5.jpg)
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
![Page 6: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/6.jpg)
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
![Page 7: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/7.jpg)
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
![Page 8: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/8.jpg)
![Page 9: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/9.jpg)
IntroductionThisbookteachesexperiencedExcelusershowtousepivottables,oneofMicrosoftExcel’smostpowerfultools,forquickandefficientdataanalysisandtominimizeformulaeusage.
ThisbookwaswrittenforExcel2013users.However,sincethedifferencesbetweenExcel2013andExcel2010orExcel2007arenegligibleandmainlymanifestedbythedifferentgroupsortabsintheribbon,usersoftheolderversionscanusethisbookaswell.
Wewouldliketothankallofthosewhotookthetimetoreadthebook’sdraft,examiningitsreadabilityandthereforeensuringthatitoffersnewpossibilitiestoExcelusers.
ThemaindifferencebetweenpivottablesinExcel2013andtheolderversionsconcernsthe“DataModel”whichwillbedealtwithintheappendix.
![Page 10: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/10.jpg)
![Page 11: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/11.jpg)
DisclaimerThisbookwaswrittenspecificallyforMicrosoftExceluserswhoseektoextendandenhancetheirabilitytoanalyzedatafromvarioussources.
Manyeffortsweremadetowriteacompleteandreliablebookaboutpivottables.However,theauthorsarenotresponsibleforanyconsequences,lossordamagetoanyindividualororganizationwhichmayresultbyusingtheinformationprovidedinthisbook.
Werecommendbackingupyourdatabeforeanychangesarecarriedoutusingthisbook.
![Page 12: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/12.jpg)
![Page 13: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/13.jpg)
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:
![Page 14: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/14.jpg)
Thefollowingpivottableswerederivedfromthedatabaseabove:
Numberofemployeesineachdepartment:
Distributionofgendersineachdepartment:
Averagesalaryineachdepartment:
![Page 15: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/15.jpg)
Averagesalaryineachsection,byrole:
![Page 16: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/16.jpg)
![Page 17: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/17.jpg)
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
![Page 18: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/18.jpg)
![Page 19: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/19.jpg)
ConditionstoCreateaPivotTableNecessaryconditions
Eachcolumnmusthaveatitle.
Thetitleshouldbewritteninasinglerow.
Inacolumn,alltheitemsshouldbeofthesamedatatype(numbers,datesorstrings).
Thedatatableshouldnotcontainanymergedcells.
Thedatatableshouldnotcontainsubtotalsorgrandtotals.
Emptyrowsorcolumnsshouldnotremainwithinthetable(ifanemptyroworcolumnremains,Excelwilltreatthetableastwodifferentones).
Aftercreatingapivottable,donotchangethetitlesofthefields,otherwisethepivottablevalueswillbedeleted.
![Page 20: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/20.jpg)
Desirableconditions
Uniquenamesforeachcolumn(whentwofieldsaregiventhesamename,thetitleofthesecondfieldwillbeappendedatendwith2,i.e.“salary2”).
Completedataforallrecords(whendataismissing,thecalculationswillonlybeappliedtotheavailablerecords.Thiscanbeobservedinthecasesofcalculationssuchasaverages,etc).
![Page 21: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/21.jpg)
Limitationsofapivottable
Numberofpivottablereportsintheworksheet:Limitedbytheavailablememory
Uniqueitemsforeachfield:ý1,048,576
Rowfieldsorcolumnfieldsinthepivottablereport:Limitedbytheavailablememory
Reportfilterinthepivottablereport:256(Maybelimitedbytheavailablememory)
Valuefieldsinthepivottablereport:256
Formulasforcalculateditemsinthepivottablereport:Limitedbytheavailablememory
Pleasenote:
Duetothelimitationsofthepivottable,anddependingonyourpersonalcomputerdata,youmayprefertosavetheexercisesappearinginthisbookinaseparatefileorworksheetforeachchapter.
![Page 22: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/22.jpg)
![Page 23: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/23.jpg)
CreatingaPivotTableCreatingapivottableisafastprocedure,consistingofthreesimplestages:
1.Selectingthedatasourceandtablelocation.
2.Dragginginthedesirablefields.
3.Usingthetoolsintheribbonforcalculationsandformatting(mostofthemcanbeoperatedbyright-clickingthecorrespondingareainthepivottable).
![Page 24: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/24.jpg)
Creatingthetable
1.Placethecursorinacellwithinthedatatable.
2.IntheINSERTtab,clickonthe“PivotTable”button:
3.Thefollowingwindowwillappear:
4.Inthiswindow,select:
Thedatasourcetobeanalyzed(seethe“SourceofData”sectionbelow).
Thedesiredlocationofthepivottablereport(seethe“DataLocation”section).
5.Click
![Page 25: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/25.jpg)
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.
![Page 26: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/26.jpg)
DataLocation
Thereportcanbeplacedinthesameworksheetasthedatatable,orinanotherworksheet.
Pleasenotethatifyouchoosetoplacethepivottableonthesamesheetasthedatatable,youwillnotbeabletodeleteanentirerow,andthefollowingmessagewillappear:
![Page 27: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/27.jpg)
Structureofthepivottable
Afterselectingthedatasourceandthelocation,anemptypivottablewillappearintheworksheet.
Nowyoucandragtheappropriatefieldsin,andperformthedesiredcalculations,asshowninthefollowingfigure:
Thescreenisdividedintothreeareas:
Thelistoffields
Thepivottableareas
Thepivottable
ListoffieldsThelistoffieldscontainsthecolumntitlesoftheselectedrange.
Tip:
§Itispreferabletogivethefieldsshortnames,sincetheywillappearastitlesinthepivottable.
§Avoidusingthewords“sum”,“average”,“minimum”,or“maximum”inthe
![Page 28: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/28.jpg)
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:
![Page 29: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/29.jpg)
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.
![Page 30: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/30.jpg)
2.Click“Options“:
3.Inthenewwindow,selectthe“Display”tab:
4.Checkthe“ClassicPivotTablelayout”option.
5.Thefieldnamewillnowappearinsteadof“rowlabel”:
Pleasenotethatcheckingthe“ClassicPivotTableLayout”optionenablesdirectdraggingoffieldsintothepivottableitself,orintothedesiredareaatthebottomofthescreen,asshowninthefollowingfigure:
![Page 31: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/31.jpg)
Inthisexample,the“City”fieldisdraggedintotheROWS.
Thecitynameslistedunderthe“City”fieldappearinthepivottable,andeachitemappearsinadifferentrow.
OtherfieldscanbedraggedintotheROWSaswell(andalsointotheCOLUMNS).Inthefollowingexample,the“Gender”fieldwasdraggedunderthefield“City.“
![Page 32: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/32.jpg)
Citynameslistedunderthefield“City”areshowninthepivottable,andeach“City”fieldisdistributedbygender.
![Page 33: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/33.jpg)
Filter
Aswehavelearnedbefore,thefieldsthatformthepivotsofthedataneedtobeplacedintheROWSorCOLUMNS.Excelalsoallowsustousethosefieldsasanadditionalfilterforthepivottable.
Intheexampleabove,the“Section”fieldwasdraggedintotheFILTERS.
Nowwearefreetodisplayanyoftherelevantdatainthepivottable:
Intheexampleabove,wesoughttoshowthenumberofemployeesineachcityand,usingtheFILTER,wereducedtheinformationtodisplaythemanagementemployeesonly.
Pleasenotethattheiconnexttothefilterchangesto ,toindicatethatthedatadisplayedinthetableisfiltered.
MultivaluedReportFilterBydefault,onlyoneitemcanbeselectedusingthefilter.
![Page 34: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/34.jpg)
Toselectmultipleitems,checkthe“SelectMultipleItems”box.
Wecannowselectmultipleitemstobedisplayedinthepivottable.
![Page 35: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/35.jpg)
Changingthedatasource
Aftercreatingthepivottable,theoriginaldatarangemaychange.
Toapplythenewrangetothepivottable,select“PIVOTTABLETOOLS”tab”ANALYZE”tab “ChangeDataSource”.
Thefollowingwindowforselectinganewdatarangewillappear:
Selectthedesiredrangeandclick
CreatingapivottablebasedonadynamicdatatableIfthedatarangethatthepivottableisbasedonmightchangeinsize,Itisrecommendedtobasethepivottableonadynamictable:
1.Placethecursoronthedatatable.
2.Select“INSERT” “Table“.
Thedatatableisnowchangedintoadynamictable:
![Page 36: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/36.jpg)
Thepivottablebasedonthisdynamicdatatablewillbeupdatedwheneverthedynamictablechangesinsize(youmayneedtorefreshthepivottable).
![Page 37: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/37.jpg)
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.
![Page 38: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/38.jpg)
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?)
![Page 39: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/39.jpg)
![Page 40: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/40.jpg)
![Page 41: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/41.jpg)
PerformingCalculationsChangingCalculations
Aswehavelearnedbefore,apivottablesummarizesnumericdatabydefault,andcountstextualdataanddates.
However,youmaychangethetypeofcalculation,accordingtoyourneeds.
Lookingatthisexample,wecanseethatthepivottablesummedupthesalariesoftheemployeesineachdepartment.Althoughsuchacalculationiseffectivewhenthedesireistoplanthecompanybudgetforthenextyear,wemayalsowanttocomparedifferentdepartments.Inordertodothat,weneedtofindtheaveragesalaryofeachdepartment.
Tochangethecalculationtype:
Placethecursoronanycellofthe“MonthlySalary”column.
Right-clickandselect“SummarizeValuesBy”.
Selectthedesiredcalculationtype,asshowninthefollowingimage:
Thefollowingpivottablewillappear:
![Page 42: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/42.jpg)
Pleasenotethattherowatthebottomisalwayslabeledas“GrandTotal”,nomatterwhatcalculationtypeisbeingusedforvalues.
![Page 43: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/43.jpg)
Datadisplayoptions
Inadditiontothebasiccalculations,Excelenablesyoutodisplaydatainadvancedforms,suchasdistribution,runningtotalandmore.
Todisplaythedata,simplyright-clickanitemintherelevantfieldofthepivottableitself.
Thefollowingwindowwillappear,enablingyoutochoosebetweenvariousoptions:
Percentageofacolumntotal
Thisshowsthedistributionofeachitemoutofthetotalcolumn:
Fromherewecanlearn,forexample,thatthesalaryintheEngravingDepartment
![Page 44: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/44.jpg)
constitutes36.66%ofthetotalsalariesofthefactoryemployees,whilethesalaryintheDivisionHeadquartersisonly0.57%.
Percentageofarowtotal
Thisdisplaysthedistributionofanitemoutoftheentirerow.
Thefollowingexamplesillustratethedifferencebetweenthepercentageofacolumntotalandthepercentageofarowtotal.
Wehavecreatedapivottablewhichdisplaysthenumberofemployeesineachrole,dividedbygender:
Asthetableshows,amongtheemployeesthereare106saleswomenand97salesmen.Itisalsoevidentthatthefactoryemploys9womenand2meninmarketing.
Butwhatifweneedtoknowwhatpercentageofmeninthefactoryaresalesmen?Orwhatpercentageofthewomenaresaleswomen?
Wehavetodisplaythedataasapercentageofacolumntotal:
![Page 45: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/45.jpg)
Wecannowseethatsaleswomenconstitute21.33%ofthetotalnumberofwomenandsalesmenare19.28%ofthetotalnumberofmen.
Wemayaskanotherquestion:amongsalespeople,whatisthepercentageofmenandwhatisthepercentageofwomen?
Forthispurpose,wepresentthedataasa“percentageofacolumntotal”
Wecanseethatamongallsalespeople,womenconstitute52.22%andmenconstitutetheremaining47.78%.
Percentageofagrandtotal
Thisdisplaysthedistributionoutoftheentiredata.
Inthefollowingexample,wecanseethepercentagesofmenandwomen,dividedbyrole,outoftheentireworkforce.
![Page 46: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/46.jpg)
Wecansee,forexample,thatoutofthetotalnumberoffactoryemployees,saleswomenconstitute10.60%,whilesalesmenconstitute9.70%.
RunningtotalPivottablesallowustocalculatetherunningtotalofthedatatable.
Inthenexttable,wecanseethebusinesscashflow,includingrevenuesandexpenses(includingfuturerevenuesandexpenses)ondifferentdates.
Pleasenotethatthereisnoneedtosortthetablebydates.
Dragthefieldstotheproperareas,asshownbelow:
![Page 47: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/47.jpg)
Thefollowingpivottableappears:
Pleasenote:atthisstage,thetwosumcolumnsareidentical.Wenowwanttoturnthesecondsumcolumnintoarunningtotal.
Right-clickaniteminthesecondsumcolumn.Select“ShowValuesAs“,andthenselect“RunningTotalIn”.
![Page 48: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/48.jpg)
Selectthefieldthatwillbeusedasacumulativebasis(thedatefield):
Thefollowingpivottablewillappear:
![Page 49: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/49.jpg)
Thistableshows,forexample,thaton02/09/2012,anamountof329USDwasobtainedandtheaccruedbalanceforthatdateis(-8022)USD.
PercentageofOneofthemoreinterestingoptionswehaveistodisplaythedatacomparedtoothergivendata.Wehavecreatedapivottablethatdisplaystheaveragesalaries,sortedbycity.
Wedraggedthesalaryfieldtothevaluesareatwice,andchangedthecalculationtoaverage.
NowwewanttoseetheaveragemonthlysalarycomparedtoNewJersey.
Inthesecondsalaryfield,select“%of.“
Inthenewwindow,weareaskedtoselectthebasefield(theCityfield)andtheiteminthefield(NewJersey):
![Page 50: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/50.jpg)
Thefollowingpivottableiscreated:
WecanseethatthecityNewJerseyconstitutesthebasis,andthusisdisplayedas100%,whileothercitiesarepresentedincomparisontoit.
Youcansee,forexample,thattheaveragesalaryinDetroitis99.41%oftheaveragesalaryinNewJersey(i.e.,about0.6%lower),whiletheaveragesalaryinMiamiisabout3%higher.
![Page 51: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/51.jpg)
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“:
![Page 52: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/52.jpg)
3.Thefollowingwindowwillappear:
Bydefault,thelowestvalueisdisplayedasthe“Startingat”andthehighestvalueisdisplayedasthe“Endingat”value,butwecanchangethevaluesaswewish,asshowninthefollowingwindow:
4.Selecttheunitsorintervalsbywhichyouwishtogroupthedata(inthiscase,weselectedgroupingby1000USDintervals).
5.Click
6.Thepivottablewilllookliketheonebelow:
Wecannowsee,forexample,that27employeesearnmonthlysalariesrangingfrom2000to2999USD.
![Page 53: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/53.jpg)
DateGroupingInthefollowingexample,wesoughttoexaminethenumberofpeople(identifiedbytheirID)hiredtoworkoneachdate.
Thefollowingtableappears:
Tofacilitateourunderstanding,weneedtogroupthedatasowecanseethenumberofpeoplehiredeachmonth:
1.Placethecursoronthepivottableinoneofthecellsthatcontainsadate.
2.Underthe”ANALYZE”tab,select“GroupField”(youcanalsorightclickonaniteminthedatesfield Group):
3.Thefollowingwindowwillappear:
![Page 54: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/54.jpg)
4.Selectthedesiredoptionsforgrouping:
Pleasenotethatyoushouldselectnotonlymonths,butalsoyears;otherwise,thereportwillshowthenumberofpeoplehiredtoworkeachmonth,regardlessoftheyearofacceptance.Forexample,employeeshiredinJanuary2001willappearinthesamerowasemployeeshiredinJanuary2002.
GroupingtextualdataAsseenabove,itiseasytogroupnumericdataordates.However,itfrequently
![Page 55: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/55.jpg)
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:
![Page 56: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/56.jpg)
![Page 57: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/57.jpg)
MultivaluedReport
Everynowandthenweneedtoperformalargenumberofcalculationsforthesamefield,e.g.calculationofminimum,maximumandaveragesalary.
WecandragthesalaryfieldontotheVALUESareaseveraltimes,andchangethecalculationtypeforeachone.
Inthefollowingexample,wedraggedthesalaryfieldintotheVALUESareafourtimes:
Thefollowingpivottableappeared:
Now,allthatremainsistochangethecalculationforeachcolumn,aswelearnedinthe“ChangingCalculations”section.Thiscreatesthefollowingpivottable:
![Page 58: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/58.jpg)
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.
![Page 59: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/59.jpg)
4.3.Clickthe button.
4.4.Continuetocreatetheformula:
4.5.Click
Thepivottablewilllooklikethis:
CalculationbasedonseveralfieldsinapivottableWemaysometimesneedtoperformacalculationbasedonseveralfieldsinapivottable.
Thefollowingexampleisinventorymanagement,basedonatablecontainingdataaboutthenumbersofingoingandoutgoingproducts:
![Page 60: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/60.jpg)
WewanttocreateareportthatwilldisplaythedifferencebetweentheincomingandtheoutgoinginventoryforeachSKU,acrossalldates.
ThefirststepistocreateapivottablethatcontainsthedataofincomingandoutgoinginventoryforeachSKU:
Tocreatethecalculatedfield:
1.Placethecursoronthepivottable.
2.Selectthe“ANALYZE”tab.
3.Select“Fields,ItemsandSets”.
4.Select“CalculatedField”:
![Page 61: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/61.jpg)
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.
![Page 62: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/62.jpg)
5.Thefollowingtablewillappear:
DeletingacalculatedfieldTodeleteacalculatedfield,selectitfromthedrop-downlistandclick“Delete”
Pleasenotethatthecalculatedfieldshouldnotbedirectlyremovedfromthepivottableareas!
![Page 63: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/63.jpg)
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:
![Page 64: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/64.jpg)
2.CanceltheGrandTotalwhich,inthiscase,displaysanincorrectresult(duetothedatastructure,theincomingandoutgoingdatashouldbesubtractedand
notadded).
3.Placethecursorononeofthetitlefieldsdesignatedforcalculation(“In”or“Out”).
4.Select“Fields,ItemsandSets” ”CalculatedItem”:
![Page 65: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/65.jpg)
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”).
![Page 66: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/66.jpg)
5.9.Theformulawillappearatthetopofthescreen,asshowninthefollowingfigure:
5.10.Click
6.Thefollowingpivottablewillappear;itincludesthedifferencebetweentheincomingandoutgoinginventoryforeachSKU:
Notethattheitemnameisacomponentintheformulaofthecalculateditem.Therefore,changingtheitemnamesinthedatatable(forexample,replacing“Inventory”with“InventoryIncome”)mayaffecttheresultofthecalculatediteminthepivottable.
![Page 67: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/67.jpg)
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.
![Page 68: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/68.jpg)
![Page 69: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/69.jpg)
ChangingTheReportStructureThepivottableenablesustomakechangesinthereportstructure.Thechangeswillbedisplayedimmediatelyby:
Switchingbetweencolumnsandrows
Insertingcolumnsorrows
![Page 70: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/70.jpg)
Switchingcolumnsandrows
Whencreatingareport,thelocationsoffieldscanbechangedbydraggingthemtoadifferentarea.
Inthefollowingexample,wecreatedapivottablethatshowstheaveragemonthlysalaryforeachcityandrole:
Thefollowingtableappears:
However,sincethenumberofcitiesissmallcomparedtothelargenumberofroles,readingthedatabecomesquitedifficult.Tomakeitclearer,wecanswitchtherowsandcolumnsofthepivottablearound.
Allwehavetodoistodragthe“Role”fieldtotheCOLUMNSandthe“City”fieldtotheROWS:
Thefollowingpivottablewillappear:
![Page 71: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/71.jpg)
Notethatbothtablesdisplaythesamedata.Selecttheonewhichismorereadableforyou.
![Page 72: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/72.jpg)
Addingcolumnsorrows
1.AdditionalcolumnsorrowscanbeeasilyaddedtothereportbydraggingtheappropriatefieldstotheCOLUMNSorROWS.
Inthisexample,weaddedthe“Gender”fieldtotheROWS,belowthe“Role”field:
Thefollowingtabledisplaystheaveragesalaryineachcity,dividedbyrole,thengender:
WithintheROWSarea,wecandragthe“Role”fieldbelowthe“Gender”field.
Wegetthefollowingpivottable,whichdisplaystheaveragesalaryineachcity,dividedbygender,thenrole:
![Page 73: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/73.jpg)
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:
![Page 74: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/74.jpg)
6.Atthesecondstage,gotothe“PAGELAYOUT”tab ”PrintTitles”:
7.Selectthetabletitlesas“Rowstorepeatattop”:
Now,thedataforeachdepartmentwillbeprintedonaseparatepage.
![Page 75: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/75.jpg)
Exercises
1.CreateapivottablereportdisplayingthecarcolorintheROWSandthegeartypeintheCOLUMNS.Calculatetheaveragedesiredprice.
2.SwitchtheROWSandCOLUMNS.
3.CreateapivottablereportdisplayingthecarcolorintheROWSandthecountryofmanufactureintheCOLUMNS.Calculatethenumberofcars.
4.SwitchtheROWSandCOLUMNS.
![Page 76: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/76.jpg)
![Page 77: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/77.jpg)
FormattingPivotTables
![Page 78: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/78.jpg)
StructureFormat
Pivottableshaveformatdefaults,setbythesoftware.However,wecancustomizethetablesaccordingtoourneeds.
SubtotalsIfthepivottablehasatleast2fieldsintheROWSorintheCOLUMNS,datainthepivottablewillappearwithsubtotalsaftereachchangeofaniteminafield,asshowninthefollowingfigure:
Subtotalformat:
Wecanformatthesubtotalsinordertodistinguishmoreeasilybetweenthemandthedata.
Formattingcanbedonebycellcolor,fontcolor,highlights,fontsize,etc.:
Placethecursorinthetotalrow,ononeofthetitles.Inourexample,youcanplacethecursoron“DetroitTotal”.
Movethecursortotheleftuntilitturnsfromthe shapetotheshape.
Theentiretotalrowisnowselected,asshowninthefollowingfigure:
![Page 79: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/79.jpg)
Thecellscannowbeformattedaswewish(cellfilling,color,size,etc.).
RemovingorChangingthesubtotalslocation:
1.Placethecursoronthepivottable.
2.Selectthe“DESIGN”tab “Subtotals”:
3.Selectthedesiredoption.
SortingPivottablesarecreatedalreadysortedinascendingorder(alphabeticalornumeric).
Thesortingordercanbechangedbyfollowingthestepsbelow:
1.Clickonthearrowatthetopofthefield.
![Page 80: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/80.jpg)
2.Selectthedesiredtypeofsorting:
Furthermore,thepivottableenablesustoarrangethedatabymovingthefieldstothedesiredpositions:
1.Right-clickontheitemtoberepositioned.
2.Fromthedrop-downlist,select“Move“.
3.Choosethenewposition.
Notethatthefieldscanalsobedraggedwithinthepivottableinordertochangetheirlocation.
FilteringBydefault,thepivottabledisplaysallthedatafieldsdraggedintoit.Ifwewantto
![Page 81: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/81.jpg)
viewonlysomeofthedata,weusetheFilteroption:
1.Clickthearrowatthetopofthefield.
2.Selectthevaluestobedisplayed:
3.Click
4.Thefollowingpivottablewillappear:
Notethatthefiltericonhaschangedfrom to ,inordertoindicatethatthedatahasbeenfiltered.
Removingfiltering:
1.Clickonthefilteringarrow.
2.Select“ClearFilter”:
![Page 82: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/82.jpg)
ValueFilters:
Pivottableshaveotherwaystofilterdata,suchasdisplayingvaluesthataregreaterthan,orlessthan,acertainvalue:
1.Clickthefilteringarrow.
2.Select“ValueFilter”.
3.Selectthedesiredfilteringoption:
LabelFilters:
Excelenablesustofiltertextualdatainmanydifferentways,forexample–by
![Page 83: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/83.jpg)
displayingtextualdatathatbeginswith,endswith,contains,ordoesnotcontaincertaincharacters.
1.Clickonthefilteringarrow .
2.Select“LabelFilter”.
3.Selectthedesiredfilteringoption:
Inthefollowingexample,wewanttoseeallthecitynamesbeginningwiththeletterN:
Thefollowingpivottablewillappear:
Removingdatawhichhasbeendeletedfromthefilterlist
Likeanydatabase,thetablesonwhichthepivottablesarebased,aresubjecttochange.Oneofthemostcommonchangesisthedeletionofdata.
![Page 84: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/84.jpg)
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
![Page 85: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/85.jpg)
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:
![Page 86: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/86.jpg)
7.Oncewehaveselectedtheitemswewant,onlythefilteredrecordswillbedisplayed:
Note:Toselectmultiplefilters,useCtrl:
ManipulatingSlicers1.Selectaslicer.
2.The“Slicers”tabwillappear.
Changingtheslicer’sname
1.Thecaptionthatwillappearasthewindowtitlecanbechanged:
![Page 87: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/87.jpg)
Creatingconnections:
Ifthereareanumberofpivottablesbasedonthesamedata,theslicerscanbelinkedtomorethanonetable:
1.Click
2.Thefollowingwindowwillappear:
3.Selectthedesiredtables.
Changingtheslicer’sappearance:
1.Differentstylescanbeselectedfortheslicers:
2.Thenumberofcolumnsintheslicerwindow,andthesizeofthebuttonscanbeset:
Youcanseethisinthefollowingfigure:
![Page 88: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/88.jpg)
RemovingitemswhichweredeletedfromthedatasourceTheslicerscancontainitemswhichweredeletedfromthedatasourceaftertheywerecreated.
Theseitemscanberemovedasfollows:
1.SelecttheSlicerswindow.
2.Under”SLICERTOOLS” ”OPTIONS”tab,click
3.Thefollowingwindowwillappear:
4.Uncheck“Showitemsdeletedfromthedatasource”.
![Page 89: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/89.jpg)
TheTimeline
AnothertoolthatenablesustofilterthedataistheTimeline,whichhelpsusfiltertherecordsbydates.
InsertTimeline:1.Createapivottable.
2.Underthe“ANALYZE”tab,select“InsertTimeline”:
3.Awindowthatcontainsdatefieldswillappear:
4.Selectthedesiredfield.
5.Click
6.Thetimelinewillappear:
7.ChoosethedesiredperiodbyselectingitontheTimeline:
![Page 90: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/90.jpg)
Inordertoselectacontinuousperiod,simplydragthecursoroverit:
Changingthetimeperiod:1.Clickonthearrow,asshowninthefollowingimage:
2.Selectthedesiredtimeperiod.
ChangingtheTimeline’sappearance:DifferentstylescanbeselectedfortheTimeline:
Selectthe“OPTIONS”tabunderthe“TIMELINETOOLS”tab:
ChangetheTimelinecaption:
Selectastyle:
![Page 91: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/91.jpg)
SelecttheTimelinesize:
(YoucanalsochangethesizeoftheTimelinebydraggingitsborders.)
ShoworhidetheHeaders,SelectionLabelandTimeLevel,ortheappearanceofthescrollbar:
Connectittonumerouspivottablesbypressingthe button,andselectingthedesiredpivottables.
![Page 92: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/92.jpg)
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.
![Page 93: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/93.jpg)
![Page 94: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/94.jpg)
DesignTabPivottablescanbeformattedbyusingthecellformattingoptions,andalsobyvariousothertools.
Formattingcommandscanbefoundinthe“PIVOTTABLETOOLS” ”DESIGN”tabandaredividedintodifferentcategories:
![Page 95: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/95.jpg)
Layoutcategory
Subtotals-thisenablesustocancelsubtotals,ortoshowthemaboveorbelowthegroup.
Totals-thisenablesustoshoworhidethetotalsinROWSandCOLUMNS.
Reportlayout-thisenablesustoviewthepivottableinacompact,outlineortabularform.Wecanalsouseittorepeatorcanceltherepetitionoflabels.
Blankrows-thisenablesustocreateorremoveblankrowsbetweenitems.
![Page 96: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/96.jpg)
Pivottablestyleoptions
RowHeaders-thisenablesustodisplayorcanceltheboldingofitems.
ColumnHeaders-thisenablesustodisplayorcanceltheboldingoffields.
BandedRow/BandedColumn–thispaintstherowsorcolumnsalternately.
![Page 97: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/97.jpg)
Quickdesignstylesofpivottable
Thisenablesustochoosebetweendifferentstyles,asshowninthetab.
ChoosinganewstyleTocreateanewdesignforthepivottable:
1.Click“NewPivotTableStyle”:
2.Thefollowingwindowwillappear:
3.Namethenewlydefinedstyleanddesignthetablecomponentsasdesired.
4.Thenewstylewillappearatthetopofthecustomizationlist,inthegallery.
![Page 98: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/98.jpg)
Exercises
1.Displaytheaveragesaleprice,dividedbymake,colorandcountryofmanufacture.
2.Cancelthesubtotalsandthegrandtotal.
3.Switchthereportlayouttoatabularform.
![Page 99: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/99.jpg)
![Page 100: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/100.jpg)
ChartsSofar,wehavelearnedhowtocreatepivottablestodisplaydataindifferentintersections.Excelalsoenablesustocreatechartsbasedonthepivottable,foramorevisualdisplayofthedata.Achartcanbeaddedtoanexistingpivottable,orcreatedatthesametimeasapivottable.
![Page 101: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/101.jpg)
Addingacharttoanexistingpivottable
1.Placethecursorinthepivottable.
2.Selectthe“ANALYZE”tab “PivotChart“:
3.Thefollowingwindowwillappear:
4.Selectthedesiredcharttype.
5.Click
6.Theselectedchartwillappear:
Note:Youcanalsocreateachartbasedonthepivottable,usingthe“INSERT”tabtoselectthedesiredcharttype.
![Page 102: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/102.jpg)
Simultaneouslycreatingapivottableandachart
Placethecursoronthedatatable.
Selectthe“INSERT”tab.
ClickthearrowbelowthePivotChartbutton.
Select“PivotChart&PivotTable“:
Thepivottableandthepivotchartwillappearinthesamewindow:
DraggingthefieldstothedesiredareaswilldisplaytheminthePivotTableandthePivotChartsimultaneously.
![Page 103: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/103.jpg)
Exercises
1.Displaytheaveragesalepricebymakeandcolor.
2.Createachartbasedonthepivottableyoucreated.
![Page 104: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/104.jpg)
![Page 105: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/105.jpg)
TipsandTricksUsingRecommendedPivotTables
Excel2013hastheabilitytoanalyzethedata,andrecommendthemostsuitablepivottablesforit.
Tousethisfeature,locatethecursorinacellinyourdatatable,andunderthe
“INSERT”tab,click
Excelwillsuggestalistofpivottables:
Selecttheonethatsuitsyourneeds.
![Page 106: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/106.jpg)
Quicklycreatingapivottable
Apivottablecanbecreatedquicklybycopyinganexistingpivottable,pastingandcustomizingit,accordingtoyourneeds.
![Page 107: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/107.jpg)
Obtainingthedatasource
Apivottableiscreatedfromrawdatatodisplaygroupeddata.
Thereverseactioncanbeperformedaswell:displayingtheconstitutingrecordsofaniteminthepivottable(Drilldown).
Inthisexample,thepivottabledisplaysthenumberofemployeesineachrole:
Inordertoseetheoriginalrecords,double-clickthedesiredvalue.Intheexampleabove,double-clickingonthenumber3nextto“Accountant”willdisplayanewworksheetcontainingtherecordswhichcreatedtheresult3.
![Page 108: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/108.jpg)
CreatingTabsfromFilters
Apivottableenablesustosplittheprimarypivottabledataintotabs,byusingthefilteringfield.
Forexample,fromapivottablecountingthenumberofemployeesineachrole,wecancreateatabforeachsection,asexplainedbelow:
1.Createapivottable.
2.DragtherelevantfieldintotheFILTER,asshowninthefollowingfigure:
3.Selectthe“ANALYZE”tab ”Options”button.
4.Select“ShowReportFilterPages“:
5.Thefollowingwindowwillappear:
![Page 109: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/109.jpg)
6.Selectthefilterwiththedatatobedisplayedinthetabsandclick
NowthefilecontainstabsbythenamesofitemsdraggedtotheFILTER.Eachofthetabsconsistsonlyoftherelevantdata:
![Page 110: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/110.jpg)
FilterLocations
Bydefault,thepagefiltersaredisplayedinonecolumn,asshowninthefollowingexample:
Excelenablesustoseparatethefiltersintoseveralcolumns,byfollowingthestepsbelow:
1.Clickthe“ANALYZE”Tab.
2.Click“Options”:
3.Thefollowingwindowwillappear:
![Page 111: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/111.jpg)
4.Inthe“Layout&Format”tab,change“Reportfilterfieldspercolumn”tothedesiredvalue.
5.Thefiltersintheresultingpivottablearenowdividedintocolumns:
![Page 112: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/112.jpg)
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).
![Page 113: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/113.jpg)
Sortingthefieldlist
Whencreatingapivottable,theFieldListappearsinthesameorderasinthedatatable:
Wecansortthefieldlistalphabetically,asfollows:
1.Selectthe“ANALYZE”tab ”Options”button.
2.Inthe“Display”Tab,select“SortAtoZ“:
3.Thefieldlistwillbesortedaccordingly:
![Page 114: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/114.jpg)
![Page 115: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/115.jpg)
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”:
![Page 116: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/116.jpg)
4.Thefollowingpivottablewillappear:
Nowwecanusethetableasasourceofdesiredcalculations.
![Page 117: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/117.jpg)
![Page 118: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/118.jpg)
AdvancedUsesofPivotTablesFindinguniquerecordsandduplicaterecords
Apivottableenablesustofindduplicaterecordsinadatabase.
Inthisexample,wehaveobtainedlistsofregisteredmembersoftwopoliticalparties,andwewanttocheckiftherearepeoplewhoareillegallyregisteredtobothparties,andwhichpeopleareregisteredtoonlyoneparty.
WehavecreatedapivottableinwhichwedraggedtheIDrecordstotheROWSandthepartyregistrationfieldtotheCOLUMNS.WealsodraggedtheIDfieldtotheVALUES:
Thefollowingpivottableappears:
![Page 119: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/119.jpg)
Fromthepivottableabove,itappearsthattheownerofID1650wasregisteredtobothpartiesAandB,whiletheownerofID1213wasregisteredtopartyAonly.
![Page 120: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/120.jpg)
HumanResourcesPlanning
ThedirectoroftheHumanResourcesDepartmentconductsanannualstaffingplan.Heisprovidedwithdepartmentalrequestsaboutspecificneeds,movespeoplefromonedepartmenttoanother,outsourcesandfiresemployees,etc.
Therawdataisshowninthefollowingtable:
Asshowninthedatatable,10employeesaretobetransferredfromthePurchaseDepartmenttotheSalesDepartment,and15employeesaretobetransferredfromthePurchaseDepartmenttotheHumanResourcesDepartment.
Inordertoknowmoreaboutthetransfersbetweendepartments,wecancreateapivottable,wherethesourcewillappearinrowsandthetargetwillappearincolumns.Thenumberofemployeeswillberepresentedinthevalues:
Nowitiseasiertoreviewthetransfersofemployeestoandfromdifferentdepartments.
Forexample,itiseasytoseethat25employeesmovedfromtheSalesDepartmenttotheHumanResourcesdepartment,20employeesmovedfromtheSalesDepartmenttothePurchaseDepartmentand45employeeswerefiredfromtheSalesDepartment.
Wecanalsoseethat60employeeswererecruitedfortheSalesDepartment,65employeeswererecruitedfortheHumanResourcesdepartmentand55were
![Page 121: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/121.jpg)
recruitedforthePurchaseDepartment.
Fromthetotalrow,welearnthat105employeesmovedtotheSalesDepartmentand90arenolongeremployedatthefactory.
![Page 122: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/122.jpg)
CashFlow
Abusinessownerentershiscustomers’paymentdataintotheExceltablebelow:
Thetabledisplaysthecustomer’sname,dateofpayment,expectedpayment,andtheactualpayment.ThecashflowcanbederivedfromthedatatablebydraggingthedateofpaymenttotheROWSandtheexpectedpaymenttotheVALUES:
Fromthepivottablewecanconcludethaton3/1/2014,asumof2,000USDisexpectedtobedepositedinthebusinessaccount.
Thesamedatatablecanbeusedtoviewthestatusofcustomerpayments,withdetailsoftheexpectedpaymentandtheactualpayment.
Thebalanceduewascalculatedusing“CalculatedField”
![Page 123: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/123.jpg)
ProfitandLoss
Afactorymanagerentersthefactoryrevenueandexpensestothefollowingtable:
Inordertocreateaprofitandlossstatement,wehavetocreatethefollowingpivottable:
InordertoviewtheProfit&LossStatement,wehavetore-orderthedatabydraggingtherevenuedatafromsalesandplacingitbeforetheexpensesdata:
Wecannowuse“CalculatedItem”inordertoperformthecalculationsofgrossprofit,operatingprofit,andnetprofit:
![Page 124: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/124.jpg)
Thefollowingpivottableappears:
Nowallwehavetodoistomovethecalculateditemstotheappropriateposition:
Thepivottableaboveshowsthefactory’sProfit&LossStatement.
Notethattheboldcolumnsaretheresultofacalculateditemfromdataintheoriginalpivottable.
![Page 125: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/125.jpg)
![Page 126: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/126.jpg)
Appendix
![Page 127: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/127.jpg)
Selectingdatafromotherfiletypes
Youmaysometimesneedtocreatepivottablesusingdatafromotherfiletypes,suchasMicrosoftAccess,SQLdatabase,etc.
Inordertocreatethedatatable,wefirstneedtoconnecttothedatabaseasfollows:
1.Inthe“INSHeadersERT”tab,select“PivotTable“.
2.Thefollowingwindowwillappear:
3.Clickthe button.
4.The“ExistingConnections”windowwillappear:
![Page 128: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/128.jpg)
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.
![Page 129: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/129.jpg)
PivotTablesWizard
InolderversionsofExcel,userscouldbuildthepivottablestepbystep,usingthePivotTablesWizard.
Thewizardincludedotheroptionsaswell,suchas“multipleconsolidationranges”,whichdonotappearintheRibbonVersions(Excel2007andhigher).
Userswhoareaccustomedtousingthewizard,orusingtheoptionsavailable,canaddittotheQuickAccessToolbar:
1.ClickthearrowintheQuickAccessToolbar.
2.Select“MoreCommands”:
3.Thefollowingwindowwillappear:
![Page 130: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/130.jpg)
4.From“CommandsNotintheRibbon“,select“PivotTableandPivotChartWizard”.
5.Click
6.Click
7.ThePivotTablesWizardwillbeaddedtotheQuickAccessToolbar:
![Page 131: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/131.jpg)
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
![Page 132: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/132.jpg)
5.Select“Createasinglepagefieldforme”:
6.Click
7.Thefollowingwindowappear:
8.Selectthedesiredrangeinthefirstworksheetandclick
9.Repeattheoperationforeachoneoftheranges:
10.Click
![Page 133: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/133.jpg)
11.Selectthedesiredlocationforthepivottable:
12.Click
13.Anewpivottablethatcontainsthedatafrombothtableswillbecreated.
14.Pleasenotethatthepivottablecreatedwillbeverylimitedcomparedtoapivottablethatwascreatedfromasingletable.
![Page 134: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/134.jpg)
![Page 135: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/135.jpg)
TheDataModelSofar,wehavedealtwithcreatingpivottablesbasedonasingledatatable.
Whenwewantedtocreateapivottablefrommultipletables,wehadtomergethemintoonetable,usuallyusingtheVLOOKUPfunction.
Excel2013introduces,forthefirsttime,theuseofthe‘DataModel’,atermthatcamefromthefieldofbusinessintelligence(BI)andallowsustocreatepivottablesfrommorethanonetable.
Sincewearetalkingaboutmultipletablesandtherelationshipsbetweenthem,thefirstpartofthischapterwillbededicatedtoabriefacquaintancewithsomeimportanttermsregardingdatabases.
Inthesecondpartofthechapter,wewilllearnhowtocreateapivottablethatisbaseduponmultipletables.
![Page 136: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/136.jpg)
Basictermsindatabases
RelationshipsInordertounderstandwhatrelationshipsare,wewillusethe“Employees”database.However,thistimethedatacomesfromtwodifferenttables(asopposedtotheformerchapters,whenthedatacamefromasingletable):
1.Employeestable-atablethatcontainsthedataoftheemployeesandthedepartmenttowhichtheybelong:
2.DepartmentsandSectionstable-thisservesasanauxiliarytable,andspecifiestowhichdepartmenteachsectionbelongs:
IfwewantedtocreateapivottableinExcel2010whichwillshowthetotalsalariesineachsection,wewouldhavetoimportthesectioncolumnfromtheauxiliarytabletoourPrimaryDataTableusingVLOOKUP,andonlythencreateapivottablebasedonthecombinedtable.
Excel2013displaysasignificantimprovement,whichisbasedontheconceptoflinkingtwotables,usingacommonfield.
![Page 137: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/137.jpg)
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.
![Page 138: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/138.jpg)
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.
![Page 139: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/139.jpg)
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.
![Page 140: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/140.jpg)
Creatingapivottablebasedontwoormoretables
Thecreationofapivottablethatisbasedonmorethanonetableisdoneasfollows:
1.Createdynamictablesfromalltherelevanttablesinyourworkbook(seehere).
2.Createanemptypivottablefromoneofthetables,byusingtheDataModel.
3.Createtherelationships.
4.Dragthefieldstothedesiredpivottableareas.
![Page 141: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/141.jpg)
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:
![Page 142: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/142.jpg)
Inthe“All”tabwecanseethatExcelhasautomaticallycreatedalistofallthedynamictablesthatareinourworkbook.
Clickingonthetrianglearrownexttothenameofthetablewillexpandittodisplaythetablefields:
![Page 143: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/143.jpg)
CreatingRelationships
Beforedraggingthefieldstotherespectiveareas,wehavetocreatearelationshipbetweenthetwotables:
Formingarelationship(OnetoMany):Afterwehaveidentifiedthekeys,weneedtoconnectthetwotablesbyformingrelationsfromtheforeignkeytotheprimarykey(inotherwords,fromthe“Many”sidetothe“One”side).Inourcase,fromthedepartmentscolumnintheemployeestabletothedepartmentscolumninthedepartmentstable.
Formingarelationship(OnetoOne):Ifourdatatablesarebasedonaonetoonerelationship,forexample,atablethatcontainstheemployees’numbersandtheirsalaries:
Andatablethatcontainsotherdetailsabouttheemployees:
![Page 144: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/144.jpg)
Thecreationofarelationshipwillbefromthekeyinthetablethatcontainstheitemsthatshouldbecalculated(inourcase–thesalary),tothekeyinthesecondtable.
![Page 145: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/145.jpg)
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
![Page 146: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/146.jpg)
andthedepartmentcolumn(whichservesasaprimarykeyinthistable):
9.Click
![Page 147: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/147.jpg)
Draggingthefields
Aftercreatingtherelationshipsbetweenthe2tables,wecanstartdraggingthefieldstothepivottable.
Inourcase,wewilldragthesectionoutofthedepartmentstabletotheROWSandthesalariesoutoftheemployeestabletotheVALUES:
Wewillgetthesumofallmonthlysalariesineachsection,eventhoughthedataisfromtwodifferenttables!
Andwhatifwedidn’tcreatetherelationshipsinadvance?
Inthefollowingexamplewedraggedthefieldswithoutcreatingtherelationsbefore,andwecanseetwothings:
1.Thetotalpayrollineverysectionisthesame(thisisactuallythesumofthetotalpayroll,withoutdividingintosections):
2.Ayellowmessagebarpromptsustocreaterelationships:
![Page 148: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/148.jpg)
Clickingon willopentherelationshipwindow,andtheircreationwillfixthevaluesinthetable.
![Page 149: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/149.jpg)
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”:
![Page 150: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/150.jpg)
6.Click
7.Apivottablethatshowsthenumberofprofessionsineachsectionwascreated:
8.Wecanseethatthereare6differentrolesinmanagement,4inmanufacturingand3differentrolesinsalesandmarketing.
![Page 151: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/151.jpg)
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.
![Page 152: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/152.jpg)
![Page 153: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/153.jpg)
EpilogueDearReaders,
Greateffortsweremadewritingthisbook,inordertoconveytoyoutheknowledgewehavegained.
However,ifthereareissuesnotexplainedhereinthebook,andwhichareofgreatimportancetoyou,pleaseletusknowbyemail,sowecanmakeevenmoreefforttointegratethemintothenextedition.
ThankYou.
MaayanPolegandSahronBarak
MaayanPoleg:[email protected]
![Page 154: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/154.jpg)
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.
![Page 155: Managers, financiers and other Excel usersdl.booktolearn.com/ebooks2/computer/office/9781511954877...Introduction This book teaches experienced Excel users how to use pivot tables,](https://reader035.vdocuments.us/reader035/viewer/2022062604/5f7c765e9a58817000713436/html5/thumbnails/155.jpg)
ThebookisdesignedforExcelusers,whodonothaveprogrammingbackgrounds,buthaveadesiretomakethemostofthesoftware.Forthisreason,thebookhasbeenwritteninaneverydaylanguage,reducingtheuseoftechnicalterms.
YoucanorderthebookviaAmazonatthislink