Excel Formulas in 2026 (The 30 You Actually Need)

The complete reference to Excel formulas that matter — from the five everyone uses daily to the modern dynamic-array functions that change how spreadsheets work.

Excel has roughly 500 functions. You'll use about 30. The list of formulas worth memorizing is shorter than people expect, and the gap between "I know basic Excel" and "I'm a power user" is mostly about knowing which 30 to focus on. This guide walks through the formulas that actually matter — organized by what kind of problem they solve, with real examples and the syntax patterns that trip up new users. Skip the formulas you already know; the value here is the modern (2021+) dynamic array functions that most older Excel guides don't cover.

Why Modern Excel Is Different

Excel 365 and 2021 introduced dynamic arrays — formulas that automatically spill into multiple cells. This was a fundamental change in how the application works. Before dynamic arrays, returning multiple values required Ctrl+Shift+Enter array formulas, which were brittle and confusing. Now you write a single formula, and it fills as many cells as the result needs. The new functions built around this — FILTER, SORT, UNIQUE, SEQUENCE — replace dozens of older multi-step techniques with single-formula solutions.

The practical impact is that pre-2021 Excel guides recommend formulas that are now considered legacy. If you're still using INDEX/MATCH for everything, or building helper columns to do what UNIQUE handles in one cell, you're working harder than you need to. The 30 formulas below are weighted toward modern Excel — the ones that exist in versions you're likely actually using.

The Five Formulas Everyone Should Know

If you're starting from scratch and want maximum impact for minimum effort, master these five before anything else. They cover the vast majority of real Excel work in business contexts.

Formula	What it does	Why it matters
XLOOKUP	Find a value, return another	Replaces VLOOKUP; the most-used function in modern Excel
SUMIFS	Sum based on multiple conditions	Reporting, dashboards, financial modeling
COUNTIFS	Count based on multiple conditions	Frequency analysis, conditional counts
IF / IFS	Conditional logic	Decision logic in every spreadsheet
INDEX/MATCH	Flexible lookup (legacy alternative to XLOOKUP)	Backward compatibility for older Excel versions

If you only learn five formulas in your career, learn these five. Every more advanced workflow builds on combinations of them.

Lookup Formulas

Looking up data in one table from another is the single most common Excel task. Three formulas handle it.

XLOOKUP — The Modern Default

XLOOKUP replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH usage in one cleaner function. Available in Excel 365 and 2021+. Default exact match (no more silent wrong results from forgetting FALSE), searches any direction, returns whole rows.

Example: =XLOOKUP("John", A:A, B:B, "Not found") finds John's salary or returns "Not found".

VLOOKUP — Legacy but Still Common

The 1985-era function that's still in millions of spreadsheets. Always pass FALSE as the fourth argument or you'll get silent wrong results. Only searches left-to-right.

INDEX/MATCH — Compatibility Workhorse

The classic combo that handles everything VLOOKUP can't. Works in all Excel versions. Two functions, but more flexible than VLOOKUP.

Use INDEX/MATCH when you need backward compatibility with older Excel; otherwise XLOOKUP is cleaner.

Math and Statistics

Excel's math functions are simple individually but powerful in combination. The "IFS" variants — SUMIFS, COUNTIFS, AVERAGEIFS — handle conditional logic without needing separate filtering steps.

Formula	Pattern	Use case
SUM	=SUM(range)	Total a column or range
SUMIF	=SUMIF(criteria_range, criteria, sum_range)	Sum based on one condition
SUMIFS	=SUMIFS(sum_range, criteria_range1, criteria1, ...)	Sum based on multiple conditions
AVERAGE / AVERAGEIFS	Same patterns as SUM	Mean values with optional conditions
COUNT / COUNTIFS	Same patterns	Count cells with optional conditions
MIN / MAX / MEDIAN	=MIN(range)	Find extremes or middle values
ROUND / ROUNDUP / ROUNDDOWN	=ROUND(value, decimals)	Control decimal places

