Excel Functions Cheat Sheet 2026: The 30 You Actually Use

An Excel functions cheat sheet only works if you group functions by the job they do. Six groups cover most office work: sums and counts, logic, lookups, text, dates and the newer dynamic array functions.

Excel ships with hundreds of functions, and I would bet the average analyst touches about thirty in a normal quarter. This guide lists those thirty, shows the syntax that trips people up and explains the error codes you will see on the way. Version notes assume Microsoft 365 or Excel 2021 and later on a US-English install, as of 2026.

What does a useful Excel functions cheat sheet actually contain?

It contains less than you think. The goal is a map, not a dictionary. If you can name the group a function belongs to, finding the right one takes seconds.

Group	Functions	What they do	Example
Sum and count	SUM, AVERAGE, MAX, MIN, COUNT	Basic totals over a range	=SUM(B2:B100)
Conditional totals	SUMIFS, COUNTIFS, AVERAGEIFS	Add, count or average only rows that match	=SUMIFS(C:C,A:A,“West”,B:B,”>=1/1/2026”)
Logic	IF, IFS, AND, OR, IFERROR	Branching and error handling	=IF(D2>=100,“Hit”,“Miss”)
Lookup	XLOOKUP, VLOOKUP, INDEX, MATCH	Pull a value from another table	=XLOOKUP(A2,F:F,G:G,“Not found”)
Text	LEFT, MID, TRIM, TEXTJOIN, TEXT	Slice, clean, join and format text	=TEXTJOIN(”, “,TRUE,A2:A6)
Dates	TODAY, EOMONTH, DATEDIF, NETWORKDAYS	Month ends, durations, business days	=EOMONTH(A2,0)
Dynamic arrays	FILTER, SORT, UNIQUE	One formula that returns many rows	=FILTER(A2:D100,C2:C100>=100)

A habit worth building: reach for the plural versions first. SUMIFS and COUNTIFS accept one condition or five with the same syntax, while the older SUMIF puts its sum range last and SUMIFS puts it first. Mixing up that order is one of the most common formula mistakes I see in shared workbooks.

Which lookup function should I use, XLOOKUP or VLOOKUP?

If your whole team is on a current version of Microsoft 365, use XLOOKUP. If anyone opens the file in Excel 2019 or earlier, use INDEX with MATCH, which works everywhere.

The XLOOKUP syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Feature	VLOOKUP	XLOOKUP
Lookup column position	Must be the leftmost column	Any column
Return column	A column index number	A range you point at
Default match	Approximate if the last argument is omitted	Exact
When nothing is found	#N/A	Custom text you choose
Insert a column in the table	Index number can silently shift	Ranges adjust
Availability	Every version	Excel 2021, 2024, Microsoft 365

For sharing with clients or a finance team that still runs old installs, I default to INDEX and MATCH. It is a little uglier to read, but it has no version risk and handles lookups to the left of the key column.

The VLOOKUP mistake that produces no error

Here is the failure case that costs real money. Someone builds a price lookup from a vendor code and leaves off the fourth argument:

=VLOOKUP(A2,Prices!A:C,3)

Excel returns a number, so nobody questions it. But with the argument missing, VLOOKUP assumes the price table is sorted and does an approximate match. On an unsorted table it returns a neighboring row’s price. The invoice total is wrong, and you find out at reconciliation weeks later.

The fix costs one word:

=VLOOKUP(A2,Prices!A:C,3,FALSE)

Errors that announce themselves are easy. Wrong answers that look fine are what hurt, so check the last argument every time you write a lookup.

Is there a subtract function in Excel?

No, and plenty of people lose ten minutes scrolling the function list to find out. Subtraction is handled by the minus operator rather than a named function.

What you want	How to write it	Example
Subtract two cells	Minus operator	=A2-B2
Subtract several values from one	Subtract a SUM	=A2-SUM(B2:B6)
Multiply	Asterisk or PRODUCT	=A2*B2
Divide	Slash	=A2/B2
Days between two dates	Minus, then format as Number	=C2-B2

Date subtraction has one quirk. Subtract two dates and Excel sometimes formats the answer as a date, which shows something like 1/20/1900. Change the cell format to General or Number and you get the day count. For months or years between dates, DATEDIF is the tool, although it will not appear in the function wizard.

What do Excel’s error codes mean, and where do I start?

Read the code before you touch the formula. Each one points toward a short list of causes.

Error	Meaning	Check first
#NAME?	Unrecognized function or name	Spelling, version support, missing quotes around text
#N/A	Lookup value not found	Trailing spaces, numbers stored as text, spelling
#VALUE!	Wrong type of argument	Text in a number cell, ranges of different sizes
#REF!	Referenced cell was removed	Deleted rows, columns or renamed sheets
#DIV/0!	Division by zero	An empty or zero denominator
#SPILL!	Result has no room to expand	Cells below or beside the formula contain data

Most #N/A errors in the wild are invisible spaces. Wrap the lookup value in TRIM, or clean the source column. For numbers that look like numbers but are text, click the small green triangle in the corner and convert them.

