Most Excel users know SUM, AVERAGE, and IF. These get you through the basics. But the formulas below are what separate casual users from professionals who save hours every week. Each one solves a real, common business problem.
1. XLOOKUP
XLOOKUP replaces VLOOKUP with a more flexible syntax. =XLOOKUP(A1, Products[ID], Products[Name], "Not found") looks up a value and returns a result from any column, in any direction. It defaults to exact match and handles errors with its fourth argument. If you are still using VLOOKUP, make the switch today.
2. INDEX-MATCH
For environments where XLOOKUP is not available, INDEX-MATCH is the professional alternative. =INDEX(B:B, MATCH(A1, C:C, 0)) finds A1 in column C and returns the corresponding value from column B. It works in any direction and is faster than VLOOKUP on large datasets.
3. SUMIFS
SUMIFS adds values based on multiple criteria. =SUMIFS(Sales[Amount], Sales[Region], "Ontario", Sales[Year], 2026) sums sales for Ontario in 2026. Unlike SUMIF (single condition), SUMIFS handles as many criteria as you need. COUNTIFS and AVERAGEIFS work the same way.
4. FILTER (dynamic array)
=FILTER(A1:D100, B1:B100="Active") returns only the rows where column B equals Active. This is a dynamic array formula: the result spills into multiple cells automatically. It replaces complex Advanced Filter setups with a single, readable formula.
5. SORT and SORTBY
=SORT(A1:D100, 3, -1) sorts the range by the third column in descending order. SORTBY lets you sort by a column that is not in the output. These work with FILTER for powerful dynamic tables: =SORT(FILTER(data, criteria), column, order).
6. UNIQUE
=UNIQUE(A1:A100) returns a deduplicated list. Combine with SORT for an alphabetical unique list: =SORT(UNIQUE(A1:A100)). This replaces the old Remove Duplicates workflow that modified your source data.
7. LET
LET defines named variables inside a formula. =LET(revenue, SUM(B:B), costs, SUM(C:C), profit, revenue - costs, IF(profit > 0, "Profitable", "Loss")) is readable and avoids recalculating the same expression multiple times. Use LET whenever your formula references the same calculation more than once.
8. LAMBDA
LAMBDA lets you create custom reusable functions. Define a LAMBDA in the Name Manager, then use it like any built-in function. =MyTax(1000) could calculate tax based on your custom formula. This is Excel's most powerful feature for people who build templates used by others.
9. TEXTJOIN
=TEXTJOIN(", ", TRUE, A1:A10) joins cell values with a separator, ignoring blanks. Useful for creating comma-separated lists from columns: product names, email addresses, or tags.
10. IFS
IFS handles multiple conditions without nesting. =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F") is cleaner than nested IF statements. Use TRUE as the last condition for a default value.
11. SEQUENCE
=SEQUENCE(12, 1, 1, 1) generates numbers 1 through 12. Combine with DATE for a sequence of months: =DATE(2026, SEQUENCE(12), 1). Useful for generating calendars, invoice numbers, or row indices dynamically.
12. STOCKHISTORY and GEOGRAPHY data types
Excel can pull live stock prices and geographic data. =STOCKHISTORY("MSFT", "2026-01-01", "2026-04-01") gives you a historical price table. Geography data types let you pull population, area, and GDP for any country or city.
13. BYROW and BYCOL
These LAMBDA helper functions apply a formula to each row or column of an array. =BYROW(A1:C10, LAMBDA(row, SUM(row))) sums each row in a range without needing a helper column.
14. XMATCH
XMATCH is to MATCH what XLOOKUP is to VLOOKUP: a modern replacement with exact match by default, support for search modes (first, last, binary), and wildcard matching.
15. PIVOTBY
New in recent Excel versions, PIVOTBY creates a pivot table directly from a formula. =PIVOTBY(Sales[Region], Sales[Category], Sales[Amount], SUM) builds a cross-tab without using the PivotTable wizard. The result is a dynamic array that updates automatically.
Learning these formulas
The best way to learn advanced formulas is by solving real problems. Take a task you do manually, then figure out which formula automates it. Start with XLOOKUP and SUMIFS since they appear in almost every business workbook. Our [Excel training](/courses/microsoft-excel/) covers all 15 of these formulas with hands-on practice.