Intermediate applications of excel
1 / 92

Intermediate Applications of Excel - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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



Performing Analysis





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




Basic Pivot Table



Elements of good data display


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


  • Orders a list or data table by selected column

    • Ascending or Descending

  • Easier to find things

  • Basic categorization


  • Go to the ‘Sorting and Filtering Example’ worksheet

  • Discussion about contents


  • 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


  • 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


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


  • What unit/department?

  • What exactly is a needle stick?

  • Years?

  • Data source?

  • What was the workload like?


Source: ACME Clinic Quality Services Department


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


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


  • Questions?

  • Bobby Steed