Using Excel to Pre-Code Survey Comments

This post shows how to use formulas to pre-code survey comments automatically in Microsoft Excel. I demonstrated this technique as part of a presentation that I made at the Wrangling Library Data virtual conference last month.

Libraries use surveys for at least two purposes: to elicit user feedback concerning specific programs and services, and to gauge overall satisfaction with library resources and services. Many surveys provide comment boxes where respondents can provide open-ended feedback. Comments can convey useful insights, but they can be time-consuming—and thus costly—to analyze. Automating the analysis process to some extent is an attractive proposition.

The creators of a survey can often make reasonable guesses as to the themes that respondents might address in their comments. For example, comments collected via a library customer satisfaction survey might speak to the value of resources, spaces, and services. If survey administrators can identify keywords that denote a given theme, Excel can easily enough determine if a comment contains one or more of those keywords.

In an academic library environment, the theme of library resources might be represented by keywords such as book, journal, article, and database, among others. Consider, then, the following survey comment:

Judging by the occurrence of forms of journal and article, an automated process can determine that this comment likely says something about the library’s resources.

I have devised an Excel file that can quickly process up to 2,500 survey comments, looking for up to seven keywords associated with each of four distinct themes. The image below shows how it visualizes the presence of keywords and summarizes the score for a particular theme.

Of course, automated pre-coding isn’t perfect. A survey comment that describes the experience of booking a study room could be taken to refer to the resources theme. Once comments have been pre-coded, a human needs to read them to make sense of what has been written and, as necessary, reassign them to appropriate categories. Nevertheless, if you have a lot of data to analyze in a short time, it can be very helpful to have your data organized into “piles” where comments generally pertain to a given theme.

It seems appropriate to mention one more caveat: You may pre-code comments into particular categories, but as a responsible researcher, you need to be open to the fact that respondents may have emphasized themes that you didn’t anticipate. You should always go into the analysis phase with an open mind, ready to create categories based on what people actually said.

Want to learn more about data Analysis and related topics?

Click the buttons below to see relevant entries in my bibliography, SmithFile.