The five things that break a spreadsheet

A new analyst is asked to clean the Bisi Provisions export. She opens it and starts fixing whatever she sees first: a misspelled branch on row 12, a naira sign on row 40, a date on row 95. Three hours later she's fixed hundreds of cells, and she still doesn't know whether she's found everything, or created new problems along the way.

An experienced analyst works differently. First, she diagnoses the whole file: what kinds of problems exist, where, and how many. Only then does she fix them, one type at a time, in a sensible order, recording each step.

This module teaches that second way. This lesson is the diagnosis.

Words in this lesson

What "good" looks like

In their paper Data Organization in Spreadsheets, Broman and Woo describe how data should be organised. Their recommendations include:

• Be consistent: the same codes, names and formats throughout.
• Put just one thing in a cell.
• Make it a rectangle: rows for records, columns for variables, with a single header row.
• No empty cells: use a consistent code for missing values.
• Write dates as YYYY-MM-DD.
• No calculations in the raw data, and don't use colour as data.

Every problem below is a way the Bisi file breaks those principles.

The five things that break a spreadsheet

1. The table isn't a clean rectangle

Why it breaks things: tools expect row 1 to hold the headings and every row below to be one record. Summaries and filters stop at blank rows, or include the total as if it were a sale.

2. Numbers stored as text

Why it breaks things: from lesson 1.1, Microsoft's documentation says Excel's SUM ignores text values, so text amounts silently go missing.

3. Dates in several formats

Why it breaks things: dates stored as text sort alphabetically, not by time, and "02/01/2026" can be read as 2 January or 1 February depending on settings.

4. The same thing written different ways

Why it breaks things: every spelling becomes its own group, so one branch becomes three, and one customer becomes five.

5. Duplicates

The Bisi raw file has 11 lines that are exact copies of the line just above them. For example, row 7 repeats row 6.

Why it breaks things: those sales are counted twice.

Plus: values that are simply wrong

Some problems aren't about format at all. Two rows in the Bisi file have a Total Amount that doesn't equal Qty × Unit Price:

These are keying errors. No amount of formatting fixes them. Only a sanity check finds them, which you'll build in lesson 2.2.

Diagnose before you fix

Use Column stats in Google Sheets

According to Google's help centre, Data → Column stats shows insights about a column's values, including counts, distribution, a frequency table of the most and least common values, and summary statistics. It's a fast way to see all nine spellings of "Branch".

Use Cleanup suggestions

Google's help centre describes Data → Data cleanup → Cleanup suggestions, which can help identify problems like extra spaces, duplicates, number formatting, anomalies and inconsistent data. Treat suggestions as a starting point, not a replacement for checking yourself.

Use formulas that reveal problems

Google's help for UNIQUE also gives a warning that matters for cleaning: rows that look like duplicates may differ by hidden text such as trailing spaces.

Record the diagnosis

In your notes sheet (lesson 1.2), write a diagnosis table before changing anything:

A sensible order for fixing

The order matters, because some fixes depend on others:

1. Remove what isn't data: title rows, total rows, blank rows.
2. Fix data types: numbers and dates.
3. Standardise text: spaces, capitals, spellings.
4. Handle duplicates, once values are consistent enough to compare.
5. Run sanity checks: totals, date ranges, impossible values.
6. Lock the structure into a table.

After cleaning, the Bisi January–March data should have 938 rows. You'll use that number to check your work at the end of this module.

Common mistakes

The short version

• Broman and Woo: be consistent, one thing per cell, a rectangle with a single header row, no empty cells, dates as YYYY-MM-DD.
• The five things that break a spreadsheet: not a rectangle, numbers as text, mixed dates, inconsistent text, duplicates. Plus wrong values that only sanity checks catch.
• Diagnose with Data → Column stats, Data cleanup → Cleanup suggestions, and formulas: UNIQUE, ISNUMBER, COUNT vs COUNTA, LEN, COUNTIF.
• Record a diagnosis table, then fix in order: remove non-data → types → text → duplicates → sanity checks → structure.
• Clean Bisi January–March data should have 938 rows.

Try it yourself

1. Open your workbook from Module 1, and go to the raw sheet.
2. Use Data → Column stats on Branch and Payment Type. Write down every spelling and its count.
3. In a scratch sheet, use =UNIQUE() on the Customer Name column. How many spellings do you get?
4. Use =COUNT() and =COUNTA() on the Total Amount column. Explain the difference in your own words.
5. Use =LEN() to look for a branch name with a hidden space. Google's help notes that Sheets trims text typed into cells by default, so depending on how your file was imported, the trailing space in "Yaba " may or may not have survived. Write down what you find either way.
6. Find rows 58 and 428, and check whether Qty × Unit Price equals Total Amount.
7. Complete the diagnosis table in your notes sheet, with your own counts.

You'll know you've got it when your diagnosis table lists every type of problem in the file, where it is and how many rows it affects, before you've changed a single cell.