Text guides

Data and spreadsheets

Define the rows, check the units and make the calculations explainable. Use small anonymous examples when asking for help.

12 practical tasks. Each guide includes a sample request and a way to check the answer.

Choose useful columns before building a spreadsheetList the decisions the table must support, then define what one row stands for. Add a column only when a decision, a filter or a calculation needs it. Give each column one type and one fact per cell, and write a sample value for it before you build anything.
Describe a spreadsheet formula problem clearlyName the input columns, say what the output cell should show, and include a few sample rows with the result you expect for each. Say which spreadsheet program you use, since function names and behavior differ between programs. The sample rows let you check any proposed formula before you rely on it.
Plan a safe review of duplicate rowsTwo rows with the same name can represent different people, orders or visits. Decide what makes a record unique before asking for duplicates to be removed. Start with a copy of the file and a few anonymous examples that show the awkward cases.
Write data cleaning rules before editing a fileWrite one rule per field before touching the file. State the problem you see, the change you propose, an example before and after, and the cases the rule should skip. Work on a copy and keep the original column, so every edit can be traced and undone.
Find ambiguous dates in a spreadsheetA value like 04/05/2024 can mean April 5 or May 4. List every date with two valid readings, note where the file came from, and confirm the intended order with a person or the source system. Convert only after that confirmation.
Check a table for mixed unitsScan each numeric column for values that use different units or scales, such as pounds beside kilograms or thousands beside single units. Record the stated unit, each variant you find, and the source of every conversion. Leave a value unresolved when its unit is unclear.
Choose a chart for the question you want answeredWrite down the question your reader will ask, then list the fields that answer it. Pick the chart that matches the comparison: change over time, parts of a whole, or one group against another. Before building anything, note which common charts would mislead for this data.
Summarize survey results with the sample size visibleReport each result next to the number of people who answered that question, not the number who started the survey. Say how many skipped it, and describe the findings as applying to respondents. Whether they stand for a wider group depends on how people were invited.
Write notes for someone inheriting a spreadsheetA new owner needs to know what the workbook is for, where data enters, which cells calculate, how it gets refreshed and who maintains it. Write the notes by walking through the file as a stranger would, and record every step that currently lives only in your memory.
Make a data dictionary for a small projectA data dictionary lists every field with one agreed meaning, a data type, allowed values and what a blank means. Build it from the real column headers and a few sample rows, then settle any field where two people would describe it differently.
Compare two lists without losing unmatched recordsChoose a matching key and check whether it is unique in each list. Assign every source row a reference before comparing, so unmatched and ambiguous entries remain traceable. Keep duplicate-key groups separate for review rather than forcing a match, and reconcile counts for each original list.
Build checks around a spreadsheet summaryPair every summary figure with a check that reaches the same number a different way, such as a separate total, a row count or a relationship that must hold. A check that reuses the same formula can repeat the same mistake.

Related topics