52 weeks of BetterEvaluation: Week 17: Analyzing data using common software

Data analysis is sometimes the weak link in an evaluation plan.  Answering key evaluation questions requires thoughtful analysis - and this needs appropriate tools.

Specialist software is available for data analysis, such as SPSS and SAS (originally designed for analyzing quantitative data but now with capacity to analyze qualitative data), NVivo or HyperResearch (originally designed for analyzing qualitative data but now with capacity to analyze quantitative data).

But many organizations don't have this specialist software and can't afford to buy it.  Instead commonly available software, such as Microsoft Word and Excel (or their OpenSource alternatives such as Google Drive Spreadsheets, OpenOffice.org, Zoho sheet, and Excel Web App - software discovered via SmallBusinessComputing) can be used for analysis.

Using Excel for data analysis in evaluation

Ann Emery's Excel for Evaluation page provides a range of short videos explaining how to use Excel for data analysis, from importing datasets and cleaning data (checking data quality), sorting and arranging it, and doing numeric and graphical analysis.

In this series of Amy's tutorials, she covers inserting pivot tables, understanding the components and terminology, and using pivot tables to create crosstabs. 

More Excel resources

Using Excel for Qualitative Data Analysis Susan Eliot gives detailed guidance on using Excel to analyze structured qualitative data, such as answers in a structured interview or a questionnaire, where it makes sense to analyze data question by question  - summarizing responses to question 1 and then question 2.  (an alternative way to analyze data is to case by case, where you summarize a persons response to all questions). A similar approach could be used for unstructured data, such as a semistructured or unstructured  interview where issues are not necessarily addressed in the same order, or where data are extracted from documents such as project documentation.

Using Word for data analysis in evaluation

Word can also be used for data analysis, especially of qualitative data.

Here are two useful resources on using Word:

Analysing qualitative data using Microsoft Word Jenna Condie explains how the features of Word support more detailed coding, including developing detailed definitions of the codes, and keeping track of comments and emerging ideas about the data.

Simplifying Qualitative Data Analysis Using General Purpose Software Tools Nancy LaPelle explains how Word can be used for coding, retrieving, semi-automated coding, creating hierarchies of codes, counting the number of responses for each code, and annotating text.

Pros and cons 

Potential advantages of using commonly available software are:

• Reduced learning time, since it builds on existing knowledge
• Makes it easier for local units to engage in data analysis and exploring findings

Potential disadvantages of using commonly available software are:

• some functions are not available
• devolving analysis increases risks of errors - systems for error checking (including protecting calculation cells is needed)