Microsoft PowerPoint - LBOH Presentation COVID-19 Webinar #18_PivotTable_May27.pptx

PivotTableDemonstr ation

ToolsforLBOHs

cott Troppy,SurveillanceEpidemiologist

BureauofInfectiousDiseaseandLaboratorySciences

MADepartmentofPublicHealth

May27,2020

1

PivotTableLearningObjectives

• Usepivottablestoturnrawdataintomeaningfuland

insightfulreports

• Structureyourrawdataintherightway

• Designandformatyourpivottablereportssothattheyare

easytoreadandunderstand

• Summarizeyourdataindifferentwaysincludingtotals,counts

andpercentages

• Usepivottables

tocreatemonthlysummaryreports

• Displayapivottableasachart

2

3

4

OutputType:CSVvs XSL

5

CommaSeparateValue(CSV)output

6

• Lookingatarawdataset

likethisonehere,how

wouldyouanswerthe

following?

• Whatisyourcountof

genderinyourdata

• WhatdoesRacelook

like?

• Whatabouthispanic

makeupofyourevents?

• HowdoIcreateatableof

ageranges?

WhyuseaPivotTable?

7

DeleteColumnsyoudon’tneed

8

9

Selectentirespreadsheet– clickonceon

boxabovethenumber1

andtotheleftofthefirstcolumn

10

ClickInserttabfromtheRibbonattopof

screenandthenclickPivotTable

11

Insertyour

PivotTable

intoanew

worksheet

12

Variables

from

spreadsheet

appearfor

youto

chooseand

count

13

Selectvariableandthendrag

downintoRowandValues

14

RowLabelsandValuestoFind

NullValues

15

Blankmeansemptyinyourspreadsheet

16

CopyandPasteintoanother

worksheettoallowforediting

17

CopyandPasteandchooseValues

sothenumberscomeoverandnot

theformulafromyourPivottable

18

Nowyoucaneditthecolumn

headersandformatasneeded

19

Blankmeansemptyinyourspreadsheet

20

CopyandPasteintoanother

worksheettoallowforediting

21

Housekeepingto

manageyour

data‐rename

yourworksheet

asyoucreate

them….

22

SaveyourCSVtoExcelformat

23

CopyandPasteandchooseValues

sothenumberscomeoverandnot

theformulafromyourPivottable

24

Nowyoucaneditthecolumn

headersandformatasneeded

25

UsingtheGroupFunction(AgeVariable)

26

• FromyourExcelspreadsheetyouhavea

listofAges(inyears)

• Insertyourpivottable

• SelectAgeforRowLabels

• SelectCountofYearsinValues

27

UpdateAgeChoices

28

• UpdatetheGroupingoptionsbasedontheagerangesyou

want

• ClickOKtocreatethetable

• Youcancopythentableandthenupdatetheageranges

withcounts

PivotTableGraph

• Tocreateagraphfirst

highlighttherowsand

columnsyouwanttouse

• ClicktheInsertribbonand

thenchooseagraphtype

• Column

• Line

• PieChart

29

InsertaGraphfromyourtable

30

Graphisinsertedinworksheetwhereyou

canthenformat

31

LBOHTemplate

S

p

readshee

t

32