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

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 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

Why use a Pivot Table? • 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? 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 11 screen and then click Pivot Table

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 18 the formula from your Pivot table

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 24 the formula from your Pivot table

Now you can edit the column headers and format as needed 25

Using the Group Function (Age Variable) • 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 26

27

Update Age Choices • 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 28

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 32 Spreadsheet