The spreadsheet somebody else built

What actually goes wrong with data

Not analysis. Cleaning. Names spelled four ways, dates in three formats, amounts with currency symbols and spaces, empty rows, merged cells, a “notes” column containing three different kinds of information, and a total row somebody typed by hand in 2023.

This is where the time goes, and it is a task these tools are unusually good at: with one rule that decides whether it works.

The rule: never let it retype your data

Asking a model to output your cleaned spreadsheet means every value is regenerated, and regenerated values can change. A 0 becomes O. A long number loses a digit. Three rows quietly vanish.

Instead, ask for the transformation, and apply it yourself.

In a spreadsheet:

Give me a LibreOffice/Excel formula for column F that converts the dates in column B, which are a mix of DD/MM/YYYY, D-M-YY and “14 March 2026”, into YYYY-MM-DD. Explain what each part does. Do not give me the converted values.

In code:

Write Python using pandas that reads sales.csv, standardises the date column, strips currency symbols and spaces from amount, and writes clean.csv. Print the row count before and after, and print any row where the date could not be parsed.

The second is worth the small effort of learning, because it is repeatable and auditable. Python and pandas are free; so is LibreOffice if you have no Excel licence.

Diagnose before you clean

Send the header row and fifteen real rows first:

Here are the header and 15 rows. List every data-quality problem you can see: inconsistent formats, ambiguous columns, likely duplicates, values that do not match their column’s apparent type. Do not fix anything yet.

You get a list that is usually longer than the one you had, and it costs nothing. The ambiguities it surfaces (“the status column contains both statuses and dates”) are the things that would have broken the cleaning halfway through.

The dangerous ones

Dates. 03/04/2026 is 3 April or 4 March depending on where the file came from. If both readings are plausible, no tool can resolve it and neither can you without asking the source. Say which convention applies; do not let it guess.

Deduplication. “Are these the same customer?” is a judgement, not a lookup. Have candidates proposed and review them:

List likely duplicate pairs with the reason, ranked by confidence. Do not merge anything.

Missing versus zero. An empty cell and a 0 mean different things, and a cleaning step that fills blanks with zero silently changes your totals.

Row counts. Check before and after, every time. This one check catches most silent data loss.

Analysis, once it is clean

Ask for the method, not the number: the arithmetic lesson applies directly. A formula or a script can be rerun next month and inspected by a colleague. A number pasted from a chat is a fossil.

For anything you will repeat, prefer a pivot table or a script over a one-off answer. You are not saving time on this month’s report; you are saving it on the next twelve.

What not to paste

Customer names, phone numbers, account numbers, national identity numbers, medical details, salaries. Most cleaning tasks do not need the real values: a column of dates in mixed formats is a formatting problem whether the names beside it are real or CUSTOMER_001.

Redact first, or work on a copy with the identifying columns removed. Where the data cannot leave your machine at all, ask for the script rather than sending the data: the script is generic, and it runs locally on the real file.

The habit that makes this compound

Keep the scripts and formulas you end up with, in the same file as your prompts, with one line saying what they were for.

Data cleaning is the most repetitive work most people do, and it is repetitive in a specific way: the same six problems, on different files, forever. Mixed date formats. Currency symbols in a numeric column. Trailing spaces. Two spellings of one supplier. A total row inside the data.

Six saved transformations cover most of it, and after a few months you stop asking for them at all: you paste the formula you already have. That is the point at which this stops being a chat habit and becomes a small toolkit you own.