DataAnalyticsTutorial:
TransactionAnalysisUsing
ExcelPivotTablesandCharts
CityofSomerville,MAdataset
Welcometothedataanalyticstutorialcoveringtransactionanalysisusingpivottablesand
chartsinExcel.
1
CityofSomerville,MAdataset
• Usingreal‐lifecheckbookdatafromCityof
Somerville,MA,for2013– 2016
• Inthistutorial,weareusingasmall,22‐
recorddataset
• Fortheactualactivity,youwillbeusingthe
fulldatasetsoanswerswillbedifferentbut
theprocesswillbesimilar
Inthisactivity,weareusingthereal‐lifecheckbookdatafromtheCityofSomerville,
Massachusetts,for2013– 2016.Forthistutorialonly,weareusingasmall,22‐recorddata
set.Fortheactualactivity,youwillbeusingthefulldatasetsoyouranswersfortheactivity
requirementswillbedifferent– buttheprocesswillbesimilar.
2
Pivottablesandpivotcharts
• UsingOffice365ExcelinWindowsinthis
tutorial
• OtherversionsofExcelmaybeslightly
different
• Maybemanywaysofaccomplishingthe
samething– justpresentingonewayhere
Forthistutorialonpivottablesandpivotcharts,wewillbedemonstratingusingOffice365
ExcelforWindows.OtherversionsofExcelmaybeslightlydifferent.Alsonotethatthere
maybemanywaysofaccomplishingthesamething– wearejustpresentingonewayhere.
3
StartbyopeningExcelworkbook
StartthisactivitybyopeningtheExcelworkbookcontainingthedataset.
4
Generalinstructions
Foreachoftherequirements,createanew
pivottableinanewworksheet.Nameeach
newworksheetas“Req1,”“Req2,”etc.
Formatthedollaramountsineachpivottable
orpivotchartusingtheaccountingformat
withzerodecimalplaces.Formatnon‐
currencynumbersineachpivottableorpivot
chartusingthenumberformatwithzero
decimalplaces.
Ingeneral,foreachoftherequirementsinthisactivity,createanewpivottableina
newworksheet.Nameeachnewworksheetas“Req1,”“Req2,”etc.Formatthe
dollaramountsineachpivottableorpivotchartusingtheaccountingformatwith
zerodecimalplaces.Formatnon‐currencynumbersineachpivottableorpivot
chartusingthenumberformatwithzerodecimalplaces.
5
Requirement1
From2013– 2016,whatwasthe
totalspendingineachofthefour
calendaryears?
Requirement1asks“From2013– 2016,whatwasthetotalspendingineachofthefour
calendaryears?”
6
Req1:Totalspending2013‐ 2016
#1:Clickanywhereinthedatainthe
Dataworksheet
ThefirststepistoclickanywhereinthedataintheDataworksheet.
7
Req1:Totalspending2013‐ 2016
#2:ClickonInserttab
Step2istoclickontheInserttabintheribbon.
8
Req1:Totalspending2013‐ 2016
#3:ClickonPivotTable
Step3istoclickonPivotTableintheribbontoinsertaPivotTable.
9
Req1:Totalspending2013‐ 2016
#4:ClickonOK(usedefaults)
Excelwillpresentyouwithaboxcontainingpivottableoptions.Acceptthedefaultsand
clickonOK.
10
Req1:Totalspending2013‐ 2016
#5:Right‐clickthe
worksheetnameto
renameitas“Req1”
Beforewegoanyfurther,forStep5,right‐clicktheworksheetnametabandrenameit“Req
1.”Thatwilljusthelptokeeptrackofourpivottables.
11
Req1:Totalspending2013‐ 2016
#6:Drag“CheckDate”inthePivotTable
FieldspaneldowntotheColumnbox(it
willexpandtoincludeYears,Quarters,
andCheckDate.Next,wewillbe
draggingQuartersandCheckDateout
oftheColumnboxtoleaveonlyYears)
ForStep6,drag“CheckDate”inthePivotTableFieldspaneldowntotheColumnbox(itwill
expandtoincludeYears,Quarters,andCheckDate.OnceIdidthis,IdraggedQuartersand
CheckDateoutoftheColumnboxtoleaveonlyYears.IstillhaveQuartersandCheckDate
displayedonthisslide,butdrageachofthemuptotheFieldboxtoremovethemfromthe
Columnsbox– thosetwofieldswillbegoneonthenextslide.)
12
Req1:Totalspending2013‐ 2016
Noticethecolumnlabels
nowappearinthepivot
table
Noticethatthecolumnlabelsfortheyears2013through2016nowappearinthepivot
table– andthatQuartersandCheckDatearenolongerintheColumnboxbecausewe
draggedthemupthefieldbox.
13
Req1:Totalspending2013‐ 2016
Bytheway,ifthispanel
everdisappears,youcan
bringitbackbyclicking
anywhereinthepivot
tableyouhavecreated
Bytheway,ifthePivotTableFieldspaneleverdisappears,youcanbringitbackbyclicking
anywhereinthepivottableyouhavecreated.
14
Req1:Totalspending2013‐ 2016
#7:Drag“Amount”inthe
PivotTableFieldspanel
downtotheValuesbox
ForStep7,dragthe“Amount”fieldfromthePivotTableFieldspaneldowntotheValues
box.
15
Req1:Totalspending2013‐ 2016
Tochangetheamountsinthe
pivottabletobethesumrather
thanthecount,clickon“Count
of…”inthePivotTableFields
panel,selectValueFieldSettings,
andthenselectSumandclickOK.
Bytheway,watchtomakesurethatthefieldintheValuesboxsays“Sumof…”.Ifitsays
“Countof…”,thenyouneedtochangeittoSUM.Tochangetheamountsinthepivottable
tobethesumratherthanthecount,clickon“Countof…”inthePivotTableFieldspanel,
selectValueFieldSettings,andthenselectSumandclickOK.Watchinfuturepivottables
thatyouaregettingsumratherthancount– andfixitjustlikehereifyouneedtochange
it.(Gettingacountratherthanasumistheresultofhavingatleastoneblankinyourdata
set.)
16
Req1:Totalspending2013‐ 2016
Nowthepivottablehas
transactionsamounts
Nowthepivottablehasthesumsofthetransactiondollaramounts.
17
Req1:Totalspending2013‐ 2016
#8:Select
amountsinpivot
tableandright‐
clicktoselect
ValueField
Settings
InStep8,wearegoingtoformatthedata.Selecttheamountsinthepivottableandright‐
clicktoselectValueFieldSettings.
18
Req1:Totalspending2013‐ 2016
#9:Select
Number
Format
InStep9,clickonNumberFormat.
19
Req1:Totalspending2013‐ 2016
#10:Select
Accounting
formatwithzero
decimalplaces
andclickOK
InStep10,selecttheaccountingformatwithzerodecimalplacesandclickOK.
20
Req1:Totalspending2013‐ 2016
That’sit– wecanseethe
spendingforSomerville
foreachofthefouryears
inthedataset.
That’sitforrequirement1– wecanseethespendingforSomervilleforeachofthefour
yearsinthedataset.
21
Requirement2
Ineachoftheyears2013‐ 2016,
howmuchwasspentineachofthe
threecategoriesofgovernment
(Education,GeneralGovernment,
andPublicWorks)?
Requirement2asksthequestion“Ineachoftheyears2013‐ 2016,howmuchwasspentin
eachofthethreecategoriesofgovernment(Education,GeneralGovernment,andPublic
Works)?”
22
Req2:Spendingbycategory2013‐ 2016
#1:Clickanywhereinthedatainthe
Dataworksheet
ThefirststepistoclickanywhereinthedataintheDataworksheet.
23
Req2:Spendingbycategory2013‐ 2016
#2:ClickInsert,thenInsertPivotTable,
andthenclickOK
ForStep#2,toinsertapivottable,clickInsert,thenInsertPivotTable,andthenclickOK.
24
Req2:Spendingbycategory2013‐ 2016
#3:Right‐clickthe
worksheetnameto
renameitas“Req2”
ForStep#3,right‐clicktheworksheetnametorenameitas“Req2.”
25
Req2:Spendingbycategory2013‐ 2016
#4:Drag“CategoryofGov”inthe
PivotTableFieldspaneldowntothe
Rowsbox,“Amount”downtothe
Valuesbox,and“Years”downtothe
Columnsbox
InStep4,drag“CategoryofGov”inthePivotTableFieldspaneldowntotheRowsbox,
“Amount”downtotheValuesbox,and“Years”downtotheColumnsbox.
26
Req 2:Spendingbycategory2013‐ 2016
Remembertocheck– isthe
Valuesfield“Sumof…”or“Count
of…”?Tochangetheamountsin
thepivottabletobethesum
ratherthanthecount,clickon
“Countof…”inthePivotTable
Fieldspanel,selectValueField
Settings,andthenselectSumand
clickOK.
Remembertocheck– istheValuesfield“Sumof…”or“Countof…”?Tochangethe
amountsinthepivottabletobethesumratherthanthecount,clickon“Countof…”inthe
PivotTableFieldspanel,selectValueFieldSettings,andthenselectSumandclickOK.
Watchinfuturepivottablesthatyouaregettingsumratherthancount– andfixitjustlike
hereifyouneedtochangeit.
27
Req2:Spendingbycategory2013‐ 2016
#5:Selecttheamountsinpivottable
andright‐click.SelectValueField
SettingsandthenNumberFormat.
SelectAccountingFormatandzero
decimalplaces.ClickOK.
ForStep5,selecttheamountsinpivottableandformatastheaccountingformatwithzero
decimalplaces.
28
Req2:Spendingbycategory2013‐ 2016
That’sit– wecanseewhatwas
spentineachcategoriesof
governmentforSomervilleforeach
ofthefouryearsinthedataset.
That’sit– wecanseewhatwasspentineachcategoryofgovernmentforSomervillefor
eachofthefouryearsinthedataset.
29
Requirement3
In2016,whichaccount(usethe
field“AccountDesc”forthisanswer)
wasthelargestintheGeneral
Governmentcategory?
Requirement3reads“In2016,whichaccount(usethefield“AccountDesc”forthisanswer)
wasthelargestintheGeneralGovernmentcategory?”
30
#1:Clickanywhereinthedatainthe
Dataworksheet
Req3:In2016,largestaccountforGeneral
Governmentcategory
ThefirststepistoclickanywhereinthedataintheDataworksheet.
31
Req3:In2016,largestaccountforGeneral
Governmentcategory
#2:ClickInsert,thenInsertPivotTable,
andthenclickOK
ForStep#2,toinsertapivottable,clickInsert,thenInsertPivotTable,andthenclickOK.
32
Req3:In2016,largestaccountforGeneral
Governmentcategory
#3:Right‐clickthe
worksheetnameto
renameitas“Req3”
ForStep#3,right‐clicktheworksheetnametorenameitas“Req3.”
33
Req3:In2016,largestaccountforGeneral
Governmentcategory
#4:Drag“CategoryofGov”inthe
PivotTableFieldspaneldowntothe
Columnbox,“AccountDesc”downto
theRowsbox,and“Amount”downto
theValuesbox
ForStep4,drag“CategoryofGov”inthePivotTableFieldspaneldowntotheColumnbox,
“AccountDesc”downtotheRowsbox,and“Amount”downtotheValuesbox.
34
Req3:In2016,largestaccountforGeneral
Governmentcategory
#5:Drag“Years”inthePivotTable
FieldspaneldowntotheFiltersbox
Nowwearegoingtoaddafilterboxtothepivottable.ForStep5,drag“Years”inthe
PivotTableFieldspaneldowntotheFiltersbox.
35
Req3:Spendingbycategory2013‐ 2016
Req3:In2016,largestaccountforGeneral
Governmentcategory
#6:Clickon
“Years”inthe
Filterboxthat
appearsatthetop
ofthepivottable
andselect“2016”
Nowwearegoingtofilterthepivottabletoshowonlyamountsfromexpenditures
madein2016.Clickon“Years”intheFilterboxthatappearsatthetopofthepivot
tableandselect“2016.”
36
Req3:Spendingbycategory2013‐ 2016
Req3:In2016,largestaccountforGeneral
Governmentcategory
Hereweseethepivottablethat
hasbeenfilteredtoshowonly
expendituresmadein2016
Hereweseethatthepivottablehasbeenfilteredtoshowonlyexpendituresmadein2016.
37
Remembertocheck– isthe
Valuesfield“Sumof…”or“Count
of…”?Tochangetheamountsin
thepivottabletobethesum
ratherthanthecount,clickon
“Countof…”inthePivotTable
Fieldspanel,selectValueField
Settings,andthenselectSumand
clickOK.
Req3:In2016,largestaccountforGeneral
Governmentcategory
Remembertocheck– istheValuesfield“Sumof…”or“Countof…”?Tochangethe
amountsinthepivottabletobethesumratherthanthecount,clickon“Countof…”inthe
PivotTableFieldspanel,selectValueFieldSettings,andthenselectSumandclickOK.
38
Req3:Spendingbycategory2013‐ 2016
Req3:In2016,largestaccountforGeneral
Governmentcategory
#7:Right‐clickintheGeneral
Governmentcolumn,selectSort,
andsortfromLargesttoSmallest
Nowlet’ssortthepivottablebytheexpensecategoryintheGeneralGovernmentcolumn
tofindthelargestcategoryofspending.ForStep7,right‐clickintheGeneralGovernment
column,selectSort,andsortfromLargesttoSmallest.
39
Req3:Spendingbycategory2013‐ 2016
Req3:In2016,largestaccountforGeneral
Governmentcategory
#8:Selecttheamountsinpivottable
andright‐click.SelectValueField
SettingsandthenNumberFormat.
SelectAccountingFormatandzero
decimalplaces.ClickOK.
InStep8,wewillformattheamountsinthepivottable.Selecttheamountsinpivottable
andformatastheaccountingformatwithzerodecimalplaces.
40
Req3:Spendingbycategory2013‐ 2016
Req3:In2016,largestaccountforGeneral
Governmentcategory
Hereweseethepivottablethathas
beenfilteredtoshowonlyamounts
from2016ANDithasbeensorted
fromlargesttosmallestaccountsin
theGeneralGovernmentcategory
Hereweseethepivottablethathasbeenfilteredtoshowonlyamountsfrom2016ANDit
hasbeensortedfromlargesttosmallestaccountsintheGeneralGovernmentcategory.
41
Requirement4
In2016,whowasSomerville’s
largestvendorasmeasuredbytotal
dollarsspent?Howmanyseparate
paymentsdidthecitymaketothis
vendor?Whatwastheaverage
amountofeachpaymenttothis
vendor?Whydidthecitypaythis
vendor?
Requirement4reads“In2016,whowasSomerville’slargestvendorasmeasuredbytotal
dollarsspent?Howmanyseparatepaymentsdidthecitymaketothisvendor?Whatwas
theaverageamountofeachpaymenttothisvendor?Whydidthecitypaythisvendor?”
42
#1:Clickanywhereinthedatainthe
Dataworksheet
Req4:In2016,largestaccountforGeneral
Governmentcategory
ThefirststepistoclickanywhereinthedataintheDataworksheet.
43
Req4:In2016,largestaccountforGeneral
Governmentcategory
#2:ClickInsert,thenInsertPivotTable,
andthenclickOK
ForStep#2,toinsertapivottable,clickInsert,thenInsertPivotTable,andthenclickOK.
44
Req4:In2016,largestaccountforGeneral
Governmentcategory
#3:Right‐clickthe
worksheetnameto
renameitas“Req4”
ForStep#3,right‐clicktheworksheetnametorenameitas“Req4.”
45
Req4:In2016,largestaccountforGeneral
Governmentcategory
#4:Drag“Years”inthePivotTable
FieldspaneldowntotheFiltersbox,
“VendorName”downtotheRows
box,and“Amount”downtothe
Valuesbox
InStep4,Drag“Years”inthePivotTableFieldspaneldowntotheFiltersbox,“Vendor
Name”downtotheRowsbox,and“Amount”downtotheValuesbox.
46
Req4:In2016,largestaccountforGeneral
Governmentcategory
#5:Drag“Amount”inthePivotTable
Fieldspanel2moretimesdownto
theValuesboxsothatyousee3
“SumofAmount”(or“Countof
Amount”)fieldsintheValuesbox
ForStep5,drag“Amount”inthePivotTableFieldspanel2moretimesdowntotheValues
boxsothatyousee3“SumofAmount”(or“CountofAmount”)fieldsintheValuesbox.
47
Req4:In2016,largestaccountforGeneral
Governmentcategory
Select2016astheyeartofilterthe
datatoincludeonly2016transactions
Select2016astheyeartofilterthedatatoincludeonly2016transactions.
48
Req4:In2016,largestaccountforGeneral
Governmentcategory
#6:Right‐clickthe2
nd
SumofAmount
columninthePivotTable,select
SummarizeValuesBy,andselectCount
ForStep6,right‐clickthe2ndSumofAmountcolumninthePivotTable,selectSummarize
ValuesBy,andselectCount.(MakesurethefirstAmountcolumnis“Sumof…”– ifnot,
changeittobe“Sumof…”.)
49
Req4:In2016,largestaccountforGeneral
Governmentcategory
#7:Right‐clickthe3
rd
SumofAmount
columninthePivotTable,select
SummarizeValuesBy,andselectAverage
InStep7,right‐clickthe3rdSumofAmountcolumninthePivotTable,selectSummarize
ValuesBy,andselectAverage.
50
Req4:In2016,largestaccountforGeneral
Governmentcategory
#8:Right‐clickacellintheSumof
Amountcolumn,selectSort,andselect
SortLargesttoSmallest
InStep8,right‐clickacellintheSumofAmountcolumn,selectSort,andselectSortLargest
toSmallest.
51
Req4:In2016,largestaccountforGeneral
Governmentcategory
#9:Right‐clickacellintheRowLabels
column,selectExpand/Collapse,and
selectExpand
InStep9,right‐clickacellintheRowLabelscolumn,selectExpand/Collapse,andselect
Expand.
52
Req4:In2016,largestaccountforGeneral
Governmentcategory
#10:Select“ItemClass”astheitemfor
whichtoshowadditionaldetail
InStep10,select“ItemClass”astheitemforwhichtoshowadditionaldetail.
53
Req4:In2016,largestaccountforGeneral
Governmentcategory
#11:Clickthe“+”or“−”symboltothe
rightofeachVendorNametoseethe
ItemClass(es)foreachvendor
ForStep11,clickthe“+”or“−”symboltotherightofeachVendorNametoseetheItem
Class(es)foreachvendor.
54
Req4:In2016,largestaccountforGeneral
Governmentcategory
#12:SelecttheSumandAverage
columnsandformatasAccountingwith
zerodecimalplaces
ForStep12,selecttheSumandAveragecolumnsandformatasAccountingwithzero
decimalplaces.(Right‐clickthenumbers,selectValueFieldSettings,NumberFormat,
Accountingformatwithzerodecimalplaces,andthenclickOK.)
55
Req4:In2016,largestaccountforGeneral
Governmentcategory
Hereweseethesortedpivottable
listingthesums,counts,andaverages
foreachvendor
Herenowweseethesortedpivottablelistingthesums,counts,andaveragesforeach
vendor.
56
Requirement5
Howmuchinexpendituresdid
SomervillehaverelatedtoProperty,
PlantandEquipment(usethefield
“ItemClass”forthisanswer)inthe
GeneralGovernmentcategoryineachof
theyears2013– 2016?Howmuchin
expendituresineachofthoseyears
wererelatedtorepairsand
maintenance?
Requirement5reads“HowmuchinexpendituresdidSomervillehaverelatedtoProperty,
PlantandEquipment(usethefield“ItemClass”forthisanswer)intheGeneralGovernment
categoryineachoftheyears2013– 2016?Howmuchinexpendituresineachofthose
yearswererelatedtorepairsandmaintenance?”
57
#1:Clickanywhereinthedatainthe
Dataworksheet
Req5:HowmuchwasspentrelatedtoProperty,Plant
&Equipmentin2013– 2016?
ThefirststepistoclickanywhereinthedataintheDataworksheet.
58
Req5:HowmuchwasspentrelatedtoProperty,Plant
&Equipmentin2013– 2016?
#2:ClickInsert,thenInsertPivotTable,
andthenclickOK
ForStep#2,toinsertapivottable,clickInsert,thenInsertPivotTable,andthenclickOK.
59
Req5:HowmuchwasspentrelatedtoProperty,Plant
&Equipmentin2013– 2016?
#3:Right‐clickthe
worksheetnameto
renameitas“Req5”
ForStep#3,right‐clicktheworksheetnametorenameitas“Req5.”
60
Req5:HowmuchwasspentrelatedtoProperty,Plant
&Equipmentin2013– 2016?
#4:Drag“CategoryofGov”inthe
PivotTableFieldspaneldownintothe
Filtersbox,“Years”intotheColumns
box,“ItemClass”intotheRowsbox
and“Amount”intotheValuesbox
ForStep4,drag“CategoryofGov”inthePivotTableFieldspaneldownintotheFiltersbox,
“Years”intotheColumnsbox,“ItemClass”intotheRowsboxand“Amount”intotheValues
box.
61
Req 5:HowmuchwasspentrelatedtoProperty,
Plant&Equipmentin2013– 2016?
Remembertocheck– isthe
Valuesfield“Sumof…”or“Count
of…”?Tochangetheamountsin
thepivottabletobethesum
ratherthanthecount,clickon
“Countof…”inthePivotTable
Fieldspanel,selectValueField
Settings,andthenselectSumand
clickOK.
Remembertocheck– istheValuesfield“Sumof…”or“Countof…”?Tochangethe
amountsinthepivottabletobethesumratherthanthecount,clickon“Countof…”inthe
PivotTableFieldspanel,selectValueFieldSettings,andthenselectSumandclickOK.
62
Req5:HowmuchwasspentrelatedtoProperty,Plant
&Equipmentin2013– 2016?
#5:Clickondropdown
arrowinthefiltercell
toselect“General
Government”
ForStep5,clickondropdownarrowinthefiltercelltoselect“GeneralGovernment”.
63
Req5:HowmuchwasspentrelatedtoProperty,Plant
&Equipmentin2013– 2016?
#6:Selecttheamountsinthepivot
tableandformatastheaccounting
formatwithzerodecimalplaces
ForStep6,selecttheamountsinthepivottableandformatastheaccountingformatwith
zerodecimalplaces.
64
Req5:HowmuchwasspentrelatedtoProperty,Plant
&Equipmentin2013– 2016?
Hereweseethesortedpivottable
whichliststheexpendituretotalsfor
eachitemclassfortheGeneral
Governmentcategoryfor2013– 2016
Hereweseethesortedpivottablewhichliststheexpendituretotalsforeachitemclassfor
theGeneralGovernmentcategoryfor2013– 2016.
65
Requirement6
Prepareapivottablethatshowsalinechart
ofSomerville’sexpendituresineachofthe
threecategoriesofgovernment(Education,
GeneralGovernment,andPublicWorks)for
theyears2013– 2016.Prepareapivotchart
usingthelinecharttypeofthisinformation
onthesameworksheet.Analyzethepivot
chartandsummarizethetrends.
Requirement6reads“PrepareapivottablethatshowsalinechartofSomerville’s
expendituresineachofthethreecategoriesofgovernment(Education,General
Government,andPublicWorks)fortheyears2013– 2016.Prepareapivotchartusingthe
linecharttypeofthisinformationonthesameworksheet.Analyzethepivotchartand
summarizethetrends.”
66
#1:Clickanywhereinthedatainthe
Dataworksheet
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
ThefirststepistoclickanywhereinthedataintheDataworksheet.
67
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
#2:ClickInsert,thenInsertPivotChart
andPivotTable,andthenclickOK
ForStep#2,toinsertapivottable,clickInsert,thenInsertPivotChartandPivotTable,and
thenclickOK.
68
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
#3:Right‐clicktheworksheet
nametorenameitas“Req6”
ForStep#3,right‐clicktheworksheetnametorenameitas“Req6.”
69
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
#4:Clickinthepivottable(notthe
chart.)Drag“CategoryofGov”inthe
PivotTableFieldspaneldowntothe
Columnbox,“Years”totheRowsbox,
and“Amount”totheValuesbox
ForStep4,firstclickinthepivottable.Makesureyouarenotclickedinthepivotchart.The
rightsidepanelshouldsay“PivotTableFields.”Drag“CategoryofGov”inthePivotTable
FieldspaneldowntotheColumnbox,“Years”downtotheRowsbox,and“Amount”down
totheValuesbox.
70
Req 6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment
Remembertocheck– isthe
Valuesfield“Sumof…”or
“Countof…”?
Remembertocheck– istheValuesfield“Sumof…”or“Countof…”?
71
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
#5:Right‐clickthechartand
selectChangeChartType
ForStep5,changethecharttypebyright‐clickingthechartandselectingChangeChart
Type.
72
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
Changethecharttypeto“Line”
Onceyouhaveright‐clickedthechartandselectedChangeChartType,select“Line.”
73
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
#7:Selecttheamountsinthepivot
tableandformatastheaccounting
formatwithzerodecimalplaces
ForStep7,selecttheamountsinthepivottableandformatastheaccountingformatwith
zerodecimalplaces.
74
Req6:UseaPivotCharttoanalyzeexpendituresby
categoryofgovernment.
HereweseethePivotTableand
PivotChartofspendingclassifiedby
governmentcategoryfor2013– 2016
HereweseethePivotTableandPivotChartofspendingclassifiedbygovernmentcategory
for2013– 2016.
75
h
o
r
C
o
nt
a
ct
In
fo
Preparedby:
WendyM.Tietz,PhD,CPA,CMA,CSCA
Copyright©2018WendyM.Tietz,LLC
Thatconcludesthisdataanalyticstutorialcoveringtransactionanalysisusingpivottables
andchartsinExcel.Thanksforwatching!
76