The spreadsheet skill nobody puts on a syllabus

Where the numbers actually are

Every company has a data strategy document and a set of spreadsheets. The decisions are made from the spreadsheets.

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 FINAL_v7_updated_JK.xlsx.

This is not a stepping stone to be endured. It is where you meet the business.

What is actually worth knowing

Six things, and they take a weekend:

1. Pivot tables. The fastest way to answer “by category, by month” for someone standing at your desk.
2. XLOOKUP, or INDEX/MATCH. Joining two sheets. VLOOKUP still appears everywhere and breaks whenever a column is inserted, which is why the others exist.
3. Text to columns, and trim. Because half of real spreadsheet data is one column that should be three, with trailing spaces.
4. 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.
5. Data validation. So the person filling it in cannot type “N/a”, “NA”, “n/a” and “-“ into the same column.
6. Reading someone else’s spreadsheet. Which is a skill, and mostly consists of finding the hard-coded numbers hidden inside formulas.

The failure that has a famous example

Spreadsheets convert things that look like dates, silently, on paste.

Human 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.

The 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.

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 dtype in pandas. It is the same bug wearing a different coat.

Spotting a spreadsheet you cannot trust

• Numbers typed inside formulas: =B4*1.18: nobody knows what 1.18 is or when it changes.
• Merged cells, which break every attempt to read the file programmatically.
• Dates stored as text, which sort as 01/05, 01/12, 02/03 and look fine until you sum by month.
• Several sheets that should be one table, one per month, with slightly different column names.
• No row that reconciles against a source system.

When you find these, you have also found your first useful contribution.

The mistake juniors make with spreadsheets

You see the mess, build a proper pipeline and a dashboard, and present it. The team keeps using the spreadsheet.

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.

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.

The free path

Job 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.

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.

The interview question this prepares you for

“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.