Pivot Table Demonstration Tools for LBOHs May 27, 2020 cott Troppy, - - PowerPoint PPT Presentation

▶
pivot table demonstration
SMART_READER_LITE
LIVE PREVIEW

Pivot Table Demonstration Tools for LBOHs May 27, 2020 cott Troppy, - - PowerPoint PPT Presentation

Jan 22, 2023 10 likes •334 views

Pivot Table Demonstration Tools for LBOHs May 27, 2020 cott Troppy, Surveillance Epidemiologist Bureau of Infectious Disease and Laboratory Sciences MA Department of Public Health 1 Pivot Table Learning Objectives Use pivot tables to turn

slide-1
SLIDE 1

Pivot Table Demonstration

Tools for LBOHs

cott Troppy, Surveillance Epidemiologist Bureau of Infectious Disease and Laboratory Sciences MA Department of Public Health

May 27, 2020

1

slide-2
SLIDE 2

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

slide-3
SLIDE 3

3

slide-4
SLIDE 4

4

slide-5
SLIDE 5

Output Type: CSV vs XSL

5

slide-6
SLIDE 6

Comma Separate Value (CSV) output

6

slide-7
SLIDE 7
  • 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

slide-8
SLIDE 8

Delete Columns you don’t need

8

slide-9
SLIDE 9

9

slide-10
SLIDE 10

Select entire spreadsheet – click once on box above the number 1 and to the left of the first column

10

slide-11
SLIDE 11

Click Insert tab from the Ribbon at top of screen and then click Pivot Table

11

slide-12
SLIDE 12

Insert your PivotTable into a new worksheet

12

slide-13
SLIDE 13

Variables from spreadsheet appear for you to choose and count

13

slide-14
SLIDE 14

Select variable and then drag down into Row and Values

14

slide-15
SLIDE 15

Row Labels and Values to Find Null Values

15

slide-16
SLIDE 16

Blank means empty in your spreadsheet

16

slide-17
SLIDE 17

Copy and Paste into another worksheet to allow for editing

17

slide-18
SLIDE 18

Copy and Paste and choose Values so the numbers come over and not the formula from your Pivot table

18

slide-19
SLIDE 19

Now you can edit the column headers and format as needed

19

slide-20
SLIDE 20

Blank means empty in your spreadsheet

20

slide-21
SLIDE 21

Copy and Paste into another worksheet to allow for editing

21

slide-22
SLIDE 22

Housekeeping to manage your data‐rename your worksheet as you create them….

22

slide-23
SLIDE 23

Save your CSV to Excel format

23

slide-24
SLIDE 24

Copy and Paste and choose Values so the numbers come over and not the formula from your Pivot table

24

slide-25
SLIDE 25

Now you can edit the column headers and format as needed

25

slide-26
SLIDE 26

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
slide-27
SLIDE 27

27

slide-28
SLIDE 28

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

slide-29
SLIDE 29

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

slide-30
SLIDE 30

Insert a Graph from your table

30

slide-31
SLIDE 31

Graph is inserted in worksheet where you can then format

31

slide-32
SLIDE 32

LBOH Template Spreadsheet

32