by Jeff McKune

If you are looking at developing a new service line or you just want to have a better understanding of business patterns that impact your hospital, you may have some questions. What are the most prevalent DRGs in our market area? What percentage of patients in our market area are coming to our hospital? If they are not coming to our hospital, where are they going? Are we holding our own on surgeries? Are we capturing our share of cardiovascular business?

I did a quick survey of state hospital associations across the country, and it looks like most, if not all, have some means of collecting and providing admissions and utilization data for hospitals in that state. Some associations make the data available on the web, while others may provide it on CD or provide summary reports of various kinds. In Missouri, an MHA member hospital can purchase this data on CD for under a thousand dollars. It is interesting how much you can learn from data like this when you have a quiet afternoon, perhaps during some down time during business travel. In fact, each of the questions above can often be answered without a lot of effort.

Microsoft Excel has a powerful feature call pivot tables. Pivot tables allow you to easily summarize and digest large amounts of data, viewing it in different ways to answer relevant business questions. Using pivot tables with statewide hospital data can provide some valuable, and often hidden, insights. There is no programming involved, and a basic pivot table takes just a few clicks and a couple of drag-and-drops to complete. There are three steps: Determine what data you are going to use, run the Pivot Table Wizard, and drag-and-drop your row and column definitions into place.

Your data can be a list of data in an Excel worksheet, a Microsoft Access database, or some larger external database. You will need to know a little about the data you are wanting to study. For example, if you want to look at inpatient admissions you will need to make sure that the data source has that kind of data in it. The Pivot Table Wizard is found under the Data menu in Excel. It will walk you through selecting your data source and determining where you want to put your new pivot table. You will probably want to put it in a new worksheet. Finally, drag-and-drop the data fields you want to study onto your pivot table.

Do you want to understand the relationship between specific hospitals and counties where patients live? From your field list drag "Hospital Name" (or whatever the data field name is) to the left of your pivot table. Boom! Excel quickly lists all of the hospitals along the left of your pivot table. Similarly, drag "County" to the top of your pivot table. Excel will list the counties across the top of the table. Drag "Admission Date" (again an example name) to the Data area of the pivot table, and Excel will provide a count of all admissions by county for every hospital. Granted, this may be a large pivot table, but drop-down lists are provide for you to select just the hospitals and counties in which you are interested. If you want to look at DRGs or physicians, drag "County" off the pivot table and back to the list of data fields, and then drag "DRG" or "Physician" to the top of your pivot table.

Yes, there are a lot of benchmarking and data analysis services available, but it is amazing what you can discover on your own using pivot tables. If you would like some more guidance, Google "building an Excel pivot table" and you will get more than 400,000 hits. One of the links is a video that shows you how to build a pivot table in Excel 2007. Or, post a question here or email me, and I will be happy to help. If you have used pivot tables in an interesting way, be sure to share it with the rest of us!