Most people use a small fraction of what Excel can do. That’s fine, but a handful of Excel functions can take hours of copying, pasting and checking off your week. Here are the five I find myself teaching most often, with examples you can adapt straight away.
A quick note: XLOOKUP, UNIQUE and FILTER need Microsoft 365 or Excel 2021 or later. If they don’t work for you, check which version you have under File, Account.
1. XLOOKUP: find anything, anywhere
XLOOKUP looks up a value in one column and returns the matching value from another. It replaces VLOOKUP and is easier to read, because you point at the exact columns you want rather than counting them.
=XLOOKUP(A2, Products[Code], Products[Price], "Not found")This finds the product code in A2, returns its price, and shows “Not found” instead of an error if the code doesn’t exist. Unlike VLOOKUP, it can look left as well as right, and it won’t break if someone inserts a column.
2. SUMIFS: totals that answer real questions
“What were sales in the North region in March?” is a SUMIFS question. It adds up values that meet one or more conditions.
=SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Month], "March")Put the region and month in their own cells and refer to those instead of typing them in, and you have a reusable summary table. COUNTIFS and AVERAGEIFS work the same way.
3. IFS: no more nested IF headaches
If you’ve ever written an IF inside an IF inside an IF, IFS is for you. It checks conditions in order and returns the result for the first one that’s true.
=IFS(B2>=90, "Excellent", B2>=70, "Good", TRUE, "Needs attention")The final TRUE acts as a catch-all for anything that didn’t meet the earlier conditions.
4. UNIQUE and FILTER: instant lists and extracts
UNIQUE gives you a list of distinct values, such as every customer who appears in your sales data, without removing duplicates by hand.
=UNIQUE(Sales[Customer])FILTER pulls out the rows that meet a condition, and updates automatically when the data changes.
=FILTER(Sales, Sales[Region]="North", "No results")Both “spill” their results into the cells below, so make sure there’s empty space for them.
5. TEXTJOIN: combine text without the fiddle
TEXTJOIN joins a range of text with a separator of your choice, and can skip blank cells.
=TEXTJOIN(", ", TRUE, A2:A10)It’s ideal for building email lists, combining address lines, or creating a single summary from several cells.
A tip that makes all of these better
Turn your data into an Excel Table first (click inside it and press Ctrl+T). Microsoft’s Excel help centre has a full reference for every function. Your formulas can then use readable names like Sales[Amount], and they expand automatically when you add new rows.
These functions, and plenty more, are covered hands-on in my Excel Intermediate course, run live online in small groups.