Content analysis: Quick guide using Excel

Qualitative data

Qualitative data in evaluation helps to understand individuals’ perspectives on a given topic. In service evaluation, qualitative data is often used in conjunction with quantitative data to understand the topic under consideration. 

Analysis

Quantitative data may be analysed by computer programme, such as Excel, to calculate the mean of a set of data. In contrast, qualitative data must be analysed by a human. There are computer packages that can help an individual organise the qualitative data, but the analysis relies on the individuals’ skills at interpreting the data.

In the Integrated Care Board (ICB) surveys are commonly used to collect qualitative data. This may be done using one of two survey packages (MS forms or Survey Monkey). Typically, the outputs from both these packages are an Excel spreadsheet showing responses from individual in a row and the question the responses relate to in each column. We briefly summarise how Excel may be used to undertake qualitative data analysis.

Method

The stages involved in content analysis and how each stage is applied to analysing qualitative data in Excel are explained.

1. Create a copy of the raw data onto a new worksheet, name the worksheet something sensible, e.g., qualitative data and delete the quantitative data.
2. Insert a new column next to each set of qualitative data, name this column something sensible, e.g., brief notes read 1.
3. Read through the text making brief notes in the new column when interesting or relevant information is found. This stage may need to be repeated multiple times.
4. Insert a new column next to the ‘brief notes’ one and name this column something sensible, e.g., ‘observations 2’. Go through the notes made in the margins and list the different types of information found.
5. Insert a new column next to the ‘observations 2’ one and name this column something sensible, e.g., ‘1st categorisation’. Read through the list created in stage 4 and categorise each item in a way that offers a description of what it is about.
6. Insert a new column next to the ‘1st categorisation’ one and name this column something sensible, e.g., ‘Theming’. Identify whether or not the categories can be linked any way and list them as major categories (or themes) and / or minor categories (or themes).
7. Compare and contrast the various major and minor categories.
8. If there more than one question requiring a qualitative response was asked, repeat the first seven stages again for each question.
9. When you have done the above with each of the questions, collect the categories or themes together and examine each in detail and consider the fit and its relevance. This is best done on a new Excel worksheet, name this something sensible, e.g., themes.
10. Once all the qualitative data is categorised into minor and major categories/themes, review to ensure that the information is categorised as it should be.
11. Review all of the categories and ascertain whether some categories can be merged or if some need to them be sub-categorised.
12. Return to the original qualitative data and ensure that all the information that needs to be categorised has been so.

If there is more than one individual analysing the data, analysis should be undertaken independently by each analyst then reviewed together to cross check categories/themes against each other and agree the final categories/themes.

Summary

The process of content analysis is lengthy and may require going over and over the data to ensure a thorough job of analysis has been completed. When writing up the qualitative analysis use quotes from the data to illustrate themes/categories.

Suggested further reading

Burnard, P., Gill, P., Stewart, K., Treasure, E., & Chadwick, B. (2008). Analysing and presenting qualitative data. British dental journal, 204(8), 429-432. Available from: https://www.nature.com/articles/sj.bdj.2008.292 

Krippendorff, K. (2018). Content analysis: An introduction to its methodology. Sage publications. Note: This book is available to download as a free pdf, use https://scholar.google.co.uk/ to search for this

Thorne, S. (2000). Data analysis in qualitative research. Evidence-based nursing, 3(3), 68-70. Available from: https://ebn.bmj.com/content/3/3/68.short