If the cause is still unclear, use Formulas, then Evaluate Formula, and step through the calculation. You will see exactly where the value goes wrong.

Resist wrapping everything in IFERROR early. Putting =IFERROR(formula,0) around a broken lookup turns an obvious problem into a plausible zero that flows into your report. Fix the cause first, then use IFERROR only where “not found” is a legitimate result.

Why does my formula break when I copy it from a website?

Three culprits come up repeatedly, and the first one is regional.

Separators. In a US install, arguments are separated by commas. Many European and some Latin American settings use semicolons, because the comma is their decimal mark. A formula copied from a site written for those settings will not parse in US Excel until you swap the separators.

Curly quotes. Straight double quotes are required around text in formulas. If you copy through a word processor, quotes can turn into curly ones, and Excel rejects the formula.

Translated names. Excel in other languages localizes function names, so a formula posted by someone using a Spanish or German install may contain SUMAR.SI.CONJUNTO instead of SUMIFS. Your English install will return #NAME?. The function is the same; only the label differs.

When a pasted formula refuses to work, check those three in that order before assuming the logic is wrong.

Which old functions should I stop using?

Microsoft keeps a Compatibility category for functions that newer ones replaced. They still calculate, so there is no emergency, but new workbooks should use the successors.

Older function	Use instead	Note
CONCATENATE	CONCAT or TEXTJOIN	TEXTJOIN adds a delimiter and can skip blanks
RANK	RANK.EQ	Same tie behavior
STDEV	STDEV.S	Sample standard deviation
MODE	MODE.SNGL	Most frequent value
VLOOKUP	XLOOKUP	Not in the Compatibility list, but the direction of travel

If you inherit a template full of legacy names, leave it alone unless you are already editing that section. Swapping every function for style reasons creates risk with no payoff.

Which combinations save the most time?

Individual functions matter less than the pairings. Here are three I build most often.

1. Monthly totals by rep: =SUMIFS(Sales,Rep,A2,Month,B1) replaces a pivot table for simple summaries.
2. A deduplicated, sorted list: =SORT(UNIQUE(A2:A500)) entered once spills the whole list and updates as data changes. It needs Microsoft 365 or Excel 2021 and later.
3. Rows that meet two conditions: =FILTER(A2:E500,(C2:C500="West")*(E2:E500>=100000),"No match"). Multiply conditions for AND, add them for OR.

For deadline work, =EOMONTH(A2,0) returns the last day of a month, and NETWORKDAYS counts working days between two dates. Pass a range of holidays as the third argument and it skips them too.

Spreadsheet habits carry over to personal money. If you are building a household sheet, the category logic in our budget app review maps nicely onto SUMIFS columns. Furnishing a small place is a classic list-and-total job, and the one-room interior budget guide gives you line items to start from. Tracking a habit like daily intake works with a simple COUNTIFS, and the water intake guide suggests the targets to log. Weekly grocery planning is another natural fit: easy cooking recipes for living alone lists meals you can cost out line by line. And if you want a reading tracker, speed reading techniques offers pace numbers to chart.

Which version of Excel am I actually running?

Half of the “this formula does not work” questions are really version questions. Open File, then Account, and read the product line. Microsoft 365 receives new functions on a rolling basis, Excel 2021 and Excel 2024 are one-time purchases that include XLOOKUP and the dynamic array functions, and Excel 2019 and earlier do not.

Excel for the web supports most of the same functions, but a few features lag behind the desktop app, so a workbook that behaves on your laptop can look different in a browser tab. If a colleague reports a #NAME? error on a formula that works for you, ask which version they have before you rewrite anything. In many US offices a mix of installs is normal, which is exactly why the INDEX and MATCH fallback earns its place.

A quick test: type =XLOOKUP( in a blank cell. If Excel pops up the argument hint, you have it. If it does not, you are on an older build and should plan around that limit.

How do I turn a cheat sheet into a skill I keep?

A saved PDF you never open is the usual outcome. A routine that works better:

1. Pick two groups from the first table that match your current job.
2. Copy a real work file and write the formulas in it. Practice data does not stick.
3. When a formula fails, read the error code and use the error table above to narrow the cause.
4. Add one new function per week. Trying to memorize thirty in a weekend rarely survives Monday.

Think of the cheat sheet as a map. You still have to walk the route in your own files before it feels familiar.

Frequently asked questions

The FAQ below covers the questions that come up most: which cheat sheet to trust, XLOOKUP versus VLOOKUP, the missing subtract function, version limits for dynamic arrays and the semicolon problem.

The short version

Excel’s catalog is huge, but the working set is small: conditional totals, IF logic, one reliable lookup, a few text and date tools, and the dynamic array trio. Remember that subtraction is an operator, that approximate-match VLOOKUP fails silently, and that an error code tells you where to look. Pick your lookup based on who opens the file, not on what looks newest.

This article is general information, not professional advice. Function availability and menu names depend on your Excel version and update channel; confirm details in Microsoft’s official documentation.