{"course":{"slug":"ai-careers","title":"Working in AI","url":"https://addaly.com/learn/ai-careers","level":"beginner","track":"work"},"lesson":{"slug":"the-spreadsheet-nobody-lists","title":"The spreadsheet skill nobody puts on a syllabus","url":"https://addaly.com/learn/ai-careers/the-spreadsheet-nobody-lists","position":27,"of":85,"minutes":7,"answer":"The organisation's real numbers live in spreadsheets, and being able to meet people there is what gets you access to the questions worth answering.","keyIdea":"The organisation's real numbers live in spreadsheets, and being able to meet people there is what gets you access to the questions worth answering."},"body":{"format":"markdown","text":"## Where the numbers actually are\n\nEvery company has a data strategy document and a set of spreadsheets. The decisions are made from the spreadsheets.\n\nFinance closes the month in one. Operations plans the rota in one. HR tracks headcount in one. The sales forecast that the board sees was assembled by hand on a Thursday. If you cannot work in that environment, you are cut off from the questions that matter, and you will spend your first year building dashboards nobody opens while the real work happens in a file called `FINAL_v7_updated_JK.xlsx`.\n\nThis is not a stepping stone to be endured. It is where you meet the business.\n\n## What is actually worth knowing\n\nSix things, and they take a weekend:\n\n1. **Pivot tables.** The fastest way to answer \"by category, by month\" for someone standing at your desk.\n2. **`XLOOKUP`, or `INDEX`/`MATCH`.** Joining two sheets. `VLOOKUP` still appears everywhere and breaks whenever a column is inserted, which is why the others exist.\n3. **Text to columns, and trim.** Because half of real spreadsheet data is one column that should be three, with trailing spaces.\n4. **Conditional formatting to find anomalies.** Highlight the top and bottom one per cent and look at them. This finds more errors per minute than anything else in this lesson.\n5. **Data validation.** So the person filling it in cannot type \"N/a\", \"NA\", \"n/a\" and \"-\" into the same column.\n6. **Reading someone else's spreadsheet.** Which is a skill, and mostly consists of finding the hard-coded numbers hidden inside formulas.\n\n## The failure that has a famous example\n\nSpreadsheets convert things that look like dates, silently, on paste.\n\nHuman gene symbols like `SEPT1` and `MARCH1` were being converted to dates by default settings, corrupting supplementary data in a substantial share of published genomics papers. The eventual fix was not to the software: in 2020 the naming committee renamed the genes. That is how hard this class of bug is to stamp out: an entire scientific field changed its vocabulary to work around a default.\n\nThe same mechanism eats your data every week in smaller ways. `1-10` becomes 1 October. A product code `5E3` becomes 5000, because it looks like scientific notation. A leading zero disappears from a phone number.\n\nThe defence: import as text, not paste. In Sheets and Excel, use the import dialog and set the column type explicitly, exactly as you set `dtype` in pandas. It is the same bug wearing a different coat.\n\n## Spotting a spreadsheet you cannot trust\n\n- Numbers typed inside formulas: `=B4*1.18`: nobody knows what 1.18 is or when it changes.\n- Merged cells, which break every attempt to read the file programmatically.\n- Dates stored as text, which sort as `01/05`, `01/12`, `02/03` and look fine until you sum by month.\n- Several sheets that should be one table, one per month, with slightly different column names.\n- No row that reconciles against a source system.\n\nWhen you find these, you have also found your first useful contribution.\n\n## The mistake juniors make with spreadsheets\n\nYou see the mess, build a proper pipeline and a dashboard, and present it. The team keeps using the spreadsheet.\n\nThe reason is not stubbornness. The spreadsheet lets them change an assumption and see the effect in two seconds, and your dashboard does not. Whatever replaces it must preserve the ability to ask a new question without asking you.\n\nThe move that works: leave the spreadsheet as the interface, and replace what feeds it. Automate the extraction and the cleaning, deliver a clean tab, and let them keep their formulas on top. Trust first, migration later.\n\n## The free path\n\nJob adverts name Excel because organisations pay for it. Everything in this lesson works in **Google Sheets** or **LibreOffice Calc**, both free, and pivot tables, lookups and validation exist in all three with different names. If you are learning at home, learn in Sheets and translate; the concepts carry.\n\nKnow the ceilings, because knowing when to leave a spreadsheet is part of the skill. Google Sheets caps at ten million cells and becomes unpleasant well before that. Excel stops at 1,048,576 rows, which is exactly why a truncated export at that number should make you suspicious rather than satisfied. When a dataset approaches either limit, the answer is a database, and DuckDB will read the same file without complaint.\n\n## The interview question this prepares you for\n\n\"Here is a spreadsheet. How would you check whether it is right?\" The strong answer is a routine, not an opinion: reconcile the total against the source system, check the row count, sort by each key column and look at the top and bottom five rows, count blanks per column, and confirm the dates are dates."},"check":{"question":"You replace the operations team's weekly planning spreadsheet with a clean dashboard that shows the same figures more accurately. Two months later the team is still using the spreadsheet. What is the most likely reason?","options":[{"text":"They do not trust an automated system and need training on the new tool.","correct":false,"tempting":"Assumes the gap is confidence or skill, when the team may understand the dashboard perfectly and still find it unusable for their task."},{"text":"The spreadsheet lets them change an assumption and see the result immediately, and the dashboard only shows them numbers, so it answers the question they had last month but not the ones they have on Thursday.","correct":true},{"text":"Old habits take longer than two months to change, and the migration simply needs more time.","correct":false,"tempting":"Treats a design problem as an adoption timeline, which means waiting instead of fixing the thing that is missing."},{"text":"The dashboard is missing figures they rely on, so an audit of the fields is needed.","correct":false,"tempting":"A reasonable diagnostic, but it assumes the deficit is in the data shown rather than in what can be done with it."}]},"prev":{"slug":"the-command-line-and-the-free-tier","title":"The terminal, the container, and a cloud bill of zero","url":"https://addaly.com/learn/ai-careers/the-command-line-and-the-free-tier"},"next":{"slug":"evaluation-the-scarce-skill","title":"Evaluation: deciding what \"correct\" means, in advance","url":"https://addaly.com/learn/ai-careers/evaluation-the-scarce-skill"},"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 skill nobody puts on a syllabus', in Working in AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/ai-careers/the-spreadsheet-nobody-lists (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 skill nobody puts on a syllabus","courseTitle":"Working in AI","module":{"index":3,"slug":"the-evidence","title":"The evidence: what gets tested, and how to prove you have it"},"answer":null,"keyIdea":"The organisation’s real numbers live in spreadsheets, and being able to meet people there is what gets you access to the questions worth answering.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"where-the-numbers-actually-are","runs":[{"text":"Where the numbers actually are"}]},{"t":"p","runs":[{"text":"Every company has a data strategy document and a set of spreadsheets. The decisions are made from the spreadsheets."}]},{"t":"p","runs":[{"text":"Finance closes the month in one. Operations plans the rota in one. HR tracks headcount in one. The sales forecast that the board sees was assembled by hand on a Thursday. If you cannot work in that environment, you are cut off from the questions that matter, and you will spend your first year building dashboards nobody opens while the real work happens in a file called "},{"text":"FINAL_v7_updated_JK.xlsx","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"This is not a stepping stone to be endured. It is where you meet the business."}]},{"t":"h2","id":"what-is-actually-worth-knowing","runs":[{"text":"What is actually worth knowing"}]},{"t":"p","runs":[{"text":"Six things, and they take a weekend:"}]},{"t":"ol","start":1,"items":[[{"text":"Pivot tables.","b":true},{"text":" The fastest way to answer “by category, by month” for someone standing at your desk."}],[{"text":"XLOOKUP","b":true,"code":true},{"text":", or ","b":true},{"text":"INDEX","b":true,"code":true},{"text":"/","b":true},{"text":"MATCH","b":true,"code":true},{"text":".","b":true},{"text":" Joining two sheets. "},{"text":"VLOOKUP","code":true},{"text":" still appears everywhere and breaks whenever a column is inserted, which is why the others exist."}],[{"text":"Text to columns, and trim.","b":true},{"text":" Because half of real spreadsheet data is one column that should be three, with trailing spaces."}],[{"text":"Conditional formatting to find anomalies.","b":true},{"text":" Highlight the top and bottom one per cent and look at them. This finds more errors per minute than anything else in this lesson."}],[{"text":"Data validation.","b":true},{"text":" So the person filling it in cannot type “N/a”, “NA”, “n/a” and “-“ into the same column."}],[{"text":"Reading someone else’s spreadsheet.","b":true},{"text":" Which is a skill, and mostly consists of finding the hard-coded numbers hidden inside formulas."}]]},{"t":"h2","id":"the-failure-that-has-a-famous-example","runs":[{"text":"The failure that has a famous example"}]},{"t":"p","runs":[{"text":"Spreadsheets convert things that look like dates, silently, on paste."}]},{"t":"p","runs":[{"text":"Human gene symbols like "},{"text":"SEPT1","code":true},{"text":" and "},{"text":"MARCH1","code":true},{"text":" were being converted to dates by default settings, corrupting supplementary data in a substantial share of published genomics papers. The eventual fix was not to the software: in 2020 the naming committee renamed the genes. That is how hard this class of bug is to stamp out: an entire scientific field changed its vocabulary to work around a default."}]},{"t":"p","runs":[{"text":"The same mechanism eats your data every week in smaller ways. "},{"text":"1-10","code":true},{"text":" becomes 1 October. A product code "},{"text":"5E3","code":true},{"text":" becomes 5000, because it looks like scientific notation. A leading zero disappears from a phone number."}]},{"t":"p","runs":[{"text":"The defence: import as text, not paste. In Sheets and Excel, use the import dialog and set the column type explicitly, exactly as you set "},{"text":"dtype","code":true},{"text":" in pandas. It is the same bug wearing a different coat."}]},{"t":"h2","id":"spotting-a-spreadsheet-you-cannot-trust","runs":[{"text":"Spotting a spreadsheet you cannot trust"}]},{"t":"ul","items":[[{"text":"Numbers typed inside formulas: "},{"text":"=B4*1.18","code":true},{"text":": nobody knows what 1.18 is or when it changes."}],[{"text":"Merged cells, which break every attempt to read the file programmatically."}],[{"text":"Dates stored as text, which sort as "},{"text":"01/05","code":true},{"text":", "},{"text":"01/12","code":true},{"text":", "},{"text":"02/03","code":true},{"text":" and look fine until you sum by month."}],[{"text":"Several sheets that should be one table, one per month, with slightly different column names."}],[{"text":"No row that reconciles against a source system."}]]},{"t":"p","runs":[{"text":"When you find these, you have also found your first useful contribution."}]},{"t":"h2","id":"the-mistake-juniors-make-with-spreadsheets","runs":[{"text":"The mistake juniors make with spreadsheets"}]},{"t":"p","runs":[{"text":"You see the mess, build a proper pipeline and a dashboard, and present it. The team keeps using the spreadsheet."}]},{"t":"p","runs":[{"text":"The reason is not stubbornness. The spreadsheet lets them change an assumption and see the effect in two seconds, and your dashboard does not. Whatever replaces it must preserve the ability to ask a new question without asking you."}]},{"t":"p","runs":[{"text":"The move that works: leave the spreadsheet as the interface, and replace what feeds it. Automate the extraction and the cleaning, deliver a clean tab, and let them keep their formulas on top. Trust first, migration later."}]},{"t":"h2","id":"the-free-path","runs":[{"text":"The free path"}],"role":"free"},{"t":"p","runs":[{"text":"Job adverts name Excel because organisations pay for it. Everything in this lesson works in "},{"text":"Google Sheets","b":true},{"text":" or "},{"text":"LibreOffice Calc","b":true},{"text":", both free, and pivot tables, lookups and validation exist in all three with different names. If you are learning at home, learn in Sheets and translate; the concepts carry."}]},{"t":"p","runs":[{"text":"Know the ceilings, because knowing when to leave a spreadsheet is part of the skill. Google Sheets caps at ten million cells and becomes unpleasant well before that. Excel stops at 1,048,576 rows, which is exactly why a truncated export at that number should make you suspicious rather than satisfied. When a dataset approaches either limit, the answer is a database, and DuckDB will read the same file without complaint."}]},{"t":"h2","id":"the-interview-question-this-prepares-you-for","runs":[{"text":"The interview question this prepares you for"}]},{"t":"p","runs":[{"text":"“Here is a spreadsheet. How would you check whether it is right?” The strong answer is a routine, not an opinion: reconcile the total against the source system, check the row count, sort by each key column and look at the top and bottom five rows, count blanks per column, and confirm the dates are dates."}]}],"check":{"question":[{"text":"You replace the operations team’s weekly planning spreadsheet with a clean dashboard that shows the same figures more accurately. Two months later the team is still using the spreadsheet. What is the most likely reason?"}],"options":[{"text":[{"text":"They do not trust an automated system and need training on the new tool."}],"correct":false,"tempting":[{"text":"Assumes the gap is confidence or skill, when the team may understand the dashboard perfectly and still find it unusable for their task."}]},{"text":[{"text":"The spreadsheet lets them change an assumption and see the result immediately, and the dashboard only shows them numbers, so it answers the question they had last month but not the ones they have on Thursday."}],"correct":true,"tempting":null},{"text":[{"text":"Old habits take longer than two months to change, and the migration simply needs more time."}],"correct":false,"tempting":[{"text":"Treats a design problem as an adoption timeline, which means waiting instead of fixing the thing that is missing."}]},{"text":[{"text":"The dashboard is missing figures they rely on, so an audit of the fields is needed."}],"correct":false,"tempting":[{"text":"A reasonable diagnostic, but it assumes the deficit is in the data shown rather than in what can be done with it."}]}],"order":[3,0,2,1],"why":null},"prev":{"slug":"the-command-line-and-the-free-tier","title":"The terminal, the container, and a cloud bill of zero","minutes":10},"next":{"slug":"evaluation-the-scarce-skill","title":"Evaluation: deciding what “correct” means, in advance","minutes":10}}}