1 / 92

Intermediate Applications of Excel

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.

zorina
Download Presentation

Intermediate Applications of Excel

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Intermediate Applications of Excel SCAHQ Presentation Robert Steed, MCSM Spartanburg Regional Healthcare System Data Quality Analyst

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

  3. Excel Uses in Healthcare BASIC Storing Performing Analysis INTERMEDIATE Displaying Sharing BASIC

  4. Storing - Basic • “Storing” is entering data into the spreadsheet cells. • Examples • Time Sheets • Lists / Logs • Calendars

  5. Performing Analysis - Intermediate • “Performing Analysis” is using Excel functions to summarize and breakdown the data. • Examples • Turnaround Time • % Compliance • Room Utilization

  6. Displaying - Intermediate • “Displaying” refers to visually representing data. • Examples • Pareto Graph • Scorecard • Pie Chart

  7. Sharing - Basic • “Sharing” is presenting or sending data to others. • Examples • Power Point • Email • Internet

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

  9. Performing Analysis Functions Sorting Filtering Basic Pivot Table Examples Displaying Elements of good data display Graphs Data Summaries What Will I Learn?

  10. Performing Analysis - Functions • Built in to Excel • Hundreds of basic to expert calculations by selecting cells or cell ranges. • Easy to use

  11. Performing Analysis - Functions

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

  13. Exercise 1

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

  15. Exercise 2

  16. Intermediate Examples.xls • Open this file • Located on your desktop • Discussion about contents

  17. Intermediate Examples.xls • No summary information • Basic patient information • Simple data table

  18. Intermediate Examples.xls • What we need to calculate • How many patients: COUNT() • Average Age: AVERAGE()

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

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

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

  22. Exercise 4

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

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

  25. Exercise 5

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

  27. Exercise 7 Complete LOS calculation for all patients • Select cells D2 – D24 • Click Edit -> Fill -> Down • Save your file

  28. Exercise 7

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

  30. Exercise 8

  31. Sorting • Orders a list or data table by selected column • Ascending or Descending • Easier to find things • Basic categorization

  32. Sorting • Go to the ‘Sorting and Filtering Example’ worksheet • Discussion about contents

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

  34. Exercise 9 Sort the table by ‘Primary MD’ in ascending order • Select cell D1 labeled ‘Primary MD’ • Click the button

  35. Exercise 9

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

  37. Exercise 10

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

  39. Exercise 11 Set up a filter for our data table • Select cell A1 • Click Data -> Filter -> Auto filter

  40. Exercise 11

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

  42. Exercise 12 Filter the data table by patients with the Primary MD of Abbott • Click the in the ‘Primary MD’ field • Select Abbott

  43. Exercise 12

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

  45. Basic Pivot Table • Click Data -> Pivot Table and Pivot Chart Report

  46. Basic Pivot Table • Choose your data and report type • Microsoft Excel or database • Pivot Table

  47. Basic Pivot Table • Select your data table (including headers) • Choose your output location

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

  49. 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?

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

More Related