Excel can do over 500 functions. Most offices only need twelve of them — and it’s exactly these twelve that trip up a surprising number of reports. This article names them, explains what they’re for, and points out the typical mistakes.
1. SUMIFS Instead of SUMIF
Everyone knows SUMIF. The catch: it only checks one condition. As soon as you need “revenue from customer X in Q2”, it no longer helps. SUMIFS (with the S at the end) takes any number of criteria pairs. Get into the habit of using SUMIFS from the start — it also works with just a single condition. The same applies to COUNTIFS and AVERAGEIFS.
2. XLOOKUP — and Why VLOOKUP Belongs in Retirement
VLOOKUP has three design flaws: it only looks to the right, the column number breaks when a column is inserted, and without the fourth parameter FALSE it silently returns wrong results.
XLOOKUP fixes all three. It searches in both directions, works with ranges instead of numbers, defaults to an exact match, and has a built-in parameter for “not found”. If you have a current version of Excel, there’s no longer any reason to use VLOOKUP. Only in files shared with older versions does INDEX combined with MATCH remain the safe choice.
3. IFERROR — Used Sparingly
IFERROR keeps spreadsheets tidy by replacing #N/A and #DIV/0! with a display of your choice. That’s exactly where the danger lies: it also hides errors you should be seeing. Only use it once you’ve understood why an error occurs — not just to make it go away.
4. TEXTJOIN and CONCAT
For assembling addresses, identifiers or salutations. TEXTJOIN takes a delimiter and can skip empty cells on request — sparing you the typical chain of ampersands with double spaces and trailing commas.
5. LEFT, RIGHT, MID and LEN
The tools for breaking text apart. A classic case: splitting “8306 Wangen-Brüttisellen” into postcode and town. In newer versions, TEXTBEFORE and TEXTAFTER take over much of this work for you. For one-off tasks, Flash Fill (Ctrl+E) is also often faster than any formula.
6. IF — But Please, Not Nested Seven Levels Deep
Nested IF formulas are the most common reason nobody wants to touch an inherited file. From three levels on, switch to IFS or SWITCH. From five levels on, the logic belongs in a small lookup table that you query with XLOOKUP. That’s readable, maintainable and easy to check.
7. UNIQUE, FILTER and SORT
The dynamic array functions are the biggest relief of recent years and still surprisingly little known. UNIQUE pulls a list without duplicates, FILTER returns all rows matching a condition, SORT sorts the result — all without helper columns, without copying, and updated automatically. Master these three and you won’t need a PivotTable for many reports.
8. The PivotTable
Not a function, but the most important tool of all. Three rules that make the difference:
- The source data must be clean: one header row, no merged cells, no blank rows, one row per record.
- Format the source as a table (Ctrl+T). Then the pivot range grows with it automatically.
- Slicers turn a PivotTable into a reporting tool that colleagues can operate too.
9. Conditional Formatting with a Formula
Most people know the standard rules. The real value lies in the “Use a formula to determine which cells to format” option. It lets you highlight entire rows based on a value in a single column — for example, every row whose due date lies in the past. What matters here is deliberate use of absolute and relative references.
10. Data Validation
The cheapest error prevention Excel offers. Dropdown lists instead of free text entry stop “Zürich”, “zürich” and “ZH” from ending up in the same column. It pays off at the latest the first time you analyse the data.
11. Date Functions: TODAY, DATEDIF, NETWORKDAYS
For deadlines, age, notice periods and project planning. NETWORKDAYS.INTL excludes weekends and a custom list of holidays — useful, because Swiss public holidays vary by canton and Excel doesn’t know them.
12. ROUND — and the Difference from Display Formatting
The most expensive beginner’s mistake in financial spreadsheets: setting cell formatting to two decimal places and assuming that rounds the number. Excel keeps calculating with the full value. This produces small rounding discrepancies in totals that nobody can explain. If you want to round properly, ROUND belongs in the formula.
What You Can Skip
Two common habits cost more than they’re worth:
Merged cells. They look tidy, then break sorting, filtering, PivotTables and most formulas. Use “Center Across Selection” instead.
Numbers stored as text. Imported data often arrives as text, recognisable by the default left alignment and the small green triangle. SUM silently ignores such values. Checking for it takes ten seconds; finding the mistake afterwards takes an hour.
And What About Copilot?
Copilot in Excel creates formulas, builds charts and PivotTables, and answers questions about your data. But it doesn’t replace understanding — it speeds it up. If you don’t know what a PivotTable does, you also can’t judge whether the result is correct. And Copilot has firm prerequisites: the file must be in .xlsx format, calculation options must be set to Automatic, and if your organisation enforces check-out in SharePoint, it won’t work at all.
So the twelve functions above remain relevant — you might just end up typing them faster in future.
Where to Learn This
In our Excel courses, you work on your own questions instead of artificial sample files. Basics, advanced techniques, Pivot and Power Query — as classroom training in Wangen-Brüttisellen or as online live training from anywhere in Switzerland.
