Printer Friendly

Analyzing focus group data with spreadsheets.

Abstract: Focus groups (FG) are widely used in health research as well as in other disciplines to gain perspectives, enlightenment, and insight into the minds of participants as they discuss topics of interest to the research. The non-quantitative data from focus groups may appear daunting to analyze because strategies vary widely, there are no standardized analytic strategies, and many specialized software packages are difficult or time consuming to learn. This article articulates a strategy for analyzing FG data using widely available and easy-to-learn spreadsheet software.


A focus group (FG) study is a structured series of group discussions designed to reveal perceptions and opinions on a defined issue involving carefully chosen participants who share common characteristics (Krueger & Casey, 2000). Focus groups are widely used in many forms of applied research including: needs assessment, program evaluation, curriculum development, product/service design, and market research. The "data" from a FG study are verbal comments made by participants in response to the interviewer's (also called a moderator) questions and from other participants' comments as well as the moderator's or other observer's field notes regarding informative nonverbal behavior in the group (e.g., behavior that indicates the extent of agreement, emphasis, boredom, and so forth, group members exhibit in regard to a topic or question). There are many methods available for analyzing FG data discussed in a variety of texts (e.g., Bloor, 2001; Krueger & Casey, 2000; Krueger, King & Morgan, 1998). There are also a fair number of sophisticated software programs available to assist the researcher with data analysis (e.g., QSR NUD*IST, Grahan & Hannibal, 1998; MARTIN, Higgins, 1998; Catterall & MacLaran, 1998). The purpose of this article is to describe how FG data can be analyzed with software that most researchers are familiar with and have access to--spreadsheets--thus reducing the learning curve and increasing the speed with which reports can be prepared.


Two primary goals of FG analysis are to: (a) reveal the important themes and their degree of emphasis that underlie participants' comments with regard to the study questions, and (b) to compare these themes across different types of groups. For example, a FG group study on college students' perceptions of tobacco prevention and control campaigns would seek to reveal the themes or categories of comments regarding ideas that may help college students quit smoking. Analysis of FG data may reveal such themes as restricting access to cigarettes, creating more smoke-free environments, addressing concomitant concerns of weight loss and stress reduction, and so forth. Furthermore, comparing themes that arise from FGs with smokers to FGs with nonsmokers may reveal that the former place more emphasis on addressing concomitant concerns whereas the latter stress environmental changes.

With a little planning and foresight, and minimal facility with spreadsheet software commands, FG data are easily organized and analyzed with modern electronic spreadsheet programs. The examples used in this article were prepared with Microsoft[R] Excel from an unpublished focus group study on perceptions of the health insurance gap in the state of Illinois. However, all of the features discussed are available on other commercially available software programs, such as NUD*IST and NVivo.


Data from FGs are primarily collected in two forms: field notes and written transcripts. Field notes are notes taken by an observer or assistant moderator who attempts to capture as much of the dialogue among group members and the moderator as possible. If the observer is a facile typist, these notes can be taken on a laptop computer during the meeting, which facilitates transfer of notes to the spreadsheet. Otherwise the field notes must be typed after the meeting. A transcript is a verbatim, typed record of a FG session taken from an audio- or video- (with audio) tape of the meeting. Both field notes and transcripts should be augmented with the moderator's and observer's notes on the important nonverbal behavior that coincided with participants' comments, especially if the nonverbal behavior provides additional information regarding emphasis, agreement/disagreement, confusion, or boredom.

Whether analyzing field notes or transcripts, the final document to be used in the analysis should be a word-processor file containing the following information: (a) group identification code; (b) questions asked in the FG; and (c) participants' responses to each question with nonverbal behavior notes typed in parenthesis in ALL CAPS following the remark(s). If tracing comments to the speaker is important to the study, then their name (or pseudonym) should also follow (or precede) the comment in ALL CAPS. Special formatting techniques to distinguish nonverbal notes or speaker names, such as boldface, italics, or underlining will not transfer to the spreadsheet. The most critical thing to do when preparing the FG notes is to separate each speaker's remarks with a hard return, therefore, making each comment a separate paragraph. When the data are transferred to the spreadsheet, each paragraph will become a Separate cell. Figure 1 provides an example of an excerpt from FG field notes made ready for transfer to a spreadsheet.



Once the field notes are cleaned and complete (e.g., missing or confusing field notes are clarified by listening to the audiotape) or transcripts are prepared and are in the format described above, the data are ready to be moved (copied) to the spreadsheet. This is essentially a copy and paste operation. Simply copy the entire word processor document and paste it to the first cell in a blank spreadsheet.


