In 2013 three researchers at the University of Massachusetts tried to replicate one of the most cited economics papers of the decade, Reinhart and Rogoff’s Growth in a Time of Debt, and found, among other things, that a spreadsheet range left five countries out of the average. The reconstruction is published in the Cambridge Journal of Economics (Herndon, Ash and Pollin, 2014). Not a conceptual error: a range dragged badly.
Three years later, an analysis in Genome Biology (Ziemann, Eren and El-Osta, 2016) examined thousands of scientific papers with spreadsheet supplements and
found that around a fifth contained gene names corrupted by automatic conversion
into dates: SEPT2 became 2-Sep. The problem was so systematic that in 2020
the body responsible for gene naming renamed the affected symbols. They changed
biology because it was easier than changing Excel.
Those two cases are why this isn’t the usual list of “advanced functions”. The functions that matter aren’t the ones that do the most: they’re the ones that make it harder to be wrong.
How common spreadsheet errors actually are
The reference literature is the body of work collected by Raymond Panko (University of Hawaii) and by the European Spreadsheet Risks Interest Group, which since the 1990s has catalogued field audits and documented incidents. The honest summary:
- audits of operational spreadsheets find errors in the large majority of files examined, when the audit is done cell by cell;
- the per-cell error rate is low, a few percentage points, but real spreadsheets have thousands of cells, and errors compound;
- the person who built the file is, on average, very confident it’s correct.
Be careful how this gets cited: percentages vary widely across studies, samples are small and non-random, and “error” is defined differently. The robust conclusion isn’t a number, it’s a direction: the probability that a complex spreadsheet built without checks contains at least one error is high, and our confidence in it is not a reliable indicator.
The most dangerous function is the one everyone uses
VLOOKUP deserves its own paragraph, because it has caused more silent errors
than any other function. Two technical reasons, both verifiable in Microsoft’s
own documentation.
1. The last argument, if omitted, defaults to TRUE. That means approximate match. If you forget the trailing zero, the formula doesn’t throw an error: it returns the wrong value, silently. It’s the worst possible way to fail.
2. The column index is a fixed number. VLOOKUP(A2,Table,4,0) stops being
correct the moment someone inserts a column inside the range. The formula keeps
working, just on a different column.
The replacement, where available, is XLOOKUP: it refers to the return column
explicitly, defaults to exact match, and has a native argument for the
“not found” case.
The ten functions, ordered by risk avoided
| Function | What it actually does for you |
|---|---|
| XLOOKUP | Replaces VLOOKUP: exact by default, robust against inserted columns |
| IFERROR | Turns #N/A into an explicit message. Use with care: it also hides real errors |
| SUMIFS | Sums by multiple criteria. Removes the hand-filtered helper column |
| COUNTIFS | The fastest sanity check there is: count the records you expect |
| FILTER | Returns a dynamic range: no copy-paste, no misalignment |
| UNIQUE | Distinct values, self-updating. The basis for every dropdown list |
| XMATCH | Finds an item’s position without hardcoding column numbers |
| TEXT | Formats dates and numbers inside strings without damaging the underlying data |
| TRIM | The invisible trailing space is the number one cause of “it finds nothing” |
| LET | Names intermediate results inside a formula, making it readable and checkable |
Two practical notes worth more than one extra function.
The argument separator changes with the locale. Comma in most English configurations, semicolon in many European ones. It’s why formulas copied from forums “don’t work”.
Dynamic array functions (FILTER, UNIQUE, SORT) aren’t available in
older versions. Before building a shared process on them, check what your
colleagues actually have installed.
And one thing that isn’t a function but is worth more than all ten: Power Query (Data → Get Data). It imports, cleans and merges tables while recording the steps. The difference is that the cleaning becomes repeatable: next month you refresh instead of redoing. If you spend hours every month fixing the same export, that’s where to invest.
The five rules that cut errors more than any function
- Separate input, calculation and output. Three distinct areas, ideally three sheets. A reader should see in five seconds where to type and what not to touch.
- No numbers written inside formulas.
=B2*1.22is an error waiting to happen. The rate goes in a cell, with a label. - Use tables (Ctrl+T). Ranges extend themselves when new rows arrive, eliminating an entire family of errors.
- Add a check row. The total computed a second way, or a difference that must come to zero. It’s the single intervention with the best ratio of time spent to errors caught.
- Have someone else read the file. The audits cited say exactly this: the person who built it is the least suited to find its errors.
The formatting that silently ruins data
The gene-name lesson applies to anyone working with exports from other systems.
- Codes starting with zero get treated as numbers and lose the zero.
- Codes like
SEPT2,MAR1,1-5can turn into dates. - Long numbers (IBANs, barcodes, VAT numbers) get converted to scientific notation.
- Decimal separators differ between comma and point systems.
The defence isn’t remembering: it’s importing through Power Query and declaring the columns as text, rather than double-clicking the CSV. You make the choice once and it holds.
What about AI?
Assistants are useful for writing a complex formula and, above all, for explaining a formula you inherited, which is the most underrated use case. They aren’t reliable for verifying that the spreadsheet is correct: they don’t see the data, don’t know the process, and produce plausible answers even when wrong. What you can and can’t paste into a chatbot is covered in how to use ChatGPT at work.
My take: “knows Excel” on a CV says nothing
I think “knows Excel” has become a useless CV line precisely because it distinguishes nothing: everyone writes it. What distinguishes is the next sentence, something like “I replaced a four-hour monthly manual process with a three-minute refresh”: it’s verifiable, tellable at interview and attached to a number. Same logic as the rest of the CV: how to write a CV that holds up.
And the less diplomatic part: most of the hours lost in spreadsheets aren’t lost through ignorance of functions, but because the file is a mess and nobody dares rebuild it. Rebuilding the structure costs a day and repays it every month.
Frequently asked questions
Should I move to XLOOKUP if I still use VLOOKUP? On new files yes, where the version allows it. On existing files that work, don’t rewrite for its own sake: fix them when you’re in there anyway, and in the meantime check the last argument is zero.
Excel or Google Sheets? For daily work the practical difference is simultaneous collaboration versus power on large files and data transformation. The functions listed exist, under similar names, in both.
How long does it take to reach a useful level? A few weeks of targeted use, if you pick a concrete output to produce rather than following a course from the start. The method is in learning a skill in 30 days.
Do I need macros or VBA? Power Query and pivot tables first: they cover most cases without introducing code to maintain. Macros are for automating interface actions, not calculations.
How do I prove this skill to a recruiter? With the process you automated and the time you saved, not with a self-declared level. Same rule as for in-demand skills: what employers actually look for.
Sources
- T. Herndon, M. Ash and R. Pollin, Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff, Cambridge Journal of Economics, 2014. The replication documenting, among other problems, a spreadsheet range that excluded five countries from the average.
- M. Ziemann, Y. Eren and A. El-Osta, Gene name errors are widespread in the scientific literature, Genome Biology, 2016. Around a fifth of papers with spreadsheet supplements contained gene names auto-converted into dates.
- HGNC. HUGO Gene Nomenclature Committee. Revision of gene symbols subject to automatic spreadsheet conversion.
- R. R. Panko, What We Know About Spreadsheet Errors, Journal of End User Computing. Review of operational spreadsheet audits and per-cell error rates.
- EuSpRIG. European Spreadsheet Risks Interest Group. Archive of documented incidents and research on spreadsheet risk.
- Microsoft Support. Official documentation for XLOOKUP, VLOOKUP (match argument behaviour), FILTER, UNIQUE, LET and Power Query.
- Cedefop. European analyses of demand for basic and intermediate digital skills.



