My favorite analytic tool in Microsoft Excel is called the pivot table. Rather than relying on the confusing and canned reports provided by our practice management system and EHR, I can use pivot tables to see the data I want to see, sort the data how I want to sort it, and produce actionable and meaningful reports.
For those unfamiliar with pivot tables, pivot tables are a very powerful way to summarize, sort, filter, and group data. They are a feature of Microsoft Excel that allow one to analyze data (e.g., a report of billed charges and reimbursements) in a spreadsheet with ease. Pivot tables allow users to ‘pivot’ or quickly rotate data between rows and columns to analyze information. They also allow users to stack or nest data in rows or columns.
Managers can quickly organize and sort information while including or excluding the data displayed. Changing the way the pivot table displays practice information adds insight into practice operations. In this article, I share five ways that I have used pivot tables to benefit our practice.
1. Provider utilization
Like many specialty practices covering multiple hospitals, we had assigned physicians to certain hospitals and had not changed this resource allocation for several years. Using data from our practice management system and EHR, I created a pivot table looking at the return on investment for each physician’s hospital work. Within the pivot table, I showed procedures, consults, RVUs, and other comparative information. The table demonstrated significant differences in utilization and resulted in changes to our hospital coverage. Not coincidentally, revenues increased.
In the past, we used our canned practice management system reports to look at referrals by provider. These reports were not timely nor useful. We now have a pivot table linked to our practice management system server that is updated daily. It permits us to study referral trends by referring physician or practice, receiving physician or practice, or type of referral. It permits us to see changes on a real-time basis and address referral changes quickly. This data has enabled us to improve our service by being more responsive to concerns of referring physicians and has contributed to increased referrals.