Dph second Edition Day 2 Exercises First Half

DPH Second Edition Day 2 Exercises

First Half (HTN Master List)

IF ISNA Function for Master Visit List VLOOKUP

1. 
   If Food Pharmacy VLOOKUP results in NA then output /No. If it results in a value, then output Yes.
   

Conditional Formatting Master Visit List

1. 
   Select Nurse/Pharmacist column
   
2. 
   Apply Conditional Formatting → Text That Contains: “Nurse”
   
3. 
   Apply Conditional Formatting → Text That Contains: “Pharmacist”
   
4. 
   Select Food Pharmacy column
   
5. 
   Apply Conditional Formatting → Text That Contains: “Yes”
   

Review Benefits of PivotTables Based on Tables

1. 
   Add/Update Records in Master List forNurse Visit Pivot Table
   
2. 
   Refresh Data
   

Building Out Nurse Visit Eligibility PivotTable

1. 
   Add Provider to Rows Quadrant
   
2. 
   Add EncounterFacility to Columns Quadrant
   
3. 
   Move EncounterFacility to Rows Quadrant
   
4. 
   Explore PivotTable Tools → Design tab
   

• 
  Move, Show, Hide Subtotals/Grand Totals
  
• 
  Banded Rows
  
• 
  Table Styles
  
• 
  Report Layouts
  

PivotTable Slicers & Drill Down Reports

1. 
   Insert Slicers for Provider and EncounterFacility
   
2. 
   Filter for patients with Provider A encountered in Primary Care
   
3. 
   Filter for patients with Provider A, J, and K encountered in Primary Care
   
4. 
   Double Click on Patient Count for Provider J to generate drill down report.
   
5. 
   Review Move & Copy to move report into new workbook.
   

Grouping PivotTable Data

1. 
   Clear Nurse Visit PivotTable
   
2. 
   Add the following fields:
   

• 
  Rows: Age
  
• 
  Values: ID
  

3. 
   Summarize Values for ID as Count
   
4. 
   Click and drag to select ages between:
   

• 
  18-24
  
• 
  25-34
  
• 
  35-44
  
• 
  45-54
  
• 
  55-64
  
• 
  65+
  

5. 
   Right click on each selection field and choose “Group”
   
6. 
   Remove Age from Values Quadrant
   
7. 
   Rename Age2 as Age Brackets
   

Show Values as % in PivotTables

1. 
   Right click on ID values in PivotTable
   
2. 
   Hover Over “Show Values As”
   
3. 
   Select “% of Grand Total”
   

————————

Second Half (Briefly in HTN, remainder in Cancer Screening Master List)

Building Bar Charts

1. 
   Using Nurse Visit PivotTable with the following fields:
   

• 
  Rows: Provider
  
• 
  Values: ID (Summarize as Count)
  

2. 
   Insert PivotChart → Bar Chart
   
3. 
   Add EncounterFacility to Columns Quadrant
   
4. 
   Remove EncounterFacility from Columns Quadrant
   
5. 
   Remove Provider from Rows Quadrant
   
6. 
   Add Age Brackets to Rows Quadrant
   

Formatting & Managing Charts

1. 
   Change Chart Type to Pie Chart
   
2. 
   Review Chart Elements
   

• 
  Chart Title
  
• 
  Data Labels
  
• 
  Legend/Data Table
  

3. 
   Review Quick Layout/Chart Colors
   

• 
  Save Chart Template for Future Use
  
• 
  Change Color Options w/ Theme Color palate
  

Building Cancer Screening Master List

1. 
   Create the following columns in Cancer Screening Master List:
   

• 
  Breast Screening
  
• 
  Colorectal Screening
  
• 
  Cervical Screening
  

2. 
   VLOOKUP (Using MRN as Lookup_Value) to extract Screening Status from the following worksheets:
   

• 
  Breast Cancer Screening
  
• 
  Cervical Cancer Screening
  
• 
  Colorectal Cancer Screening
  

Using IFERROR to Clean Up VLOOKUP

1. 
   Build IFERROR function around VLOOKUPs in Master List
   
2. 
   If there is an N/A error, output “No Data Available”
   

IF & OR Function to Check IF Due for Any Cancer Screening

1. 
   Create New Column in Cancer Screening Master List called Any Cancer Screening Due
   
2. 
   Build IF & OR Nested Function to check if any of the following columns say Due:
   

• 
  Cervical Screening
  
• 
  Breast Screening
  
• 
  Colorectal Screening
  

3. 
   IF any say Due, say “Due”
   
4. 
   IF none say Due say “Not Due”
   

PivotTable Analysis of Cancer Screening Master List

1. 
   Build PivotTable with the following fields:
   

• 
  Rows: RaceDescription
  
• 
  Values: MRN (Summarize Values as Count)
  

2. 
   Review Drill Down Reports (Double Click on Values in PivotTable)
   
3. 
   Insert Slicers for the following fields:
   

• 
  Cervical Screening
  
• 
  Breast Screening
  
• 
  Colorectal Screening
  

Review Text to Columns Date Conversion

1. 
   Apply Text to Columns on NextScheduledApptDate
   

• 
  Fixed Width Delimeter
  
• 
  Do Not Import Time
  

CONCATENATE Full Name

1. 
   Insert New Column after PCPFirstName
   
2. 
   Build CONCATENATE Function with PCPFirstName, “ “, and PCPLastName