Data Analysis with Spreadsheets

In 2020, England lost nearly 16,000 COVID test results because of an Excel row limit

Public Health England was tracking COVID-19 test results in an Excel spreadsheet. They were using the older .xls format, which maxes out at 65,536 rows. When the dataset grew past that limit, new rows simply vanished. Nearly 16,000 positive test results were silently lost — meaning thousands of infected people were never contacted for contact tracing during a critical period of the pandemic.

The problem was not that someone wrote a bad formula. The problem was that no one had set up the data for proper analysis — no structured table, no validation, no overflow checks. Raw data was dumped into a spreadsheet with no guardrails.

This module teaches you how to analyze data in spreadsheets without losing it, breaking it, or misreading it.

Sorting: the simplest analysis you can do

Sorting rearranges your data by one or more columns. It sounds trivial, but sorting is how you answer questions like "Who are our top 10 customers?" or "Which product sold the least last quarter?"

Single-column sort: Click any cell in the column, then Data → Sort A-Z (ascending) or Z-A (descending).

Multi-level sort: Data → Sort → Add Level. Example: Sort by Region first, then by Sales (largest to smallest) within each region.

Custom sort orders: Need to sort months chronologically instead of alphabetically (which puts April before January)? Custom sort lets you define your own order: Jan, Feb, Mar, Apr... instead of Apr, Aug, Dec, Feb.

Filtering: show only what matters

Filters hide rows that do not match your criteria — without deleting them. The data is still there; you just cannot see the irrelevant parts.

Filter types:

There Are No Dumb Questions

"If I filter data and then use SUM, does it sum only the visible rows?"

No — SUM adds everything, including hidden rows. Use =SUBTOTAL(109, D2:D100) instead. The 109 tells Excel to sum only visible cells. This is one of the most common filtering mistakes. Alternatively, use =AGGREGATE(9, 5, D2:D100) for even more control.

"Can I filter and then copy-paste just the visible rows?"

Yes, but you need to select the visible cells first. After filtering, select your range, then press Alt+; (Windows) or Cmd+Shift+Z (Mac) to select only visible cells. Now copy-paste works as expected.

Conditional formatting — make data tell you where to look

Conditional formatting changes a cell's appearance (color, font, icon) based on its value. Instead of scanning 1,000 rows of numbers, your eyes are drawn immediately to what matters.

Practical examples:

• Highlight all cells in the "Profit" column that are negative → red fill
• Color scale on monthly revenue → see seasonal patterns instantly
• Icon set on delivery status → green check for delivered, yellow clock for in transit, red X for delayed

Data validation — preventing bad data at the source

Data validation restricts what can be entered into a cell. Instead of cleaning up bad data after the fact, you prevent it from being entered at all.

How to set it up: Select cells → Data → Data Validation → Choose criteria → Add an input message (tells users what to enter) → Add an error alert (tells users what went wrong).

There Are No Dumb Questions

"Can people bypass data validation by pasting data?"

Yes — and this is an important limitation. If someone copies data from another source and pastes it into a validated cell, the validation can be bypassed. This is why data validation is a guide, not a lock. For critical data, combine validation with periodic checks (like COUNTIF to find duplicates).

"What happens to existing data when I add validation to a column that already has data?"

Nothing happens to existing data. Validation only applies to new entries. To find existing values that violate your rules, go to Data → Data Validation → Circle Invalid Data. Excel will circle any cells that do not meet the criteria.

Removing duplicates

Duplicate data inflates your counts, skews your averages, and makes reports unreliable. Excel makes removal simple:

1. Select your data range
2. Data → Remove Duplicates
3. Choose which columns to check (check all columns for exact row duplicates, or specific columns for partial matches)
4. Excel tells you how many duplicates were found and removed

Structured tables — the feature that changes everything

A structured table (Insert → Table or Ctrl+T) converts a plain range into an intelligent data object. This is the single most underused feature in Excel.

Why tables matter: When you add a new row of data to a structured table, every formula, chart, and pivot table that references the table automatically includes the new row. With plain ranges, you have to manually update every reference. This alone prevents an enormous category of spreadsheet errors.

Key takeaways

• Sorting answers "what are the top/bottom values?" — always sort the full data range, never a single column in isolation
• Filtering hides irrelevant rows without deleting them — use SUBTOTAL (not SUM) to calculate on visible rows only
• Conditional formatting directs your eyes to exceptions and patterns — use it sparingly on what matters most
• Data validation prevents bad data at entry — dropdown lists for categories, number ranges for quantities, custom formulas for uniqueness
• Remove Duplicates cleans data but is destructive — always identify duplicates visually first, then remove
• Structured tables (Ctrl+T) automatically expand formulas, charts, and pivot tables when new data is added — use them for every dataset