Introduction

Last updated on 2026-10-26 | Edit this page

Estimated time: 18 minutes

Overview

Questions

  • What are spreadsheets useful for in a research project?

Objectives

  • Define the scope of this lesson
  • Describe some drawbacks and advantages of using spreadsheet programs

Good data organization is the foundation of your research project. Most researchers have data or do data entry in spreadsheets. Spreadsheet programs are very useful graphical interfaces for designing data tables and handling very basic data quality control functions.

Spreadsheet outline

In this lesson, we’re going to talk about:

  • Good data entry practices - formatting data tables in spreadsheets
  • How to avoid common formatting mistakes
  • Recognising and reformatting dates in spreadsheets
  • Basic quality control and data manipulation in spreadsheets
  • Exporting data from spreadsheets

Spreadsheet programs

Many spreadsheet programs are available. We will use Microsoft Excel in our examples. Although it is not open source software it is very widely available and used.

Free spreadsheet programs such as LibreOffice are available. The functionality of these may differ from Excel, but in general they can be used to perform similar tasks.

Problems with Spreadsheets

Spreadsheets are good for data entry, but in reality we tend to use spreadsheet programs for much more than data entry. We use them to create data tables for publications, to generate summary statistics, and make figures. Laying out spreadsheets in this way often adds some difficulty when we want to take our data from the spreadsheet and use it in another program. Additional white space, merged cells, colour and grids may aid readability but are not easily handled by other programs that take our spreadsheet as an input to further analysis.

Generating statistics and figures in spreadsheets should be done with caution. The graphical, drag and drop nature of spreadsheet programs means that it can be very difficult, if not impossible, to replicate your steps (much less retrace anyone else’s). This is particularly true if your stats or figures require complex calculations. Furthermore, when performing calculations in a spreadsheet, it’s easy to accidentally apply a slightly different formula to multiple adjacent cells. This often makes it difficult to demonstrate data quality and consistency in our analysis.

Even when we are aware of some of the limitations that data in spreadsheets presents, often we have inherited spreadsheets from another colleague or data provider. In these situations we cannot exercise any control in its construction or entry of the data within it. Nevertheless it is important to be aware of the limitations these data may present, and know how to assess if any problems are present and how to overcome them.

Callout

What this lesson will not teach you

  • How to do statistics in a spreadsheet
  • How to do plotting in a spreadsheet
  • How to write code in spreadsheet programs

If you’re looking to do this, a couple of good references are the Excel Cookbook, published by O’Reilly, and the Microsoft Excel 365 bible.

Challenge

Exercise

  • How many people have used spreadsheets in their research?
  • How many people have accidentally done something that made them frustrated or sad?

Best uses for spreadsheets

However, there are circumstances where you might want to use a spreadsheet program to produce “quick and dirty” calculations or figures. Some of its features can be used in data cleaning, prior to importation into a statistical analysis program. We will show you how to use some features of spreadsheet programs to ensure proper data entry and check your data quality along the way.

Workshop Data

If you are not already familiar with the data, for this lesson and the other lessons in the Social Science curriculum we will be using the Studying African Farmer-led Irrigation (SAFI) Dataset.

The data used in these lessons are taken from interviews of farmers in two countries in eastern sub-Saharan Africa (Mozambique and Tanzania). These interviews were conducted between November 2016 and June 2017 and probed household features (e.g. construction materials used, number of household members), agricultural practices (e.g. water usage), and assets (e.g. number and types of livestock).

This is a real dataset, however, it has been simplified for this workshop. If you’re interested in exploring the full dataset further, you can download it from Figshare and work with it using exactly the same tools we’ll learn about today.

Key Points
  • Good data organization is the foundation of any research project.
  • Spreadsheets are good for data entry, but when doing data cleaning or analysis, it’s not easy to show or replicate what you did.