Intermediate applications of excel
Download
1 / 92

Intermediate Applications of Excel - PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on

Intermediate Applications of Excel. SCAHQ Presentation Robert Steed, MCSM Spartanburg Regional Healthcare System Data Quality Analyst. About Robert ‘Bobby’ Steed. Graduated Clemson 2001 BS, Computer Information Systems Data Quality Analyst Spartanburg Regional Quality Services Dept.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Intermediate Applications of Excel' - zorina


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Intermediate applications of excel

Intermediate Applications of Excel

SCAHQ Presentation

Robert Steed, MCSM

Spartanburg Regional Healthcare System

Data Quality Analyst


About robert bobby steed
About Robert ‘Bobby’ Steed

  • Graduated Clemson 2001

    • BS, Computer Information Systems

  • Data Quality Analyst Spartanburg Regional Quality Services Dept.

  • MIDAS+ Certified System Manager

  • Data Analysis, Report Rollups


Excel uses in healthcare
Excel Uses in Healthcare

BASIC

Storing

Performing Analysis

INTERMEDIATE

Displaying

Sharing

BASIC


Storing basic
Storing - Basic

  • “Storing” is entering data into the spreadsheet cells.

  • Examples

    • Time Sheets

    • Lists / Logs

    • Calendars


Performing analysis intermediate
Performing Analysis - Intermediate

  • “Performing Analysis” is using Excel functions to summarize and breakdown the data.

  • Examples

    • Turnaround Time

    • % Compliance

    • Room Utilization


Displaying intermediate
Displaying - Intermediate

  • “Displaying” refers to visually representing data.

  • Examples

    • Pareto Graph

    • Scorecard

    • Pie Chart


Sharing basic
Sharing - Basic

  • “Sharing” is presenting or sending data to others.

  • Examples

    • Power Point

    • Email

    • Internet


Intermediate excel steps
Intermediate Excel Steps

  • Concentrate on these steps

  • Goals:

    • Performing Analysis: Using Excel to provide summary data for analysis.

    • Displaying: Accurately and easily portray your data for your audience to understand.


What will i learn

Performing Analysis

Functions

Sorting

Filtering

Basic Pivot Table

Examples

Displaying

Elements of good data display

Graphs

Data Summaries

What Will I Learn?


Performing analysis functions
Performing Analysis - Functions

  • Built in to Excel

  • Hundreds of basic to expert calculations by selecting cells or cell ranges.

  • Easy to use



Exercise 1
Exercise 1

Calculate the average of the following numbers: 12, 15, 14.25, 20

  • Enter the numbers into cells starting with A1 going down

  • Select cell C1

  • Click and select AVERAGE()

  • Select the cell range and click OK.



Exercise 2
Exercise 2

Add 21, 41, 8, 10, 5 to your number list and update the average function to include all your numbers.

  • Enter the numbers into cells following your first set

  • Select the cell where your average is calculated and click the button.

  • Update the cell range to all your numbers and click OK.



Intermediate examples xls
Intermediate Examples.xls

  • Open this file

  • Located on your desktop

  • Discussion about contents


Intermediate examples xls1
Intermediate Examples.xls

  • No summary information

  • Basic patient information

  • Simple data table


Intermediate examples xls2
Intermediate Examples.xls

  • What we need to calculate

    • How many patients: COUNT()

    • Average Age: AVERAGE()


Intermediate examples xls3
Intermediate Examples.xls

Where will we put our summary data?

  • Don’t place at bottom of data table!

    • What if we want to add more data?

  • Right side of the data table

  • Need a way to identify summary information

    • Names


Exercise 3
Exercise 3

Type in the names of the fields we are going to calculate to the right of the data table.

  • Cell I2 – ‘Number of Patients’

  • Cell I4 – ‘Average Age’

  • Remember to AutoFit your column!

    • Select column I

    • Format -> Column -> AutoFit Selection


Exercise 4
Exercise 4

Insert a formula to calculate the number of patients and average age.

  • Cell J2 – Click and select COUNT()

    • Select cell range A2 – A24

  • Cell J4 – Click and select AVERAGE()

    • Select cell range D2 – D24

  • Format cells



Intermediate examples xls4
Intermediate Examples.xls

  • What else can we calculate?

  • Length of Stay for each patient

    • Insert new column

    • Label ‘LOS’

    • End Date – Start Date

  • Average Length of Stay for all patients

    • AVERAGE() with cell range of new LOS column


