Excel Tips and Tricks
leveraging Excel in your pediatric officeExcel Tips & Tricks for your Pediatric Office
Sample Patient Data
Here’s a sample patient list (of completely made-up data — no HIPAA PHI) to practice Excel manipulations.
It’s also a good way to show how to generate random data samples.
The Sheet1 tab contains the most common US surnames [column A], the most common girls’ names [column C], and the most common boys’ names [column D]. Sources: BehindTheNames.com and Nameberry.com
The Randomizer tab generates made-up patient data along the following parameters:
- Chart number (column A is a randomly generated number between 10000 and 50000)
- Surname (column B generates a random number N between 1 and 1000, and column C is the Nth surname in the surname list on Sheet1)
- Sex (column E generates 1 or 2 randomly; column H is “F” if column E is a 1, and “M” if column E is a 2)
- First name (column D generates a random number N between 1 and 229; if column E is 1, it chooses the Nth female name from Sheet1, otherwise the Nth male name from Sheet1)
- Date of birth (column I generates a random date between 1/1/1998 and 10/31/2016)
- Insurance ID (column J generates three random letters followed by six random digits)
- Social Security Number (column K generates nine random digits, separated by dashes)
The Sample List tab retrieves 250 fictional patient records from the Randomizer tab for testing.
COUNTIF Function
How do you add up how many patients fall into each category? Count the X’s! An example of the COUNTIF function.
This example was used in the NCQA PCMH certification domain in which you must calculate which of your patients fall under each category of care management needs.
The Report tab is a simple tally sheet showing the patient’s chart number, the initials of the child’s PCP, and the care management category(ies) in which that child would potentially fit. Row 93 shows a simple COUNTIF function, in which the frequency of an “X” is totalled up for each column.
The Summary tab is a slightly more complex summary chart which uses COUNTIFS. COUNTIFS is simply a COUNTIF function with multiple conditions. On this tab, the COUNTIFS function tallies how many patients meet a specific care coordination criteria for each PCP.
The PivotTable tab is a PivotTable which provides nearly identical information to the Summary tab, except using the PivotTable feature rather than COUNTIFS.
COUNTIFS in Schedule Analysis
This example was also used in a NCQA PCMH analysis of different types of scheduled appointments. This type of report answers questions like:
- How many visits did you do on a Thursday that were also well checks?
- How many acute sick visits did you do on Mondays during a given period?
This is another example of COUNTIFs, in which we count the number of rows that meet multiple criteria.
The Data tab is a exported practice management report which shows the chart number, date of service, weekday of the date of service, the visit type (acute, chronic, or sick), and the final first coded diagnosis for that visit. The day of the week is computed from the date of service by using the function TEXT:
=TEXT(B1,”dddd”)
The Summary tab shows computations using COUNTIFS. For example, cell C3 counts the number of appointments where the day of the week is “Tuesday” and the visit type is “chronic.”
The PivotTable tab shows a similar summary using the PivotTable feature.
Concatenation
An example of simple concatenation, in which you combine the month “4” and the year “2012” to form a single field called “4/2012.” This example also illustrates two simple data graphs.
Concatenation is also commonly used with names. For example, with a spreadsheet like:
A | B | C | |
1 | FIRST | MI | LAST |
2 | JOHN | E | SMITH |
3 | MARY | T | BROWN |
…you can create a construction like BROWN, MARY T. using the function:
=CONCATENATE(C3,”, “,A3,” “,B3,”.”)
You could also create Dear MARY BROWN: using the function:
=CONCATENATE(“Dear “,A2,” “,C2,”:”)
Conditional Formulas (IF, AND, OR)
When reviewing patient lists, how would you classify patients as “ours” or “not ours” ? In this example, we classify patients who are “ACTIVE” or “INACTIVE” as “ours”, and all others (TRANSFERRED, DISCHARGED, or DECEASED) as “not ours.” This example uses the IF and OR functions in combination.
Very Simple VLOOKUP
This example features two lists of patients, compared using the VLOOKUP function. The My MCO List show’s the practice’s list of active patients with insurance plan X. Column G uses a bare bones VLOOKUP function to compare the insurance ID in column B to the list of insurance company IDs in the Their List tab column A. Matches return the insurance ID; non-matches return “#N/A.” Similarly, column G in the Their List tab performs a VLOOKUP function as well, comparing column A to My MCO List‘s column B.
A short video tutorial on how to use VLOOKUP
Elegant VLOOKUP
This example builds on the Very Simple VLOOKUP spreadsheet above. Rather than returning the insurance ID vs “#N/A,” it returns “Ok” for matches on both lists, “Move on our panel!” for patients on My MCO List but not Their List, and “Move off our panel!” for patients on Their List but not My MCO List. The tabs Action List – Move on Panel and Action List – Move Off Panel demonstrate how filters and linked tables can be used to create a printable action list to send to the MCO.
VLOOKUP for Complex List Matching
Here’s an example of a more complex matching/reconciliation on patient lists based on last names and DOB. Sometimes a child with a hyphenated surname appears on one list as “Clarissa Hailsham” and on another as “Clarissa Hailsham-Brown.” This example combines the IF function as well as the VLOOKUP function.
More Excel Tutorials
Helpful Excel Functions – an Overview. Includes practical examples of why you want to learn VLOOKUP, conditional formatting, SUMIF, COUNTIF, and others. There are also links to tutorials for each of these functions.