When the data, including the group identification code, questions, and comments, are transferred to the spreadsheet, everything will be in one, narrow column. Moving some of this information around and executing simple formatting commands will prepare this file for analysis. Figure 2 shows how the final formatted spreadsheet will look prior to analysis.

Insert an empty row above the first line of text (to create a row for column labels). Insert four empty columns to the left of the first column.

Label the first column, "Sequence." Label the second column, "Group Code" (or Group ID). Label the third column, "Group Type," label the fourth column "Question," and label the fifth column, "Comment" (additional columns will be labeled later).

Cut the Group ID that is at the top of a file for a particular group's transcript or field notes, and paste it in column 2 in the row parallel to the first comment. Copy this Group ID in every cell of column 2 containing that particular group's comments. In the example provided in Figure 2, the code is structured so that it contains information about the group region (FGs were conducted in five regions of the state), group type (there were seven types of FGs), and group sequence within a given region and group type. Therefore the entire code identifies a unique group. We could break this code apart to organize FGs by region or type. For this study we were interested in group type (indicated by the middle portion of the code), so we created a new variable, Group Type, and entered this information into the Group Type column.

Widen the fourth and fifth columns to about 2-3 inches each, and format the cells in these two columns so that the text wraps within the cell. This will allow you to see the entire question and comment on the screen.

Cut the first question from column 5 and paste it in column 4 on the same row as the first comment. Then copy the question in the cells of column 4 next to each comment that corresponds to the question. Repeat this procedure for the remaining questions and comments. Eliminate the empty rows that are created when the questions are moved from above their respective comment to beside the comment.

In a similar manner, cut the participant's name from a comment and paste it next to the comment in column 6 (now label this column, "Name"), and cut any nonverbal behavior notes from a comment and place it on the same row as the comment in column 7 (labeled "Nonverbal").

Read through each participant's comments (in column 5) and decide whether any one comment addresses more than one substantive issue or theme. If so, cut the comment into its respective themes placing subsequent portions of the comment on a line below the first portion of the comment (insert a new line below the original comment before the next speaker's comment). Fill in the appropriate information in columns 2, 3, 4, 6, and 7. Ton enhance reliability, it would be appropriate to have a second person, such as an "auditor" examine these decisions.

In the first column (Sequence) number each row sequentially from 1 to k (where k is the last comment). Make sure these numbers are values and not calculations (calculations can be transformed to values), because the purpose of this column is to be able to sort the spreadsheet file to its original order.


The data are now ready to be coded. The essential goal is to develop a limited set of codes corresponding to themes that cut across numerous comments. There are a variety of options for developing a coding strategy and analyzing the data that will not be presented in this article. The reader is referred to Krueger & Casey, (2000) and Krueger et al., (1998) for additional resources. Most of the strategies for analyzing focus group data can be accommodated with this general spreadsheet approach.

To start the analysis either sort the file by question (e.g., all responses to question 1 regardless of what type of group it came from) or type of group, whichever makes sense to the analyst. Create a new column (perhaps immediately to the right of the Comments column) and label it "Theme Code 1." The remainder of these instructions are oriented toward coding comments that are sorted by question, but similar processes should be followed if the file is sorted and analyzed by group type.

Read a comment and decide on a one- or two- word descriptor that will serve as a temporary label for the theme that comment addresses. Put that descriptor in the column labeled Theme Code 1 in the same row as the comment that is being coded. Continue in the same manner until there are no new theme codes. Move to the next question and repeat the procedure--decide on a descriptor for the comment and type it in the Theme Code 1 column next to the comment, and continue until themes become redundant. Finish the remaining questions in a like manner. Again, a second analyst or auditor may be utilized to assure the reliability of these decisions

The next step is to polish the coding system and complete the coding of the remaining comments. Polishing the coding system involves deciding whether related themes should be collapsed into one or two codes or whether the themes should be more thoroughly distinguished. To do this, sort the file by Theme Code 1, and then by Question (in some cases, themes cut across questions). After the file is resorted, first make sure that themes are consistently labeled. For example, the analyst might have started to use the code, "Universal Health Care" as a code for comments regarding national health care plans (e.g., in a study of perceived solutions to health insurance gaps), but then switched to using the code "Universal Health Insurance" or "Single Payer" to refer to the same theme. Decide which theme code should be used, and recode the errant comments accordingly, and resort by Theme Code 1 and then by Question. Second, examine the codes for similar ideas and decide whether themes should be coded the same or kept separate. For example should the themes "Universal Health Insurance" and "Government-Sponsored Health Insurance" be collapsed or kept separate? The analyst must decide by reading the comments corresponding to these themes and deciding whether they should be collapsed or not. If collapsing, then choose the appropriate code, and recode all the previously coded comments that correspond to this theme with the new code. Resort the file by Theme Code 1 and Question again.


