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.
Robert Steed, MCSM
Spartanburg Regional Healthcare System
Data Quality Analyst
Basic Pivot Table
Elements of good data display
Data SummariesWhat Will I Learn?
Calculate the average of the following numbers: 12, 15, 14.25, 20
Add 21, 41, 8, 10, 5 to your number list and update the average function to include all your numbers.
Where will we put our summary data?
Type in the names of the fields we are going to calculate to the right of the data table.
Insert a formula to calculate the number of patients and average age.
Insert a new column before the ‘Age’ column for LOS calculation
In the LOS column, insert the calculation of End Date – Start Date
Complete LOS calculation for all patients
Add ‘Average LOS’ to your summary data
Sorts entire table Ascending
Sorts entire table Descending
Sort the table by ‘Primary MD’ in ascending order
Sort the table by ‘Diabetic?’ Then ‘Last Name’ in ascending order
Set up a filter for our data table
Filter the data table by patients with the Primary MD of Abbott
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.
Click and drag D/C Loc into Row Field and Data Items
Count of each different D/C Loc
Create this pivot table
Select your pivot table and click the button
Create a pivot table that shows a breakdown of ‘Principal DX Description’ and their ‘DC Disp’
Source: ACME Clinic Quality Services Department
Create a graph of Length of Stay for 9 Tower
Add a text box that says ‘Outlier of 20 day LOS’ above the July 2005 Length of Stay
Sort your data table by ‘% Needle Sticks/Shots Admin’ in descending order
Create a bar graph that shows each division and the % Needle Sticks/Shots Admin.