It seems like everyone works with their reports and data in a different way. I often have clinics asking for a very specific report “Patients who have been seen in the last 3 months with a birthday in August and a first name that starts with J”. Well, that’s a bit extreme, but reports do tend to be managed very differently in every practice.
So while Jane doesn’t want to clutter the reports area with reports that aren’t used by the general population, we do offer an export to Excel option on the majority of our reports. And the reason for this? Excel is a master at manipulating data.
We have a bit of an Excel crush around here. And if you can master just a couple of basic Excel functions, you’ll never feel more empowered in getting the exact info you want.
1. Sort and Filter
Excel filters are a quick and powerful way of sorting data and filtering the data to get only specific items. I recommend that you start by highlighting the column names (the top row) and then selecting the Filter option:
Once the filter option is applied to the column names, you can click the drop-down option to open the filter window.
With the filter window open, you can select how you want to sort the data, and also select which items that you want to filter. In the example below, I’ve selected all the appointments with the word “Initial”:
For more information, check out this help document from Microsoft about how to use the sort & filter functionality.
2. Remove Duplicates
Another awesome and basic function is taking out any exact matches.
Here’s another help document from Microsoft on how to remove duplicates or matching fields.
Letting Excel do the addition for you. This is another basic and super useful function when working with financial exports.
Here’s the help document from Microsoft explaining how to use that feature.
4. Pivot tables
Pivot tables are complicated but awesome. Some number nerds even attribute them for saving their job. For you clinic types, if you want to find out who the top revenue contributing patient was last year, or find out how many new patient visits you had in each of the last 12 months, there is no better tool in Excel than pivot tables. While they’re not something that you’ll pick up in 5 min, if you’re at all inclined to number nerd-ery, here’s a great tutorial on Pivot tables. Or talk to your friendly neighbourhood Excel wizard (a.k.a. your accountant) for more assistance!
Feel free to read up on these and expand your brain… you’ll be manipulating data like a pro in no time ;)