Data Analytics for HIM ProfessionalsUsing Microsoft Excel © And Access ©
Michael GeraThe Healthcare Computer Training Group
May 23, 2016
Excel © and Access © Are Copyrights of the Microsoft Corporation
Disclosure Statements
Mr. Gera has no relevant financial or nonfinancial relationships to disclose.
This presentation has educational value and is not forthe purpose of marketing the products or services of
the Healthcare Computer Training Group.
Excel © and Access © Are Copyrights of the Microsoft Corporation
Getting Started With HIM Analytics
Using Microsoft Excel
• Principles of Data Organization
• Working With Someone Else’s Spreadsheet
• The Data Analytics Mantra
• Quick Review of Excel’s Use of Math
• Quick Review of Excel’s Use of Logic
Excel © and Access © Are Copyrights of the Microsoft Corporation
Perform an Excel Skill Self Assessment
• Spreadsheet Design and Layout
• Introductory Formulas and Math
• Sorting and Manipulating Data
• Filtering Data
• Charting and Graphing
• Report Writing and Printing
• Data Analysis and Logical Formulas
• Securing A Worksheet
• Importing / Exporting Data
• Pivot Tables
• Intermediate Formulas and Math
• Importing and Exporting Data
• VLOOKUP
Introduction
Intermediate
Advanced
These designations
are somewhat audience
dependent.
Excel © and Access © Are Copyrights of the Microsoft Corporation
Spreadsheet Design and Layout
• Strive For Simplicity of Design
• Our Mantra: No Blank Columns, No Blank Rows
• Clarity of Column Headings
• Exclusivity of Column Headings
• Spreadsheets: Row and Column Orientation
• Multiple Worksheets Within Each Spreadsheet
• Understanding Command Line and Icons
• Understanding Sorting and Related Topics
Excel © and Access © Are Copyrights of the Microsoft Corporation
Data Entry Considerations
• Unique Column Headers
• Include Several Rows of Sample
• Use Actual Data As Sample, Not “Fake” Data
• Set Up Data Validation Tables Where Helpful
• Color Code Cells Where Appropriate
• Demonstrate Column Insert and Column Delete
• Demonstrate Column Hide and Column Unhide
• Demonstrate Data Validation techniques
Excel © and Access © Are Copyrights of the Microsoft Corporation
Introductory Formulas and Math
• Review Basic Arithmetic Operations: – Addition (+)
– Subtraction (-)
– Division (/)
– Multiplication (*)
• Emphasize the Unique Role of the Equal (=) sign
• Emphasize the Balanced Parenthesis ()
• Create Several Simple Calculation Exercises
• Demonstrate Role of AutoSum function
Excel © and Access © Are Copyrights of the Microsoft Corporation
Sorting and Manipulating Data
• Working With Column Headers
• Single Level Sorts
• Multiple Level Sorts
• Case Sensitive Sorting
• Sorting By Calculated Columns (LOS, etc.)
Excel © and Access © Are Copyrights of the Microsoft Corporation
Charting and Graphing
• Review the Principles of Graphing– X (Horizontal) and Y (Vertical) Axis
– Need For Simplicity and Clarity of Initial Exercise
– Distribution (Pie) Vs. Measurement (Bar)
– Understanding Role of Time as Graphing Factor
• Typical Examples of Graphing– Distribution of Patients By Diagnosis
– Documenting Chart Completion Activities
Excel © and Access © Are Copyrights of the Microsoft Corporation
Filtering Data
• Understanding Purpose of Filtering
• Comparison of Filtering and Programming
• Standard Filtering Procedures
• Text Filtering Procedures
• Date Range Filtering Procedures
• Print and Display of Filtered Data
• Customized Filtering Techniques
Excel © and Access © Are Copyrights of the Microsoft Corporation
Report Writing and Printing
• Understanding Purpose of a Report Writer
• Review Column Header Principles
• Review Calculation Principles
• Review of Multiple Level Sorting
• Detail Vs. Summary Data
• Impact of Spreadsheet Design On Report Writing
• Principles of Printing and Electronic Output– Hard Copy (Paper)
– Spreadsheet
– PDF File
Excel © and Access © Are Copyrights of the Microsoft Corporation
Data Analysis and Logical Formulas
• Data Analysis Fundamentals– Sorting and Multiple Level Sorting Techniques
– Fundamental Principles of Data Filtering
– Review Data Validation Techniques
– Review of Report Writing For Data Analysis
– Creating Summary Data Through Pivot Tables
– Useful Logical Formulas• IF Formula
• AND Formula
• Nested IF & AND Formula
Excel © and Access © Are Copyrights of the Microsoft Corporation
Securing A Spreadsheet
• Protect and Unprotect A Spreadsheet
• Protect and Unprotect A Worksheet
• Protect and Unprotect A Range of Data
• Protect and Share A Workbook
Excel © and Access © Are Copyrights of the Microsoft Corporation
Reports With Subtotals
• Data Preparation…Remember to Sort First !
• Understanding Report Criteria
• Subtotal Selection Window– At Each Change In
– Function
– Add Subtotal To
– Replace Current Subtotals
– Summary Below Data
Excel © and Access © Are Copyrights of the Microsoft Corporation
Pivot Tables
• Organize Your Data
• Define Your Data Range
• Setup Column Labels
• Setup Row Labels
• Setup Values
• Create Pivot Table
Excel © and Access © Are Copyrights of the Microsoft Corporation
Pivot Tables With Chart
• Organize Your Data
• Define Your Data Range
• Setup Column Labels
• Setup Row Labels
• Setup Values
• Create Pivot Table
• Create Chart
Excel © and Access © Are Copyrights of the Microsoft Corporation
VLOOKUP Formula• Data Reconciliation
• Search for Matched Values
• Remove Unmatched Values
Excel © and Access © Are Copyrights of the Microsoft Corporation
VLOOKUP Formula• Data Reconciliation
• Search for Matched Values
• Remove Unmatched Values
Excel © and Access © Are Copyrights of the Microsoft Corporation
Intermediate Formulae and Math
• Review of IF & AND Logical Formulas
• Calculation of Length of Stay (LOS)
• Calculation of Patient Acuity
• Calculating Provider Suspension Rates
• Categorizing and Graphing Chart Completion Activities
• Other Importing Formulae Considerations
Excel © and Access © Are Copyrights of the Microsoft Corporation
Importing and Exporting Data
• Understanding Principles of Data Import / Export
• Working With Data In Multiple Worksheets
• Defining Data Ranges for Import / Export
• Data Quality Control
Excel © and Access © Are Copyrights of the Microsoft Corporation
MPIPtSeqNoMRNOPt Last NamePt First NamePt DOBetc…
Encounter
EncounterNoMRNODate of ServiceChief ComplaintPrimary DxProvideretc…
Database Administration: Fundamentals of Database Design
Excel © and Access © Are Copyrights of the Microsoft Corporation
Excel © and Access © Are Copyrights of the Microsoft Corporation
Database Administration:
The Classic “One-To-Many” Database Design
MPIPtSeqNoMRNOPt Last NamePt First NamePt DOBetc…
Encounter 1EncounterNoMRNODate of Service
Encounter 2EncounterNoMRNODate of Service
Encounter nEncounterNoMRNODate of Service
Excel © and Access © Are Copyrights of the Microsoft Corporation
Understanding The Four “Objects” of Access
• Consists of “Objects” including:
– Tables: Store the Data
– Forms: Display the Data
– Queries: Retrieve the Data
– Reports: Output the Data
• Each “Object” Can Be Customized to Meet User Requirements
• Strong Visualization Skills
• Better Than Average Navigation Skills
• Able to Navigate Intuitive and Counter-Intuitive Systems
• Ability to Create Relationships Between Multiple Systems
• Data Conversion / Manipulation Skills
• Data Quality Control Skills
What Are the Most Important Skillsfor Using Microsoft Access ?
Excel © and Access © Are Copyrights of the Microsoft Corporation
Review / Q & A
• Approach to Data Analytics for HIM
• Sorting, Filtering, Subtotals, Pivot Tables
• VLOOKUP and Related Functions
• Next Steps: Microsoft Access
• Ideas for Further Discussion
Excel © and Access © Are Copyrights of the Microsoft Corporation