Now that the coding system is polished and condensed, a theme dictionary should be created. The theme dictionary contains an alphabetized listing of themes with a more thorough definition or explanation of the theme, and one or two representative quotes. If your spreadsheet permits multiple pages in the same file, then this dictionary can be created on a new page. Otherwise either open a new, blank spreadsheet file or a blank word processing file. The theme dictionary will have three columns, labeled Theme, Definition, and Quote, respectively. Your focus group data file should be alphabetized according to theme. Copy the first theme code into the first column of your theme dictionary. After reviewing the comments that are associated with that theme, write a brief, but descriptive explanation of that theme in the column labeled "definition." Look through the comments associated with that theme and choose one or two that well represent that theme. Copy these comments from the focus group data file to the theme dictionary file in the column labeled "quote." Continue in like manner until all themes are included and defined in the theme dictionary. Table 1 provides an abbreviated example of a theme dictionary.

The theme dictionary provides a useful guide for coding the remaining comments and for developing the narrative report of the focus group findings. The remaining comments should now be coded using the themes listed in the theme dictionary. On occasion, a new theme may arise. This is likely to happen if the theme dictionary is developed before all of the focus group data are collected. If a new theme is warranted, simply add the theme to the theme dictionary, and code the comments accordingly.


The researcher may be interested in sub themes, overarching themes, or other ways of coding and analyzing the comments than just by the primary content themes codified in the Theme Code 1 column. For example, comments could be further coded to reflect emotional or evaluative themes, such as "resistance," "concerns," or "positive regard." To add secondary themes, create a new column (or set of columns), labeled "Theme Code 2" (and so forth), and code the comments with regard to the secondary themes. Add these themes to the theme dictionary and define accordingly.


Now that the FG data are fully coded, the analyst has the task of examining the meaning of the findings and organizing the findings in a way that is enlightening to the intended audience. Again, the analyst has choices in how to organize the findings. A straightforward strategy is to analyze the themes that correspond to each of the questions in the FG protocol. For example, if the first substantive question was "What are the effects of not having health insurance?", each theme corresponding to this question would be listed and discussed, and representative quotes would be provided. The theme dictionary can be consulted to assist with the discussion of the theme and to provide representative quotes. To analyze the data in this manner, sort the FG data file by question, and then by theme. The file could be further sorted by group type, if comparisons across groups are of interest. The "findings" section of the report then might look like the example in Figure 3.

Alternatively, the analyst may choose to study the results by primary themes, regardless of the question asked, or by secondary (e.g., overarching) themes, or by group type. The choice is left to the analyst and should follow the purpose of the study. Regardless of the framework for organizing the data, a fully coded spreadsheet with a thorough theme dictionary should permit a wide variety of analytic strategies.


Data from focus group studies are the conversations and attendant nonverbal behavior from select group of participants who respond to a carefully structured, but open-ended question protocol. The purpose of the focus group is to gain perspectives, enlightenment, and insight into the minds of these participants as they discuss the topic of interest to the research. These non-quantitative data may appear daunting to analyze because strategies vary widely, there are no standardized analytic strategies, and many specialized software packages are difficult or time consuming to learn. This article has articulated a strategy for analyzing FG data using widely available and easy-to-learn spreadsheet software, which, hopefully, will enable health professionals, as well as those in other disciplines, to more effectively utilize FG methodology.


Responsibility IV: Evaluating Effectiveness of Heatlh Education Programs

Competency A: Develop plans to assess achievement of program objectives.

Subcompetency 8: Develop valid and reliable evaluation instruments.
Figure 2. Arrangement amd Formatting of Data in the Spreadsheet Prior
to Analysis.

 A B C D
1 Sequen Group ID Group Question
 ce Type

2 1 NI_2_01 2 1. Many businesses, especially small
 businesses, do not offer health ins. to
 their employees. What barriers prevent
 or impede small businesses like yours
 from offering health care benefits.
3 2 NI_2_01 2 1. Many businesses, especially small
 businesses do not offer health ins. to
 their employees. What barriers prevent
 or impede small businesses like yours
 from offering health care benefits.