Exercise 5
Exercise 5

Insert a new column before the ‘Age’ column for LOS calculation

  • Select column D

    • Right click and select Insert

  • Cell D1 type in LOS



Exercise 6
Exercise 6

In the LOS column, insert the calculation of End Date – Start Date

  • Cell D2: ‘=C2-B2’

  • Problem??

  • Select D2 and click Format -> Cells

    • Change from Date to Number with no decimal places


Exercise 7
Exercise 7

Complete LOS calculation for all patients

  • Select cells D2 – D24

  • Click Edit -> Fill -> Down

  • Save your file



Exercise 8
Exercise 8

Add ‘Average LOS’ to your summary data

  • Cell J6 type in ‘Average LOS:’

  • Cell K6 add average calculation

    • =AVERAGE(D2:D24)

  • Format cell to a number with 2 decimal places



Sorting
Sorting

  • Orders a list or data table by selected column

    • Ascending or Descending

  • Easier to find things

  • Basic categorization


Sorting1
Sorting

  • Go to the ‘Sorting and Filtering Example’ worksheet

  • Discussion about contents


Sorting2
Sorting

  • 2 Ways to sort

    • Selecting the column you want to sort by and clicking the following buttons:

    • Selecting the entire table and clicking Data->Sort

      • Option to sort by more than 1 column

Sorts entire table Ascending

Sorts entire table Descending


Exercise 9
Exercise 9

Sort the table by ‘Primary MD’ in ascending order

  • Select cell D1 labeled ‘Primary MD’

  • Click the button



Exercise 10
Exercise 10

Sort the table by ‘Diabetic?’ Then ‘Last Name’ in ascending order

  • Select the entire table

  • Click Data -> Sort

  • Sort by ‘Diabetic?’ first in ascending order

  • Sort by ‘Last Name’ second in ascending order



Filtering
Filtering

  • Allows the user to breakdown or customize the data table

  • Excel can automatically filter a table set up similar to ours

  • Select the header or top row in your table

    • Data -> Filter -> AutoFilter


Exercise 111
Exercise 11

Set up a filter for our data table

  • Select cell A1

  • Click Data -> Filter -> Auto filter



Filtering1
Filtering

  • Combo selection boxes at the top of each column

  • Click the for the different filtering options for that column

  • Will update the entire data table


Exercise 12
Exercise 12

Filter the data table by patients with the Primary MD of Abbott

  • Click the in the ‘Primary MD’ field

  • Select Abbott



Basic pivot table
Basic Pivot Table

  • A pivot table is an interactive representation of data in a data table

  • You can customize the layout while summarizing your data

  • Drag and drop fields you want to summarize


Basic pivot table1
Basic Pivot Table

  • Click Data -> Pivot Table and Pivot Chart Report


Basic pivot table2
Basic Pivot Table

  • Choose your data and report type

    • Microsoft Excel or database

    • Pivot Table


Basic pivot table3
Basic Pivot Table

  • Select your data table (including headers)

  • Choose your output location


Basic pivot table4
Basic Pivot Table

Data Table Elements – Fields in the selected data table

Column Fields – Groups the data table element by columns

Row Fields – Groups the data table element by rows

Data Items – Displays summary information based on what you put in the Row and Column fields.


Basic pivot table5
Basic Pivot Table

  • We add elements to our pivot table by dragging the field from the ‘Data Table Elements’ window to the pivot table row, column or data items section

  • Questions?


Basic pivot table6
Basic Pivot Table

  • Go to the ‘Pivot Chart Example’ worksheet

  • Discussion about contents

  • Need a breakdown of the discharge locations from our list of patients.

    • D/C Loc – Rows Field

    • D/C Loc – Data Items


Basic pivot table7
Basic Pivot Table

  • Data -> Pivot Table and Pivot Chart Report

  • Click Next

  • Select the entire data table

  • Click Next


Basic pivot table8
Basic Pivot Table

  • Select new worksheet

  • Click Finish


Basic pivot table9
Basic Pivot Table

Click and drag D/C Loc into Row Field and Data Items


Basic pivot table10
Basic Pivot Table

Count of each different D/C Loc


Exercise 13
Exercise 13

Create this pivot table


Exercise 14
Exercise 14

Select your pivot table and click the button


Basic pivot table11
Basic Pivot Table

  • Drag and drop additional fields from your data elements to further summarize your data

  • How would we add DC disp to this pivot table?


