Skip to content
Controllingblog

Home❧Software & Data❧Excel for controllers: the 30 most important functions

Excel for controllers: the 30 most important functions

Excel is and remains the Swiss army knife of controlling. These 30 functions cover almost everything that comes up in daily work – sorted by task, with examples.

Software & Data16 min readUpdated 14 Nov 2026
Illustration: laptop with dashboard, database and cloud +12% 4.2 M 98%

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, CostCentres[No], CostCentres[Name], "not found")
fx=XLOOKUP(A2,F2:F4,G2:G4,"not found")
ABCDEFG
1Cost centreNameNoName
24100Sales4100Sales
34200Marketing4200Marketing
49999not found4300Admin
What it looks like in the sheet: the formula in B2 fetches the name from the master data table on the right.

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.

=VLOOKUP(A2, MasterData!$A:$D, 3, FALSE)

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.

=INDEX(PL, MATCH(Account, AccountColumn, 0), MATCH(Month, HeaderRow, 0))

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], $A5, Postings[Month], B$4)
fx=SUMIFS(Postings[Amount],Postings[Account],$A2,Postings[Month],B$1)
ABCD
1AccountJanFebMar
2Revenue412,500398,200455,900
3Materials−181,300−176,400−199,800
4Personnel−121,000−121,000−123,500
One formula in B2, copied right and down, fills the whole table from the posting journal.

6. COUNTIFS

Counts rows that meet several conditions – such as open items over 30 days for key accounts.

=COUNTIFS(OI[Days], ">30", OI[Class], "A")

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.

=SUMPRODUCT(Quantity, CM_per_unit) / SUM(Quantity)

9. SUBTOTAL

Sums only the visible rows of a filtered list, so the total row automatically matches the filter.

=SUBTOTAL(109, C2:C500)

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.

=IF(ABS(D2/C2-1)>5%, "check", "")

12. IFS

Several conditions without nested IF chains – for example a three-level traffic light.

=IFS(D2>=0, "green", D2>=-5%, "amber", TRUE, "red")

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.

=IFERROR(D2/C2, 0)

Dynamic lists (Microsoft 365)

15. FILTER

Returns all rows meeting a condition – and grows automatically when new data arrives.

=FILTER(OI_List, OI_List[Days overdue]>30, "none")

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.

=LET(revenue, B5, vc, C5, cm, revenue-vc, IF(revenue=0, 0, cm/revenue))

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.

=EOMONTH(A2, 0)

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.

=NPV(7%, C2:C7) + B2

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.

=PMT(4%/12, 25*12, -200000) → 1,055.67
Try it outThe same calculations as NPV, IRR and PMT are available interactively in the investment calculator and the loan calculator – handy for cross-checking your Excel formulas.

Tools that aren't functions

Four Excel tools save more time than any formula. Each has its own page with the most important features:

Practical tipCreate a folder for your month-end close where you save the exports from your accounting system. A Power Query on that folder automatically loads all files – including next month's.

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.

Further links

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.