4 3 NI_2_01 2 1. Many businesses, especially small
 businesses, do not offer health ins. to
 their employees. What barriers prevent
 or impede small businesses like yours
 from offering health care benefits.
5 8 NI_2_01 2 2. If you were to offer health
 insurance to your employees, what would
 be a minimum benefit policy that you
 might feel appropriate for a small
6 9 NI_2_01 2 2. If you were to offer health
 insurance to your employees, what would
 be a minimum benefit policy that you
 might feel appropriate for a small
7 12 NI_2_01 2 2. If you were to offer health
 insurance to your employees, what would
 be a minimum benefit policy that you
 might feel appropriate for a small

 A E C
1 Sequen Comment Name Nonverbal

2 1 Cost is a big factor due to our (BOB)
 small size.

3 2 No. 1 is cost in trying to run (JOHN)
 the business as efficiently as
4 3 All costs, like unemployment, (PAM)
 are going up and that adds to
 our problem of providing
 health insurance.
5 8 It would be the same as the (PAM)
 HMO Act required.
6 9 Hospitalization, outpatient, (JOHN)
 mental, drugs would be
 included, maybe with co-pay.
7 12 Preventative care would be (DOROTHY) (HEAD
 really nice. It would keep NODDING IN
 costs down. AGREEMENT)

Figure 3. Example of a Focus Group Report.

What are the effects of not having health insurance?

Being ill more often. Uninsured focus group participants as well as health care providers believed that their health was compromised because they could not afford to go to the doctor for minor illnesses. Representative Quote: I didn't see a doctor for a cold, which turned into pneumonia. (uninsured person)

Inability to find affordable health care. The uninsured had difficulty finding health care providers who reduced their fees or would permit them to pay their bills a little at a time, therefore they often went without a medical provider.
Table 1. Example ofaTheme Dictionary, Abbreviated.

 Theme Definition Representative Quote

Universal Health Care A state or federal "Universal health
 health insurance care but with some
 program that covers element of personal
 all citizens. responsibility
 added back in."

Beneficiary Contribution Individuals/families "Paying some helps
 who receive health us feel that it is
 insurance pay a not a hand out."
 portion of the
 premium or

The author acknowledges Caryl Cox, Dan Shannon, Paul Sarvela, Rachel Ruetter, and Jennifer Hobson for their contribution to the research that is used in the examples in this paper.


Bloor, M. (2001). Focus group in social research. London: Sage.

Catterall, M., & MacLaran, P. (1998). Using computer software for the analysis of qualitative market research data. Journal of the Market Research Society, 40, 207-222.

Gahan, C., & Hannibal, M. (1998). Doing qualitative research using QSR NUD*IST. London: Sage.

Higgins, J. W. (1998). Social marketing and MARTIN: Tools for organizing, analyzing, and interpreting qualitative data. Qualitative Health Research, 8, 867-876.

Krueger, R. A., & Casey, M. A. (2000). Focus Groups: A Practical Guide for Applied Research (3rd Ed.). Thousand Oaks, CA: Sage.

Krueger, R. A., King, J. A., Morgan, D. L. (1998). Focus Group Kit. Thousand Oaks CA: Sage.

Margaret S. Stockdale, Ph.D. is an Associate Professor of Psychology at Southern Illinois University at Carbondale. Address correspondence to Margaret S. Stockdale, Ph.D., Associate Professor of Psychology, Southern Illinois University at Carbondale, Carbondale, IL 62901-6502; PHONE: 618.453.3549; FAX: 618.453.3563; E-MAIL:
COPYRIGHT 2002 University of Alabama, Department of Health Sciences
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2002, Gale Group. All rights reserved. Gale Group is a Thomson Corporation Company.

Article Details
Printer friendly Cite/link Email Feedback
Author:Stockdale, Margaret S.
Publication:American Journal of Health Studies
Date:Dec 22, 2002
Previous Article:Comparison of a 5-a-Day social marketing intervention and school-based curriculum.
Next Article:Positioning social marketing as a planning process for health education.

Related Articles
Electronic business tool.
Spreadsheets: big-picture views.
Spreadsheets: faster, smarter.
Using integrated spreadsheets.
When is a database not a database?
The power of spreadsheets.
Add perspective to spreadsheets.
How to link to Web data.
Using BI tools to turn information into action. (Special Report: Business Intelligence).
Cut and paste errors a thing of the past with new spreadsheet system.

Terms of use | Privacy policy | Copyright © 2021 Farlex, Inc. | Feedback | For webmasters |