The conditional variants — SUMIFS, COUNTIFS, AVERAGEIFS — are where most reporting work lives. Master the syntax pattern (the sum/count range first, then alternating criteria_range / criteria pairs) and you can build most dashboards from these alone.

Logical Formulas

Conditional logic powers everything from simple flags to complex nested decision trees. Four formulas handle 95% of cases.

Avoid nesting IFs more than three deep — past that point, IFS or a lookup table becomes more readable. The most common abuse is something like =IF(A1=1, "Apple", IF(A1=2, "Banana", IF(A1=3, "Cherry", IF(A1=4, "Date", ...)))). Replace with =XLOOKUP(A1, {1;2;3;4}, {"Apple";"Banana";"Cherry";"Date"}) or a real lookup table.

Text Formulas

Text manipulation is awkward in Excel but unavoidable. Five formulas cover most needs. TEXTJOIN replaced the older CONCATENATE in 2016 and is dramatically better — it handles arrays directly and lets you specify a separator.

Formula	Use case
TEXTJOIN(separator, ignore_empty, range)	Combine text values with a separator
LEFT / RIGHT / MID	Extract specific parts of a string
LEN(text)	Character count
TRIM(text)	Remove extra spaces — fixes most data import issues
UPPER / LOWER / PROPER	Change case
FIND / SEARCH / SUBSTITUTE	Find positions or replace text

TRIM deserves special mention: invisible trailing spaces are the #1 cause of "VLOOKUP is finding the wrong row" bugs. Wrap your lookup values in TRIM to eliminate the entire category.

Date Formulas

Excel's date handling is quirky but workable once you understand that dates are just numbers (days since 1900-01-01). Four formulas handle most date math.

DATEDIF is undocumented in modern Excel but still works — Microsoft kept it for backward compatibility. It's the cleanest way to calculate age in years from birthdate to today.

Modern Dynamic Array Formulas

The dynamic array functions are the biggest Excel improvement of the last decade. They return arrays that "spill" into multiple cells from a single formula. Six functions cover what you'll actually use.

FILTER

Returns rows from a range that meet a condition. Replaces complex multi-step filtering with one formula.

SORT and SORTBY

Sort a range or sort one range based on values from another.

UNIQUE

Returns the unique values from a range. Replaces removing duplicates as a formula instead of a manual operation.

SEQUENCE

Generates a sequence of numbers. Useful for test data, dynamic ranges, or building array logic.

LET

Define variables inside a formula for readability. Game-changer for complex formulas — assign intermediate values to names instead of repeating expressions.

LET makes complex formulas dramatically more readable. Once you start using it, you'll wonder how you wrote 200-character formulas without it.

Power User Tips

Three habits separate Excel users who write maintainable formulas from those who fight their own spreadsheets six months later.

These three habits compound. A spreadsheet built with Tables, named ranges, and proper absolute references is dramatically easier to maintain than the same data with raw cell references.

Common Mistakes

The mistakes that cause the most pain cluster around five patterns. Forgetting FALSE in VLOOKUP — silent wrong results, the most common cause of Excel bugs in production. Hardcoded values inside formulas — putting =A1*0.07 when 0.07 should be in a separate cell so the rate is editable. Not using IFERROR — sheets covered in #N/A errors look broken even when working correctly. Cell references not absolute when copying — works for first row, breaks for every subsequent row. Nesting IFs more than three deep — becomes unreadable; use IFS, lookup tables, or INDEX/MATCH instead.

Frequently Asked Questions

The Bottom Line

You don't need to memorize 500 Excel functions. The 30 covered here handle 95% of real business work, and the five top formulas handle 80% of that. Master XLOOKUP, SUMIFS, IF, COUNTIFS, and INDEX/MATCH first. Add the dynamic array functions (FILTER, SORT, UNIQUE, LET) once you're comfortable. Wrap risky formulas in IFERROR. Use Tables and named ranges for readability. The rest is just practice on real problems.