What to do when you get your data – How to sort – How to pivot

View Github Repository Open presentation in a new window

scottpham

See all presentation from scottpham

What to do when you get your data – How to sort – How to pivot

0 0

excel-slides

Slides for a presentation for NBC Bay Area

On Github scottpham / excel-slides

What to do when you get your data

Will talk about how to get insights from your data using some basic excel skills: subsetting, sorting and if we have time, pivoting using pivot tables.

Good Spreadsheet Formats

  • .xls
  • .xlsx
  • .ods
  • .csv
  • .tsv
1: Excel has some flexibility so if you see these file formats, don't worry about it, they will convert automatically. 2. Don't have to ask for an excel file--just ask for spreadsheet format

Bad Spreadsheet Formats

PDF

Adverse Events Database

Database of medical errors

Download the sample file: http://bit.do/adverse-events

We used it as part of this story.

Story was last November

Insights from data can form the basis of whole stories

A basic insight

Hospitals in California have reported 6,282 adverse events to the state in the last four fiscal years. They range from “death associated with an error”, to “decubitus ulcers,” or bedsores.
Takeaway: you can get this information by simply looking at the data and counting rows.

More advanced insights require subsetting or manipulation

What is the most common adverse effect? How many adverse effects are there in a particular city? Which hospitals have the most adverse effects?

A more advanced insight:

Santa Clara Valley Medical Center had a total of 64 adverse events, but 30 of those were “retention of a foreign object” in a patient during surgery, the most in the state during that time period.

Let's get to the data

Open your spreadhseets. Link, if you forgot is bit.do/adverse-events

Subsetting and Sorting

We are not modifying the contents of cells or creating new ones.

rearranging and filtering

How to sort

It's almost always better to use a filter to sort Select any cell, not a range. Home tab --> sort & filter --> Filter.

Sort by fiscal year

Filter for 'Santa Clara Valley Medical Center'

Make sure to unselect "select all" before selecting the thing you want

How to pivot

Put it in a new worksheet

How many adverse effects per city

The 'builder'

explain field list, filter, column labels, row labels, values. For the most part you can only pay attention to row labels and values. Drag description to row labels

The 'values' field

The row will COMBINE rows that are categories. In the VALUES field you decide what operation you want to run on which columns. Defaults to count.

Sort the counts

To sort, select a SINGLE CELL then data -->sort

A more interesting pivot

Which hospital has the most 'Retention of a foreign object in a patient'?

Start a new pivot table

Group by TWO labels

Order matters here

Select the row you want to filter

Select the adverse effect

Take a moment before the next slide

Sort

A bonus subset

Double-click on any calculated cell

What else???

There's a lot I didn't cover:

  • Formulas
  • Splitting and adding columns
  • Stats
subsets are powerful

Download the slides

http://scottpham.xyz/excel-slides/

Scott Pham

scott.pham@nbcuni.com