A Gentle Introduction to Data Cleaning

Curriculum

5 Sections
17 Lessons
Lifetime

Expand all sectionsCollapse all sections

Introduction
In this section, learn about the “horror stories” of where data errors in spreadsheets have led to real consequences and how you can avoid them yourself.
1
- 1.1
  Course Outline
Section 1: Nuts and chewing gum
In this section, you’ll gain practical knowledge of common formatting and layout features of spreadsheets and some ideas about how to present your raw data to others so they will love you!
4
Section 2: The Invisible Man is in your spreadsheet, messing with your data
In this section, you’ll learn how even characters which you cannot see can cause havoc in data analysis. The section will teach you to ghostbust white space and other non-printable characters such as carriage returns, spaces and tabs. Once you have removed them, you can get on with your analysis in peace.
4
Section 3: Your data is a witch’s brew
Three is not a number. Nor is a million. At least not when they are typed in as text in a cell in a spreadsheet. Your spreadsheet is pedantic and needs everything to be precise and consistent. Read on to learn more.
4
Section 4: Did you bring the wrong suitcase (again)?
All our spreadsheet wants from us is each cell of data to be organised and neatly packed. In this section, you will analyse data to highlight problems in the way it has been structured so as to avoid them in your own work with data.
4

Introduction

*Image source (with adaptations). Public Domain licensed.*

Three is not a number. Nor is a million. At least not when they are typed in as text in a cell in a spreadsheet. Your spreadsheet is your awkward and pedantic friend that needs everything to be precise, defined and consistent. If you don’t do this, your spreadsheet will become confused and start making an evil witch’s brew, as depicted above. Here’s how you and your spreadsheet might see things differently:

*(If the witches in Shakespeare’s Macbeth (see IV, i, 14-15) had a spreadsheet it would look like the above).*

With spreadsheets, there are three basic data formats. Or in other words, three ways you can enter data into a spreadsheet cell:

as a number, like 100.
as text, like one hundred.
as a formula, like =SUM(99+1), which creates a “calculated value”.

To avoid creating a witch’s brew in your spreadsheet, follow two basic rules of thumb:

Be consistent and don’t mix them up in a column of data.
Let the spreadsheet know what type of data is in each column.

If you ignore these rules, your spreadsheet will have difficulty doing all the useful things for which we entered the data in the first place, like adding, subtracting, counting, sorting, filtering and so on. After completing the tasks in this section you’ll have gained: