Excel Formulas Cheat Sheet: The Only List You Actually Need

This cheat sheet collects the Excel formulas that working professionals reach for every day, with correct syntax, a small worked dataset, and the result each example actually produces. Bookmark it, or keep it open in a second tab while you build. Everything here also works in Google Sheets unless noted otherwise.

Unless a row says otherwise, the examples below assume a tiny sales table: A2:A6 holds the regions North, South, North, East, South and B2:B6 holds the amounts 100, 250, 175, 90, 310.

The master Excel formulas cheat sheet

The fastest way to use this basic Excel formulas list: find the task in the middle column, copy the example, and swap in your own ranges. Every formula below is tested syntax, and every result is what Excel really returns against the sample data described above, not a rounded approximation.

Math and counting basics: SUM, AVERAGE, and the COUNT family

Use SUM to total a range, AVERAGE for the mean, COUNT for numeric cells, and COUNTA for any non-empty cell. Add a condition with SUMIF, COUNTIF, or AVERAGEIF, and use the plural SUMIFS when you need two or more conditions at once.

The one thing to burn into memory is the argument order difference:

• =SUMIF(criteria_range, criteria, sum_range) — the sum range comes last. =SUMIF(A2:A6,"North",B2:B6) returns 275.
• =SUMIFS(sum_range, criteria_range1, criteria1, ...) — the sum range comes first. =SUMIFS(B2:B6,A2:A6,"South",B2:B6,">300") returns 310.

Conditions with comparison operators go in quotes: ">100", "<=90", "<>North" (not equal). To compare against a cell, concatenate: =COUNTIF(B2:B6,">"&D1).

For rounding, =ROUND(3.14159,2) gives 3.14. ROUNDUP and ROUNDDOWN force the direction, which matters for prices and quantities you can't split.

Logical functions: IF, nested IF, IFS, and IFERROR

IF tests a condition and returns one of two values: =IF(B2>=150,"High","Low"). For more than two outcomes, nest IFs or use IFS, which reads far more cleanly. IFERROR catches any error a formula throws and substitutes your own fallback value instead of showing #N/A or #DIV/0!.

A grading example with a score of 84 in B2:

• Nested IF: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) → B
• IFS (Excel 2019+): =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") → B

Note the trailing TRUE,"F" pair in IFS. It acts as the catch-all; without it, an unmatched score returns #N/A.

IFERROR is the polish layer. =IFERROR(VLOOKUP("West",A2:B6,2,FALSE),"Not found") returns Not found because West isn't in the sample data. Use it on anything that feeds a report someone else will read. One caution: it hides every error, including real bugs, so add it after the formula works, not before.

Lookup formulas: VLOOKUP, XLOOKUP, and INDEX + MATCH

Lookups pull a related value from another table, like fetching a price from a product list. VLOOKUP is the classic, XLOOKUP is its modern replacement in Excel 365 and 2021, and INDEX + MATCH is the version-proof combination that works everywhere, including old workbooks and Google Sheets.

Here's the anatomy of the one everybody learns first:

The same lookup three ways, finding the amount for East in our sample table:

• =VLOOKUP("East",A2:B6,2,FALSE) → 90
• =XLOOKUP("East",A2:A6,B2:B6,"Not found") → 90
• =INDEX(B2:B6,MATCH("East",A2:A6,0)) → 90

If you're deciding which to standardize on, we walk through the tradeoffs with more examples in our XLOOKUP vs VLOOKUP comparison.

Stuck writing a lookup right now? Describe your problem in plain English, like "get the price for the product code in A2 from the Products sheet", and XLsheetAI writes the formula for you, with an explanation of every argument.

Text functions for cleaning and combining data

Text functions slice, measure, clean, and join strings. LEFT, RIGHT, and MID extract characters by position, LEN counts them, TRIM strips stray spaces, and CONCAT or TEXTJOIN glue values back together. Most messy-import problems get solved with some combination of these six.

A pattern worth memorizing: extract everything before a delimiter with =LEFT(A2,FIND("-",A2)-1). For "XL-2043" that returns "XL". And if your data starts life as a screenshot or a printed table rather than text at all, you can convert an image to an Excel sheet first and then clean it with these functions.

Date formulas: TODAY, DATEDIF, and EOMONTH

Excel stores dates as serial numbers, which is why date math works with plain arithmetic. TODAY() returns the current date and recalculates on open. DATEDIF measures the gap between two dates in years, months, or days. EOMONTH jumps to the last day of any month, which makes month-end reporting formulas trivial.

• =TODAY()+30 → the date 30 days from now.
• =DATEDIF(A2,TODAY(),"Y") → complete years between the date in A2 and today. Use "M" for months or "D" for days. DATEDIF doesn't appear in Excel's function autocomplete, but it works; it's a legacy function kept for compatibility.
• =EOMONTH(TODAY(),0) → the last day of the current month. =EOMONTH(TODAY(),-1)+1 → the first day of the current month, a handy trick for "month to date" ranges.

If a date formula returns a number like 46251 instead of a date, the formula is fine. Just change the cell's number format to a date format.

Modern dynamic array functions: FILTER, SORT, UNIQUE, and XMATCH

Dynamic array functions return multiple results that "spill" into neighboring cells from a single formula. FILTER extracts matching rows, SORT orders them, UNIQUE deduplicates, and XMATCH finds positions. They need Excel 365 or Excel 2021 and newer; in Excel 2016 or 2019 they simply don't exist and will show #NAME?.

• =FILTER(A2:C100,B2:B100="West","No matches") → every row where column B says West, or the text "No matches" if none do.
• =SORT(A2:C100,3,-1) → the whole range sorted by its third column, descending.
• =UNIQUE(A2:A100) → each distinct value once. Against our sample region column it spills North, South, East.
• =XMATCH("East",A2:A6) → 4, the position of East in the range. Unlike classic MATCH, exact match is the default.

These combine beautifully: =SORT(UNIQUE(A2:A100)) gives you an alphabetized list of distinct values in one cell. If you see a #SPILL! error, something is sitting in the cells the result needs; clear that range and the formula recovers on its own.

Excel error codes and how to fix them

Every Excel error code is a specific diagnosis, not a generic failure. #N/A means a lookup found nothing, #VALUE! means a wrong data type, #REF! means a deleted reference, #DIV/0! is division by zero or a blank, and #NAME? means Excel doesn't recognize something you typed.

Common mistakes that break formulas

Most broken spreadsheets trace back to a handful of repeat offenders: wrong argument order, missing exact-match flags, unlocked references before a fill-down, and invisible text problems in the data itself. Check this list before rewriting a formula from scratch, because the formula is usually closer to correct than the data is.

• Swapping SUMIF and SUMIFS argument order. SUMIF ends with the sum range; SUMIFS starts with it. Both will often return a plausible-looking wrong number rather than an error.
• Omitting FALSE in VLOOKUP. Without it you get approximate matching, which on unsorted data returns quietly wrong results.
• Forgetting $ before copying down. =VLOOKUP(A2,$D$2:$E$50,2,FALSE) keeps the table fixed while the lookup value walks down the column.
• Numbers stored as text. They look identical but won't sum and won't match in lookups. The little green corner triangle is the tell.
• Trailing spaces from exports. "North " never equals "North". Run TRIM on imported columns first.
• Using 365-only functions in files shared with Excel 2016/2019 users. Their copy shows #NAME?. Keep an INDEX + MATCH fallback for shared workbooks.

Want these to stick? The fastest way to learn formulas is to use them on your own data. XLsheetAI turns a plain-English description into a working formula, explains broken ones, and fixes errors like #N/A and #REF! so you learn by doing instead of memorizing.