No matter how many planning and BI tools a company has: in the end almost every analysis ends up in Excel. Whoever masters the right functions is faster, makes fewer mistakes and builds files colleagues can understand. This selection focuses on what really counts in everyday controlling – grouped by task. Function names are those of the English Excel version.
Note: functions such as XLOOKUP, FILTER, UNIQUE, SORT, LET and TEXTBEFORE are only available in Microsoft 365 and newer Excel versions.
Looking up and linking
1. XLOOKUP
Searches for a value in one column and returns the matching value from another – left or right, with a built-in message if nothing is found. Doesn't break when someone inserts columns.
=XLOOKUP(A2,F2:F4,G2:G4,"not found")| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Cost centre | Name | No | Name | |||
| 2 | 4100 | Sales | 4100 | Sales | |||
| 3 | 4200 | Marketing | 4200 | Marketing | |||
| 4 | 9999 | not found | 4300 | Admin |
2. VLOOKUP
The classic you'll find in almost every old file. It only searches to the right and counts columns by number – insert a column and the result is wrong. Use XLOOKUP for new files, but you need to be able to read it.
3. INDEX and 4. MATCH / XMATCH
The combination for two-dimensional lookups: row and column at once. In older Excel versions the best alternative to XLOOKUP.
Summing, counting, analysing
5. SUMIFS
The workhorse of every analysis: total of all postings for an account, cost centre, month. With mixed references ($A5, B$4) one formula fills an entire P&L matrix.
=SUMIFS(Postings[Amount],Postings[Account],$A2,Postings[Month],B$1)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Account | Jan | Feb | Mar |
| 2 | Revenue | 412,500 | 398,200 | 455,900 |
| 3 | Materials | −181,300 | −176,400 | −199,800 |
| 4 | Personnel | −121,000 | −121,000 | −123,500 |
6. COUNTIFS
Counts rows that meet several conditions – such as open items over 30 days for key accounts.
7. AVERAGEIFS
Average with conditions, for example the average payment delay per customer – important for cash flow planning.
8. SUMPRODUCT
Multiplies columns row by row and sums the result. Ideal for weighted averages, such as the average contribution margin across a product range.
9. SUBTOTAL
Sums only the visible rows of a filtered list, so the total row automatically matches the filter.
10. AGGREGATE
Like SUBTOTAL, but can also ignore error values. Handy when a column contains the occasional #N/A.
Logic and error handling
11. IF
The basic function for decisions: if the variance is greater than 5%, then “check”, otherwise blank.
12. IFS
Several conditions without nested IF chains – for example a three-level traffic light.
13. AND / OR
Combine conditions inside IF: comment only if the variance is above 5% and above €10,000.
14. IFERROR
Catches errors like #DIV/0!. Only use it where an error is really expected – otherwise you hide real problems.
Dynamic lists (Microsoft 365)
15. FILTER
Returns all rows meeting a condition – and grows automatically when new data arrives.
16. UNIQUE
Creates a list without duplicates, such as all cost centres in the posting journal – perfect as row labels for analyses.
17. SORT
Sorts a list by formula, for example top customers by revenue. Combined with FILTER you get rankings that update themselves.
18. LET
Assigns names to intermediate results inside a formula. Long formulas become shorter, faster and understandable.
Dates and periods
19. DATE
Builds a date from year, month and day – essential when exports deliver year and month in separate columns.
20. EOMONTH
Returns the last day of a month, also offset. Ideal for month-end accruals and due dates.
21. EDATE
Moves a date by a number of months – for example the contract end after 36 months.
22. NETWORKDAYS
Counts working days between two dates, optionally excluding holidays. Helpful for closing schedules and staff capacity.
Text and data cleaning
23. TEXT
Formats numbers and dates as text, for example for report titles: “Revenue as of ” & TEXT(A1, "mmmm yyyy").
24. TEXTJOIN
Joins several values with a delimiter, e.g. all overdue customers into one cell.
25. TEXTBEFORE / TEXTAFTER
Splits text at a delimiter – such as “4100 – Sales” into number and name.
26. TRIM
Removes surplus spaces from exports. Many “not found” errors in XLOOKUP have exactly this cause.
27. ROUND
Rounds to a fixed number of digits. Important so that totals in a report add up – cell formatting only rounds the display.
Financial maths
28. NPV
Calculates the present value of future payments. Caution: NPV already discounts the first value, so add the initial investment outside the function. For irregular dates use XNPV.
29. IRR
Returns the internal rate of return of a cash flow series – the return at which NPV is zero. For irregular dates: XIRR.
30. PMT
Calculates the constant payment of a loan. Together with FV (future value) and RATE almost all loan and savings questions can be solved.
Tools that aren't functions
Four Excel tools save more time than any formula. Each has its own page with the most important features:
- Pivot tables: analysis with a few clicks. Always format the source data as a table (Ctrl+T) so it grows automatically. The 30 most important pivot features →
- Power Query (Data → Get Data): imports, cleans and joins exports. The steps are saved and repeated next month with one click – the biggest time-saver of all. The 30 most important Power Query features →
- Goal Seek and data tables (Data → What-If Analysis): “Which price gives a result of zero?” and sensitivities at the push of a button. All ten what-if tools → See also: scenario analysis.
- Conditional formatting: traffic lights with a system – use sparingly, or everything lights up. The 30 most important rules →
Good habits
- Inputs, calculations and outputs on separate sheets.
- Mark input cells in a consistent colour.
- No hard-coded numbers in formulas – assumptions belong in their own cells.
- A check sheet that shows whether all totals add up.
Conclusion
With these 30 functions you cover most of the controlling work in Excel. The biggest leap usually comes from XLOOKUP, SUMIFS and Power Query – that's where the first hour of learning pays off most.
- Excel functions by category – Microsoft's official overview with an explanation of every function
- Power Query for Excel help – getting started, tutorials and how-tos from Microsoft
- Power Query documentation – detailed documentation on Microsoft Learn
- Power Query M formula language – for those who want to go deeper
Frequently asked questions
Which Excel function is most important for controllers?
In daily work usually SUMIFS and XLOOKUP. The biggest time saving, however, comes from Power Query, which strictly speaking is a tool rather than a function.
Is XLOOKUP available in every Excel version?
No. XLOOKUP, FILTER, UNIQUE, SORT and LET are available in Microsoft 365 and newer versions. In older versions INDEX/MATCH remains the best alternative.
Why does NPV give a different value than my calculator?
NPV already discounts the first value in the series. The investment at time zero therefore has to be added outside the function.
What is the difference between SUBTOTAL and SUM?
SUBTOTAL only takes visible rows of a filtered list into account, SUM always all rows.