Session 2: Spreadsheets for Data Work

Microsoft Excel and Google Sheets are the two tools this work is done in, and they share almost every concept: rows, columns, cells, references, formats, sorting, filtering. The differences that matter in practice are that Excel is what Nigerian employers and government forms assume, is faster on very large files, and has the deeper feature set; while Google Sheets is free, lives in the browser, saves automatically, and allows several people to edit at once — which is why remote clients often prefer it.

Learn Excel first, because its concepts are a superset and Sheets will feel familiar within an afternoon. Two Sheets-specific habits are worth knowing early: it stores everything in Drive so there is no save step, and its menu equivalents live under Data rather than under the Home ribbon. If a client sends you a .xlsx and you open it in Sheets, be careful about formats on download — round-tripping through the two tools can alter date and number formatting, so check a sample after converting.

A data sheet has one job structurally: be a clean rectangle. Headers in row 1, one per column, named clearly and consistently — no merged header cells, no two-row titles, no decorative banner above the data. Data from row 2 downward, one record per row, with no blank rows inside the data — Excel and Sheets both detect the extent of a dataset by finding the first blank row, so one stray blank row silently truncates every sort, filter and formula range you apply. And no merged cells anywhere in the data: a merged cell stores its value in only the top-left position, so the others read as empty and sorting breaks.

Then make it a real table. In Excel, select the data and press Ctrl+T to convert it to a Table; in Sheets, use Data → Create a filter or Data → Named ranges. A table gives you filter buttons, banded rows that make scanning easier, a header that repeats when printing, and — most valuable — automatic expansion, so new rows inherit the column's format and formulas instead of arriving unformatted and silently outside your ranges.

Dates are the most common source of quiet failure. Excel and Sheets store dates as serial numbers, which is what makes date arithmetic possible — subtract one date from another and you get days. But that only works if the value was recognised as a date, and the giveaway is alignment: real dates and numbers right-align, text left-aligns. If your date sits on the left, it is text and no calculation will ever work on it. Enter dates in an unambiguous form and let the number format handle display, because 03/04/2026 means April the third to some people and the third of April to others.

Names need consistency more than correctness. Decide once whether you store Surname and First Name in separate columns (recommended, because it sorts and searches properly) or a single Full Name column, and whether you use Title Case throughout. Inconsistent capitalisation — 'adebayo', 'ADEBAYO', 'Adebayo' in the same column — breaks deduplication and looks unprofessional in any report. Use PROPER() to fix a whole column at once if you inherit a mess.

Phone numbers are text, always. Format the column as Text before typing, or prefix with an apostrophe, so the leading zero survives and the value never becomes scientific notation. Decide on one format — `0803 456 7890` is the most readable Nigerian convention — and apply it consistently, because mixed formats make deduplication impossible. Addresses should be split into at least Street, City and State; a single address blob cannot be filtered by city, which is one of the first things a client asks for.

Sorting reorders rows so patterns become visible. Excel: Data → Sort. Sheets: Data → Sort range. You can sort by one column or add levels — sort by City, then within each city by Surname — which is how you produce a grouped report. The rule that prevents disaster is to always sort the whole table, never a single column. Sorting one column alone shuffles it against the others and destroys the relationship between a name and its phone number, which is a data-integrity failure you may not notice for weeks. If the tool prompts 'Expand the selection', choose it; if you have used a real Table, the problem cannot arise.

Sorting is also your best error-finding tool. Sort a numeric column ascending and the outliers appear at both ends — an amount ten times larger than the rest, or a negative value that should not exist. Sort a text column and the inconsistencies group together, so 'LAGOS', 'Lagos' and 'lagos' sit adjacent and become obvious. Sort by date and the impossible entries — a 1900 date, a future date — surface immediately. Whenever a dataset feels wrong, sort it; it is faster than reading.

Filtering hides rows that do not match a condition while leaving the data intact. Turn on filter buttons (Data → Filter), then use the dropdown on any column to tick specific values or apply conditions such as 'greater than' or 'contains'. This is how you answer a client question in three clicks — 'show me everyone in Port Harcourt who joined this year' — and how you inspect one field across the whole dataset without scrolling. To clear, use Data → Clear; nothing was ever deleted, only hidden. One important note: a SUM over a filtered range still counts the hidden rows, so use SUBTOTAL(109, range) when you want the visible total.

Duplicates are inevitable in real data — the same customer entered twice, the same invoice captured by two people. Find them with Conditional Formatting → Highlight Cells Rules → Duplicate Values to see them, then Data → Remove Duplicates to delete them. Read the Remove Duplicates dialog carefully before confirming: it deletes rows that match on the columns you have ticked, so if you tick only Surname you will delete every second person who shares a surname. Tick the columns that together define a unique record — usually the full set of identifying fields — and always work on a copy of the sheet first, because the deletion is immediate and not always undoable.