Using Microsoft Excel to analyse patient data
Originally published in GP magazine .
Written for GP magazine around 2002. This is the version I submitted - the published article may have been edited.
Each of the major GP computer systems has tools to perform searches and audits to extract information from the records. Microsoft Excel can also be used to sort and examine data and perform searches, and has several excellent features that allow it to complement what your system provides. This is a brief guide assuming some basic knowledge of spreadsheets; it doesn’t cover how to get information from your system into Excel, as this varies widely, however even a simple paper-based audit could be entered and examined. The functions we are interested in live on the Data menu.
Excel will assume any block of information is a database, with each row representing one record, usually a person, and each column a bit of information for that record, sometimes called a field, e.g. blood pressure. It is customary to have the first row contain names of the fields that each column represents.
To use the following functions the active cell must be in the table, and you must only be highlighting one cell, or you will get an error message or, worse, disturb the relationship between records and fields. Purists would argue this is why Access is better; however, Excel is much easier to use and most of us have some knowledge of it anyway.
Sort
You can sort a column in ascending or descending order using the buttons on the toolbar; however, more complexity can be achieved by using the sort function on the Data menu. This allows you to sort more than one column at a time. For example, sorting a table of diabetics first by HbA1c then by blood pressure can provide information on who to target.
Autofilter
This simple but powerful tool allows you to build up intricate queries. Extract as much data as possible from your clinical system and use Excel to filter it. For example, to analyse your diabetic patients, select all diabetes and then send as much data to Excel as possible: BP, HbA1c, weight etc. Once it is in Excel, save two copies of the data, then work on one. You can use the hide columns facility to remove clutter on the screen, or even delete a column if you decide it is irrelevant.
Clicking Autofilter will place small boxes into the bottom corner of the column title cells. Clicking on these allows you to filter the information in that column. By setting up filters in more than one column, complex searches are built up.
There are some useful built-in filters: one searches for blank or non-blank records, another allows you to choose the top or bottom number or percentage from a column of numbers. You can use this to find the top 10% of all blood pressures or HbA1cs. There is also a custom filter that allows more complex options like greater than, equal to, etc.
The number of patients found is displayed at the bottom of the screen and the table shows those selected. It is easy to cut and paste the results to another worksheet and name it appropriately to preserve that query. The beauty of this method is that it is quick and allows you to dynamically change the queries and see the dataset grow and shrink with each filter.
Subtotal tool
This tool allows you to summarise data using functions like average and count. You can use this to produce a list of average blood pressures for each patient. Create a table that contains a unique patient identifier in one column and every blood pressure reading from the last year in the next. Then use subtotal to create an average reading for each patient, which can be more useful than using the last reading. I also suggested using subtotal to a colleague who wanted to look at how often his schizophrenic patients were consulting. We created a table containing patient ID in column one and the date of every consultation in the next. Using the count function and then sorting in descending order produced a list of most frequent attenders. I also used this function to analyse our serial DNA’ers.
Pivot tables
This is a powerful tool for analysing data that could fill an article by itself. Pivot tables not only allow you to group and summarise data but add depth to it, producing multilayered tables. The different layers could be year, type of diabetes or smoker/non-smoker. You can use the count and average tools, and can also easily group results, for example HbA1cs less than and more than 7.
Mail merging from Excel into Word
After analysing the data it can be useful to do something with it. When using the mail merge wizard in Word, select the Excel spreadsheet that contains the information you want to merge; it will allow you to select the appropriate sheet and columns, and after creating your template you can preview the letters before printing.