Skip to content
Go back

Excel for Work: 10 Functions That Save You Hours

Updated:
Illustration of a spreadsheet with formulas and a chart

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:

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

FunctionWhat it actually does for you
XLOOKUPReplaces VLOOKUP: exact by default, robust against inserted columns
IFERRORTurns #N/A into an explicit message. Use with care: it also hides real errors
SUMIFSSums by multiple criteria. Removes the hand-filtered helper column
COUNTIFSThe fastest sanity check there is: count the records you expect
FILTERReturns a dynamic range: no copy-paste, no misalignment
UNIQUEDistinct values, self-updating. The basis for every dropdown list
XMATCHFinds an item’s position without hardcoding column numbers
TEXTFormats dates and numbers inside strings without damaging the underlying data
TRIMThe invisible trailing space is the number one cause of “it finds nothing”
LETNames 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

  1. 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.
  2. No numbers written inside formulas. =B2*1.22 is an error waiting to happen. The rate goes in a cell, with a label.
  3. Use tables (Ctrl+T). Ranges extend themselves when new rows arrive, eliminating an entire family of errors.
  4. 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.
  5. 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.

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

Founder of UpSkillLab, with a Master's in Digital Marketing from the University of Florence. Every article starts from public sources cited in the text and is drafted with the help of AI tools, under my review and editorial responsibility: no purchased content, nothing published without human checking. Read the editorial method or report an error.


Share this post:

See all articles in Skills & Competencies

Previous Post
Job Interview Questions: How to Answer Them (With Examples)
Next Post
Time Management: 6 Techniques That Actually Work