Basic pivot table12
Basic Pivot Table

  • Drag DC Disp to the Row Fields section



Exercise 15
Exercise 15

Create a pivot table that shows a breakdown of ‘Principal DX Description’ and their ‘DC Disp’




Displaying1
Displaying

  • What unit/department?

  • What exactly is a needle stick?

  • Years?

  • Data source?

  • What was the workload like?


Displaying2
Displaying

Source: ACME Clinic Quality Services Department



Displaying4
Displaying

  • Goal is to display your data accurately to your audience

  • Elements of effective data display

    • N

    • Account for workload or factors that directly effect your data

    • Source

    • Obvious to you; Oblivious to them


Simple graph
Simple Graph

  • Visually displays data from a data table

  • Grouped by rows or columns

  • Multiple types to choose from

    • Line

    • Bar

    • Pie


Simple graph1
Simple Graph

  • Elements that make an effective graph

    • Keep it simple

    • Avoid too many elements

    • Clear title that describes your data

    • Label the x and y axis

    • Use text boxes!! or Insert -> Text Box


Simple graph2
Simple Graph

  • Click the button or click Insert->Chart

  • Select the chart type


Simple graph3
Simple Graph

  • Select your Source Data by choosing the cell range


Simple graph4
Simple Graph

  • Chart Wizard

    • Titles

    • Axes

    • Gridlines

    • Legend

    • Data Labels

    • Data Table


Simple graph5
Simple Graph

  • Choose your chart location


Simple graph6
Simple Graph

  • Go to the ‘Graph Example’ worksheet

  • Discuss contents


Exercise 16
Exercise 16

Create a graph of Length of Stay for 9 Tower



Exercise 18
Exercise 18

Add a text box that says ‘Outlier of 20 day LOS’ above the July 2005 Length of Stay

  • Click or Insert -> Text Box

  • Click on the graph above the July 2005 data point

  • Type in ‘Outlier of 20 day LOS’



Displaying5
Displaying

  • Use what we have learned today to accomplish all of elements of effective data display

    • Functions

    • Pivot Table

    • Pivot Chart

    • Graphs

  • Data Summary

    • Rollup of ‘Performing Analysis’

    • Display as report and/or use to make graphs


Detailed example 1
Detailed Example 1

  • Your director sends you some data regarding needle sticks for last month by hospital division

  • Figure out which division has the highest occurrence of needle sticks

  • Provide a report by the end of the day


Detailed example 11
Detailed Example 1

  • Go to the ‘Displaying Example’ worksheet

  • Discuss contents


Detailed example 12
Detailed Example 1

  • What do we need to calculate?

    • % Needle Sticks per Shots Administered

    • Needle Sticks / Shots Administered


Detailed example 13
Detailed Example 1

  • Add a new column named ‘% Needle Sticks/Shots Admin’. Remember to format!

  • Calculate % Needle Sticks per Shots Administered for each Division/Unit

  • Format new column to Percentage with 2 decimal places



Detailed example 2
Detailed Example 2

  • You send the data summary to your director and wants you to display the data in a Pareto Graph

  • Pareto Graph – Descending bar graph

    • Sort descending


Detailed example 21
Detailed Example 2

Sort your data table by ‘% Needle Sticks/Shots Admin’ in descending order

  • Click cell D1

  • Click


Detailed example 22
Detailed Example 2

Create a bar graph that shows each division and the % Needle Sticks/Shots Admin.

  • Click

  • Select Column. Click Next.

  • Select data in column A and column D (Hint: Hold down Ctrl button)

  • Add chart options. Click Next.

  • Locate in “As Object in:” Displaying Example

  • Finish



Detailed example 24
Detailed Example 2

  • Time to send to your director?

    • Does it print correctly?

    • What else do we need to provide?

      • Date Range – January 2005 – December 2005

      • Source – ACME Care Management System

      • Report Date – Today

    • Header/Footer?


Group or individual project
Group or Individual Project

  • Open the ‘Intermediate Project.xls’ file on your desktop

  • Discuss contents


Group or individual project1
Group or Individual Project

  • How many surgeries were performed?

  • What is the average surgery time?

  • How many patients with surgery developed an infection? Infection rate?

  • Print a list of ONLY patients that had an infection.

  • Make a chart that displays only the surgery times of patients who had an infection.


Congratulations
Congratulations!!!

  • Questions?

  • Bobby Steed

    864-560-6995