Introduction
Excel has more than 500 built-in functions. Nobody needs all of them. But there is a core set — functions that appear in professional spreadsheets across finance, marketing, HR, operations, and data analysis — that will repay the time spent learning them many times over. Master these 15, and you will handle the vast majority of professional data challenges without reaching for Google.
This guide assumes you have access to Excel 2021 or Excel 2024. Some of the most powerful functions on this list — XLOOKUP, FILTER, GROUPBY — are not available in Excel 2019 or earlier. If you are still on an older version, consider Office 2024 Professional Plus to unlock the full set.
1. XLOOKUP
XLOOKUP replaces VLOOKUP, HLOOKUP, and INDEX/MATCH for most use cases. It searches a range or array for a match and returns the corresponding value from another range. Unlike VLOOKUP, it can search in any direction, handles errors cleanly, and does not require the lookup column to be the leftmost column.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example: =XLOOKUP(A2, Products[SKU], Products[Price], "Not found") — searches the SKU column for the value in A2 and returns the corresponding price, showing "Not found" if the SKU does not exist.
Why it matters: The if_not_found parameter alone eliminates the need for wrapping every lookup in IFERROR. The ability to search from last to first is invaluable when looking up the most recent entry in a log.
2. FILTER
FILTER returns a filtered subset of a range based on criteria you define, spilling the results automatically into adjacent cells. Before dynamic arrays, achieving the same result required complex array formulas or VBA.
Syntax: =FILTER(array, include, [if_empty])
Example: =FILTER(A2:C100, B2:B100="London", "No results") — returns all rows where column B equals "London".
Combining conditions: Use multiplication for AND logic: =FILTER(A2:C100, (B2:B100="London")*(C2:C100>10000)) returns rows where the city is London AND the value exceeds 10,000.
3. UNIQUE
UNIQUE extracts a list of distinct values from a range, eliminating duplicates. Combined with SORT or FILTER, it becomes a powerful data cleaning and analysis tool.
Syntax: =UNIQUE(array, [by_col], [exactly_once])
Example: =UNIQUE(B2:B100) — returns a deduplicated list of all values in B2:B100.
Pro tip: Use =SORT(UNIQUE(B2:B100)) to get an alphabetically sorted unique list in one formula — the kind of thing that used to require a PivotTable or manual effort.
4. GROUPBY
Available in Excel 2024, GROUPBY aggregates data by one or more row fields — effectively a formula-based PivotTable. It is faster to set up than a PivotTable for common aggregations and updates automatically when source data changes.
Syntax: =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])
Example: =GROUPBY(A2:A100, C2:C100, SUM) — sums column C grouped by the categories in column A.
This function alone justifies upgrading to Office 2024 for anyone who regularly builds summary reports.
5. LET
LET assigns names to calculation results within a formula. This makes complex formulas far more readable, and more importantly, eliminates repeated calculations that would otherwise slow down large worksheets.
Syntax: =LET(name1, value1, [name2, value2...], calculation)
Example:
=LET(
sales, B2:B100,
target, 50000,
above_target, FILTER(sales, sales>target),
SUM(above_target)
)Without LET, FILTER(sales, sales>target) would be recalculated every time it appeared in the formula. With LET, it is calculated once and referenced by name.
6. SUMIFS / COUNTIFS / AVERAGEIFS
These conditional aggregation functions are workhorses in professional spreadsheets. They allow calculations based on multiple criteria across different ranges.
SUMIFS syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2...])
Example: =SUMIFS(D2:D100, B2:B100, "London", C2:C100, "Q1") — sums column D where column B is "London" AND column C is "Q1".
The mistake beginners make is using SUMIF (singular) when they have multiple criteria. Always use SUMIFS — it handles single criteria just as well as multiple, with no downside.
7. INDEX / MATCH
In Excel 2021 and 2024, XLOOKUP handles most scenarios where INDEX/MATCH was previously required. But INDEX/MATCH remains essential for complex two-dimensional lookups and backward compatibility with workbooks shared with Excel 2019 users.
Example (two-dimensional lookup):
=INDEX(B2:F10, MATCH(H1, A2:A10, 0), MATCH(H2, B1:F1, 0))
This looks up a value at the intersection of a row (found by the first MATCH) and a column (found by the second MATCH) — something XLOOKUP cannot do in a single formula.
8. IFERROR / IFNA
Error handling is a sign of professional-grade spreadsheet work. IFERROR returns a specified value if a formula produces an error, and IFNA targets only #N/A errors (which are typically lookup failures).
IFNA example: =IFNA(XLOOKUP(A2, D2:D100, E2:E100), "Unknown")
Use IFNA rather than IFERROR for lookups — IFERROR will also suppress genuine errors in your data (like #DIV/0! caused by a zero in a denominator), which you may actually want to see.
9. TEXT
TEXT converts a number or date into a formatted text string. Essential for building dynamic labels, concatenating dates with text, and producing human-readable outputs from raw data.
Syntax: =TEXT(value, format_text)
Examples:
=TEXT(TODAY(), "DD MMMM YYYY")→ "21 March 2026"=TEXT(1234567.89, "£#,##0.00")→ "£1,234,567.89"="Report for "&TEXT(A1,"MMMM YYYY")→ "Report for March 2026"
10. SORT and SORTBY
SORT returns a sorted version of a range, spilling the results. SORTBY allows you to sort one array by the values in a completely separate array — particularly useful when you want to sort a table by a calculated column that you do not want displayed.
SORTBY example: =SORTBY(A2:C50, C2:C50, -1) — returns the full table sorted by column C in descending order, without modifying the source data.
11. SEQUENCE
SEQUENCE generates an array of sequential numbers with configurable rows, columns, start value, and step. Useful for creating numbered lists, date series, and testing formulas with sample data.
Example: =SEQUENCE(12, 1, DATE(2026,1,1), 28) — generates twelve dates starting 1 January 2026, stepping forward by 28 days.
12. NETWORKDAYS
NETWORKDAYS calculates the number of working days between two dates, excluding weekends. NETWORKDAYS.INTL extends this to custom weekend definitions (useful if your working week is not Monday–Friday).
Example: =NETWORKDAYS(A2, B2, E2:E20) — calculates working days between the dates in A2 and B2, excluding the bank holidays listed in E2:E20.
This is indispensable for project planning, SLA tracking, and payroll calculations.
13. INDIRECT
INDIRECT returns a reference constructed from a text string. It is powerful but should be used sparingly — it forces recalculation every time anything changes in the workbook. Its best use case is building dynamic references where the sheet name or range address is determined by other cells.
Example: =SUM(INDIRECT(A1&"!B2:B100")) — where A1 contains a sheet name, this sums B2:B100 on that sheet. Useful for workbooks that consolidate data from sheets named after months, regions, or departments.
14. ARRAY with CTRL+SHIFT+ENTER (Legacy) vs. Dynamic Arrays
In Excel 2019 and earlier, many powerful operations required legacy array formulas entered with CTRL+SHIFT+ENTER. In Excel 2021 and 2024, dynamic arrays have replaced this approach for most purposes. Understanding the difference matters when inheriting old workbooks:
- Legacy array formulas are wrapped in curly braces:
{=SUM(IF(A2:A100="London",B2:B100))} - Modern equivalent:
=SUM(FILTER(B2:B100,A2:A100="London"))— entered normally, no special key combination
15. VLOOKUP (Know Why You Should Probably Stop Using It)
VLOOKUP is included because you will encounter it constantly in inherited spreadsheets. Knowing how it works — and knowing its limitations — is essential for maintaining those workbooks. The key limitations that XLOOKUP resolves:
- VLOOKUP can only look to the right of the lookup column
- VLOOKUP breaks when columns are inserted into the lookup range (it uses a column index number, not a reference)
- VLOOKUP defaults to approximate match, which returns wrong results if the lookup column is not sorted — a trap for beginners
For new work, always use XLOOKUP. For existing workbooks with VLOOKUP, understand what it is doing before replacing it.
Putting It All Together
The most effective spreadsheet professionals do not use every function in isolation — they combine them. A common pattern in financial reporting is =GROUPBY() for the summary, =FILTER() for drilling into specific rows, and =LET() to make the formulas maintainable. Practice building each function individually, then experiment with nesting them.
Access to GROUPBY, PIVOTBY, and the full dynamic array suite requires Excel 2021 or 2024. If you are working from an older version, Office 2024 Professional Plus gives you the complete toolkit at a one-time cost of £29.99.
