Most Excel formula guides are alphabetical lists of functions. That ordering is useless for the actual problem, which is never “what does CONCATENATE do” — it is “I have this, I need that, which of the five plausible functions is right”.
So this is organised by the job. Find the row that matches what you are trying to do, and the function to reach for is on the right of it.
| What you are trying to do | Reach for |
|---|---|
| Total or count a column | SUM, COUNT, COUNTA |
| Total only the rows that meet conditions | SUMIFS, COUNTIFS, AVERAGEIFS |
| Return a different result depending on a value | IF, then IFS when there are more than two outcomes |
| Fetch a matching value from another table | XLOOKUP, or INDEX with MATCH |
| Stop an error message appearing | IFERROR |
| Split or join text | LEFT, RIGHT, MID, TEXTSPLIT, TEXTJOIN |
| Clean imported data | TRIM, CLEAN, VALUE, PROPER |
| Work with dates | TODAY, EOMONTH, DATEDIF, NETWORKDAYS |
| Round for reporting | ROUND, ROUNDUP, MROUND |
Everything below expands on those groups, in the order it is worth learning them.
References come first, and nobody teaches them
The most common cause of a broken Excel model is not a misunderstood function. It is a reference that moved when it should not have.
Every reference in Excel is relative unless you tell it otherwise. Copy
=B2*C1 down a column and both parts shift. If C1 is a tax rate that should
apply to every row, that is wrong — and it is wrong quietly, producing numbers
that look plausible.
The dollar sign locks a reference:
$A$1— never moves.A$1— the column can move, the row cannot.$A1— the row can move, the column cannot.
Do not type the dollar signs. Put the cursor inside the reference and press
F4; it cycles through all four states. It is on
the shortcut sheet for a reason — it
is the single most-used keystroke in formula work.
The better answer, once a workbook matters, is to stop using cell addresses
altogether. Convert your data to a Table with Ctrl+T and references become
names: =[@Quantity]*[@Price] reads as English, survives inserted columns, and
extends automatically when rows are added.
Conditional logic: IF, and when to stop using it
IF takes a test, a result if true and a result if false:
=IF(B2>1000, "Review", "OK")That is fine for two outcomes. The trouble starts at three, because the
traditional answer is to nest one IF inside another:
=IF(B2>10000,"Tier 1",IF(B2>5000,"Tier 2",IF(B2>1000,"Tier 3","Standard")))This works and it is horrible to read, harder to change, and easy to get wrong
by putting the thresholds in the wrong order. IFS does the same thing flat:
=IFS(B2>10000,"Tier 1", B2>5000,"Tier 2", B2>1000,"Tier 3", TRUE,"Standard")Condition, result, condition, result, and TRUE as the catch-all at the end.
There is a fuller treatment in the IFS guide,
including the order-of-evaluation trap that catches people moving from nested
IF.
Conditional totals: SUMIFS and COUNTIFS
If you are filtering a list, reading the total off the status bar and typing it into another cell, this is the section that will save you the most time.
=SUMIFS(Sales[Amount], Sales[Region],"West", Sales[Month],"March")Sum this column, where that column is this and that other column is that. Add as many condition pairs as you need.
Two things worth knowing. First, use SUMIFS rather than SUMIF even for a
single condition — the argument order is different between them, and standardising
on one avoids an entire category of mistake. Second, the criteria can be cell
references rather than typed values, which is what turns a static formula into a
report that responds to a dropdown.
Lookups: fetch a value from somewhere else
This is the function family people most want and most often get wrong.
The modern answer is XLOOKUP:
=XLOOKUP(A2, Products[Code], Products[Price], "Not found")What to find, where to look for it, what to return, and what to say if it is not there. It searches in either direction, does not care where the columns sit relative to each other, and does not break when someone inserts a column.
INDEX and MATCH together do the same job and work in every version of Excel:
=INDEX(Products[Price], MATCH(A2, Products[Code], 0))The 0 in MATCH is not optional decoration — it is the difference between an
exact match and an approximate one. Leave it out and MATCH defaults to
approximate, which returns a wrong answer with no error at all on unsorted data.
That single argument is the most dangerous default in Excel, and it is covered in
detail in
XLOOKUP compared with INDEX MATCH.
VLOOKUP is the one everybody learned. It only searches rightward, it addresses
the return column by number so inserting a column silently breaks it, and it has
no not-found argument. Learn to read it; do not write new ones.
Handling errors deliberately
IFERROR wraps a formula and substitutes something when it fails:
=IFERROR(XLOOKUP(A2, Products[Code], Products[Price]), "Check code")Use it to catch errors you expect — a lookup that legitimately might not find a
match. Do not wrap everything in IFERROR by reflex. An error you did not expect
is Excel telling you something is wrong, and hiding it means the wrong number
propagates silently into a report instead of stopping there.
Where to go next
If this material is mostly familiar and it is the multi-condition work that is awkward, Excel Advanced Functions is the course that covers it properly — lookups, conditional aggregation, error handling and auditing, on your own files with an instructor watching.
If you have not yet met pivot tables, they solve a lot of what people try to do with formulas, and often better. That is Excel Intermediate 2 territory, and there are free guides in the Excel data and reporting cluster.
Not sure which of those applies to you? The five-minute skill assessment asks eight questions and recommends one.
Common questions
- How many Excel functions do I actually need to know?
- About fifteen covers the overwhelming majority of real work — SUM, AVERAGE, COUNT, IF, IFS, SUMIFS, COUNTIFS, XLOOKUP or INDEX MATCH, IFERROR, TEXT, LEFT/RIGHT/MID, TRIM, TODAY, and ROUND. Knowing those fifteen properly beats recognising a hundred.
- What does the dollar sign do in an Excel formula?
- It locks part of a reference so it does not move when the formula is copied. $A$1 never moves, A$1 keeps the row fixed, $A1 keeps the column fixed. Press F4 with the cursor inside a reference to cycle through the four combinations rather than typing them.
- Why does my formula show
- Because it is pointing at a cell that no longer exists — almost always because a row or column it referenced was deleted. The fix is to rewrite the reference. Using named ranges or table column names instead of cell addresses prevents it happening again.
- Should I still use VLOOKUP?
- Not in new work. XLOOKUP does the same job without breaking when a column is inserted, searches in both directions, and has a built-in not-found argument. VLOOKUP is worth being able to read, because you will inherit workbooks full of it, but there is no reason to write a new one.
- What is the difference between a formula and a function?
- A formula is anything you type after an equals sign, including plain arithmetic like =B2*1.05. A function is one of Excel's built-in named operations, like SUM or XLOOKUP. Most useful formulas contain one or more functions, which is why the two words get used interchangeably.