{"course":{"slug":"prompting","title":"Getting Real Work out of a Model","url":"https://addaly.com/learn/prompting","level":"beginner","track":"start"},"lesson":{"slug":"messy-spreadsheet-data","title":"The spreadsheet somebody else built","url":"https://addaly.com/learn/prompting/messy-spreadsheet-data","position":68,"of":72,"minutes":9,"answer":"Ask for the transformation (a formula or a script) rather than the cleaned data itself, diagnose from fifteen rows before cleaning, and check the row count before and after every step.","keyIdea":"Ask for the transformation (a formula or a script) rather than the cleaned data itself, diagnose from fifteen rows before cleaning, and check the row count before and after every step."},"body":{"format":"markdown","text":"## What actually goes wrong with data\n\nNot 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.\n\nThis is where the time goes, and it is a task these tools are unusually good at: with one rule that decides whether it works.\n\n## The rule: never let it retype your data\n\nAsking 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.\n\nInstead, ask for the **transformation**, and apply it yourself.\n\n> Figure: Two ways to ask for a clean spreadsheet. Ask for the cleaned data: Every value is regenerated; A 0 becomes an O; a long number loses a digit; Three rows quietly vanish; Cannot be rerun on next month's file; Real names have to leave your machine. Ask for the transformation: A formula or a short script; Runs on the real file, locally; Prints row counts before and after; Rerun in seconds next month; Works on CUSTOMER_001 as well as on names. The transformation runs on the real file, on your own machine, and can be rerun next month. Regenerated data changes in the retyping and cannot be audited afterwards.\n\n**In a spreadsheet:**\n\n> 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.\n\n**In code:**\n\n> 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.\n\nThe 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.\n\n## Diagnose before you clean\n\nSend the header row and fifteen real rows first:\n\n> 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.\n\nYou 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.\n\n## The dangerous ones\n\n**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.\n\n**Deduplication.** \"Are these the same customer?\" is a judgement, not a lookup. Have candidates proposed and review them:\n\n> List likely duplicate pairs with the reason, ranked by confidence. Do not merge anything.\n\n**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.\n\n**Row counts.** Check before and after, every time. This one check catches most silent data loss.\n\n## Analysis, once it is clean\n\nAsk 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.\n\nFor 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.\n\n## What not to paste\n\nCustomer 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`.\n\nRedact 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.\n\n## The habit that makes this compound\n\nKeep the scripts and formulas you end up with, in the same file as your prompts, with one line saying what they were for.\n\nData 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.\n\nSix 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."},"check":{"question":"Why is asking for a cleaning script riskier to skip than it appears, compared with pasting the sheet and asking for cleaned output?","options":[{"text":"Scripts run faster on large files than a chat interface can produce output.","correct":false,"tempting":"True and beside the point; speed is not the risk being avoided."},{"text":"Scripts are easier for colleagues to review than a table of values.","correct":false,"tempting":"A genuine benefit, and secondary to the integrity of the values themselves."},{"text":"Regenerated values can change silently (a lost digit or a dropped row) whereas a script transforms the original file and can print row counts.","correct":true},{"text":"Chat interfaces truncate long outputs, so some rows would be missing.","correct":false,"tempting":"Truncation is visible and recoverable; silent alteration of values is neither."}]},"prev":{"slug":"comparing-options","title":"Deciding between options","url":"https://addaly.com/learn/prompting/comparing-options"},"next":{"slug":"code-you-cannot-write","title":"Asking for code you could not have written","url":"https://addaly.com/learn/prompting/code-you-cannot-write"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'The spreadsheet somebody else built', in Getting Real Work out of a Model. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/prompting/messy-spreadsheet-data (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"The spreadsheet somebody else built","courseTitle":"Getting Real Work out of a Model","module":{"index":8,"slug":"the-work-you-actually-have","title":"The work you actually have"},"answer":null,"keyIdea":"Ask for the transformation (a formula or a script) rather than the cleaned data itself, diagnose from fifteen rows before cleaning, and check the row count before and after every step.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"left":{"title":"Ask for the cleaned data","items":["Every value is regenerated","A 0 becomes an O; a long number loses a digit","Three rows quietly vanish","Cannot be rerun on next month’s file","Real names have to leave your machine"]},"type":"compare","right":{"title":"Ask for the transformation","items":["A formula or a short script","Runs on the real file, locally","Prints row counts before and after","Rerun in seconds next month","Works on CUSTOMER_001 as well as on names"]},"title":"Two ways to ask for a clean spreadsheet","caption":"The transformation runs on the real file, on your own machine, and can be rerun next month. Regenerated data changes in the retyping and cannot be audited afterwards."},"alt":"Figure: Two ways to ask for a clean spreadsheet. Ask for the cleaned data: Every value is regenerated; A 0 becomes an O; a long number loses a digit; Three rows quietly vanish; Cannot be rerun on next month’s file; Real names have to leave your machine. Ask for the transformation: A formula or a short script; Runs on the real file, locally; Prints row counts before and after; Rerun in seconds next month; Works on CUSTOMER_001 as well as on names. The transformation runs on the real file, on your own machine, and can be rerun next month. Regenerated data changes in the retyping and cannot be audited afterwards."}},"blocks":[{"t":"h2","id":"what-actually-goes-wrong-with-data","runs":[{"text":"What actually goes wrong with data"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-rule-never-let-it-retype-your-data","runs":[{"text":"The rule: never let it retype your data"}]},{"t":"p","runs":[{"text":"Asking a model to output your cleaned spreadsheet means every value is regenerated, and regenerated values can change. A "},{"text":"0","code":true},{"text":" becomes "},{"text":"O","code":true},{"text":". A long number loses a digit. Three rows quietly vanish."}]},{"t":"p","runs":[{"text":"Instead, ask for the "},{"text":"transformation","b":true},{"text":", and apply it yourself."}]},{"t":"p","runs":[{"text":"In a spreadsheet:","b":true}]},{"t":"quote","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"In code:","b":true}]},{"t":"quote","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"diagnose-before-you-clean","runs":[{"text":"Diagnose before you clean"}]},{"t":"p","runs":[{"text":"Send the header row and fifteen real rows first:"}]},{"t":"quote","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"You get a list that is usually longer than the one you had, and it costs nothing. The ambiguities it surfaces (“the "},{"text":"status","code":true},{"text":" column contains both statuses and dates”) are the things that would have broken the cleaning halfway through."}]},{"t":"h2","id":"the-dangerous-ones","runs":[{"text":"The dangerous ones"}]},{"t":"p","runs":[{"text":"Dates.","b":true},{"text":" "},{"text":"03/04/2026","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"Deduplication.","b":true},{"text":" “Are these the same customer?” is a judgement, not a lookup. Have candidates proposed and review them:"}]},{"t":"quote","runs":[{"text":"List likely duplicate pairs with the reason, ranked by confidence. Do not merge anything."}]},{"t":"p","runs":[{"text":"Missing versus zero.","b":true},{"text":" An empty cell and a "},{"text":"0","code":true},{"text":" mean different things, and a cleaning step that fills blanks with zero silently changes your totals."}]},{"t":"p","runs":[{"text":"Row counts.","b":true},{"text":" Check before and after, every time. This one check catches most silent data loss."}]},{"t":"h2","id":"analysis-once-it-is-clean","runs":[{"text":"Analysis, once it is clean"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"what-not-to-paste","runs":[{"text":"What not to paste"}]},{"t":"p","runs":[{"text":"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 "},{"text":"CUSTOMER_001","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-habit-that-makes-this-compound","runs":[{"text":"The habit that makes this compound"}]},{"t":"p","runs":[{"text":"Keep the scripts and formulas you end up with, in the same file as your prompts, with one line saying what they were for."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"Why is asking for a cleaning script riskier to skip than it appears, compared with pasting the sheet and asking for cleaned output?"}],"options":[{"text":[{"text":"Scripts run faster on large files than a chat interface can produce output."}],"correct":false,"tempting":[{"text":"True and beside the point; speed is not the risk being avoided."}]},{"text":[{"text":"Scripts are easier for colleagues to review than a table of values."}],"correct":false,"tempting":[{"text":"A genuine benefit, and secondary to the integrity of the values themselves."}]},{"text":[{"text":"Regenerated values can change silently (a lost digit or a dropped row) whereas a script transforms the original file and can print row counts."}],"correct":true,"tempting":null},{"text":[{"text":"Chat interfaces truncate long outputs, so some rows would be missing."}],"correct":false,"tempting":[{"text":"Truncation is visible and recoverable; silent alteration of values is neither."}]}],"order":[1,3,0,2],"why":null},"prev":{"slug":"comparing-options","title":"Deciding between options","minutes":8},"next":{"slug":"code-you-cannot-write","title":"Asking for code you could not have written","minutes":9}}}