Excel Formulas Cheat Sheet
Excel formulas cheat sheet: the most useful math, logic, text, lookup, date, and conditional-count formulas, each with a tested example and result.
Excel formulas by task
Sample table used by every example. The symbol ␣ marks a space typed in the cell.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Item | Region | Qty | Price | Customer |
| 2 | 2026-01-15 | Pens | East | 10 | 2.5 | ␣␣ana␣␣lopez␣ |
| 3 | 2026-02-03 | Ink | West | 4 | 8 | bo chen |
| 4 | 2026-02-20 | Pens | West | 6 | 2.5 | cy diaz |
| 5 | 2026-03-09 | Tape | East | 12 | 1.25 | di wu |
| Function | What it does | Example | Result |
|---|---|---|---|
| SUM | Adds the numbers in a range. | =SUM(D2:D5) | 32 |
| AVERAGE | Returns the mean of a range. | =AVERAGE(D2:D5) | 8 |
| ROUND | Rounds a number to a set number of decimals. | =ROUND(AVERAGE(E2:E5),1) | 3.6 |
| MAX | Returns the largest value. | =MAX(D2:D5) | 12 |
| MIN | Returns the smallest value. | =MIN(E2:E5) | 1.25 |
| SUMPRODUCT | Multiplies matching cells and adds the products, such as quantity times price. | =SUMPRODUCT(D2:D5,E2:E5) | 87 |
| Function | What it does | Example | Result |
|---|---|---|---|
| IF | Returns one result when a test is true and another when it is false. | =IF(D2>5,"Large","Small") | "Large" |
| AND | True only when every test is true. | =AND(D2>5,C2="East") | TRUE |
| OR | True when at least one test is true. | =OR(C3="East",D3>3) | TRUE |
| IFERROR | Replaces any error with a value you choose. | =IFERROR(D2/0,"n/a") | "n/a" |
| Function | What it does | Example | Result |
|---|---|---|---|
| TRIM | Removes leading and trailing spaces and collapses doubled spaces. | =TRIM(F2) | "ana lopez" |
| PROPER | Capitalizes the first letter of each word. | =PROPER(TRIM(F2)) | "Ana Lopez" |
| LEFT | Returns the first characters of a text. | =LEFT(F3,2) | "bo" |
| LEN | Counts the characters in a text. | =LEN(B2) | 4 |
| SUBSTITUTE | Replaces one piece of text with another. | =SUBSTITUTE(F3," ","_") | "bo_chen" |
| Function | What it does | Example | Result |
|---|---|---|---|
| VLOOKUP | Finds a key in the first column and returns a value from a column to its right. | =VLOOKUP("Tape",B2:E5,4,FALSE) | 1.25 |
| XLOOKUP | Finds a key in one range and returns the matching value from another. | =XLOOKUP("Tape",B2:B5,E2:E5,"Not found") | 1.25 |
| INDEX | Returns the value at a given position in a range. | =INDEX(E2:E5,3) | 2.5 |
| MATCH | Returns the position of a value in a range. | =MATCH("Tape",B2:B5,0) | 4 |
| Function | What it does | Example | Result |
|---|---|---|---|
| DATE | Builds a date from year, month, and day. | =DATE(2026,3,9)=A5 | TRUE |
| YEAR | Returns the year of a date. | =YEAR(A2) | 2026 |
| MONTH | Returns the month number of a date. | =MONTH(A3) | 2 |
| EDATE | Moves a date forward or back by whole months. | =TEXT(EDATE(A2,1),"yyyy-mm-dd") | "2026-02-15" |
| EOMONTH | Returns the last day of a month. | =TEXT(EOMONTH(A2,0),"yyyy-mm-dd") | "2026-01-31" |
| DATEDIF | Counts complete months, years, or days between two dates. | =DATEDIF(A2,A5,"m") | 1 |
| NETWORKDAYS | Counts working days between two dates, both included. | =NETWORKDAYS(A2,A3) | 14 |
| Function | What it does | Example | Result |
|---|---|---|---|
| COUNT | Counts cells that hold numbers. | =COUNT(D2:D5) | 4 |
| COUNTA | Counts every nonblank cell, headers included. | =COUNTA(B1:B5) | 5 |
| COUNTIF | Counts cells that meet one condition. | =COUNTIF(C2:C5,"West") | 2 |
| COUNTIFS | Counts rows that meet several conditions. | =COUNTIFS(C2:C5,"East",D2:D5,">5") | 2 |
| SUMIF | Adds the values that meet one condition. | =SUMIF(B2:B5,"Pens",D2:D5) | 16 |
| SUMIFS | Adds the values that meet several conditions. | =SUMIFS(D2:D5,B2:B5,"Pens",C2:C5,"West") | 6 |
| AVERAGEIF | Averages the values that meet one condition. | =AVERAGEIF(C2:C5,"West",E2:E5) | 5.25 |
How to read the formula tables
Every example uses the sample sales table in A1:F5: dates in A, items in B, regions in C, quantities in D, prices in E, and customer names in F. Change the ranges to match your own data and keep the pattern. The Result column is what the formula returns on that table, so a different result on your sheet points to different data rather than a different formula.
Text goes in double quotes, ranges use a colon, and a criterion such as ">5" is text too. In the Result column, text is shown in quotation marks only to make spaces visible; the cell itself shows the text without them.
Pick the formula by the question
Start from the question, not the function name. "How much?" is SUM, or SUMIFS when a condition applies. "How many?" is COUNT for numbers, COUNTA for anything nonblank, and COUNTIF or COUNTIFS for conditions. "Which value belongs to this key?" is XLOOKUP, VLOOKUP, or INDEX with MATCH. "What should the cell say?" is IF, extended with AND and OR.
When a result looks too large, check whether a total row sits inside the range. When a count looks too small, check for numbers stored as text: COUNT ignores them, while COUNTA counts every nonblank cell, header included.
Habits that keep formulas correct
Lock a range that must not move when you copy a formula down by adding dollar signs, as in $B$2:$B$5. Clean lookup keys with TRIM before matching, and ask for an exact match: in VLOOKUP the fourth argument is FALSE. Wrap a formula in IFERROR only when you know which error to expect; otherwise it hides real mistakes.
Dates are numbers, so the cleanest way to show one as text is TEXT with a format such as "yyyy-mm-dd". Format codes follow the language of your copy of Excel, so a localized version may need different letters. TODAY changes every day, so it has no fixed result to show in a table; use it as the end date in DATEDIF when you need an age or a tenure.
Common errors at a glance
A #N/A means a lookup found nothing. #REF! means a referenced cell or column was removed. #DIV/0! means a division by zero, and #NAME? means a misspelled function name or a function your version does not have. Read the error code first; it tells you which part of the formula to inspect.
Versions and what to learn next
Everything in the tables except XLOOKUP works in older desktop versions. XLOOKUP is available in Microsoft 365, Excel 2021 and later, and Excel for the web; the lookup comparison guide linked below sets it against VLOOKUP in detail. Formulas go faster with keyboard habits, so keep the shortcuts cheat sheet beside this one.
Your turn
In E2, total the Qty for Mugs sold in the West region.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Region | Qty | Mugs West | |
| 2 | Mugs | West | 6 | ||
| 3 | Cups | East | 10 | ||
| 4 | Mugs | East | 3 | ||
| 5 | Mugs | West | 5 | ||
| 6 | Cups | West | 7 |
Answer: =SUMIFS(C2:C6,A2:A6,"Mugs",B2:B6,"West")
Worked example
Formulas that combine the building blocks above, evaluated on the sample sales table shown at the top of the formula tables.
| Formula | Result |
|---|---|
=IFERROR(XLOOKUP("Glue",B2:B5,E2:E5),"Not stocked") | "Not stocked" |
=SUMPRODUCT((C2:C5="East")*D2:D5*E2:E5) | 40 |
=SUMIFS(D2:D5,A2:A5,">="&DATE(2026,2,1),A2:A5,"<"&DATE(2026,3,1)) | 10 |
=IF(COUNTIF(B2:B5,"Pens")>1,"Repeat item","Single") | "Repeat item" |
=PROPER(TRIM(F2))&" ("&C2&")" | "Ana Lopez (East)" |
=INDEX(F2:F5,MATCH(MAX(D2:D5),D2:D5,0)) | "di wu" |
Quick mistake checklist
- Copying a range such as D2:D5 down without locking it with dollar signs.
- Counting with
COUNTwhen numbers are stored as text;COUNTAshows how many cells are nonblank. - Looking up a key that carries a trailing space instead of cleaning it with
TRIM. - Using
IFERRORto silence every error instead of handling the expected one.
Frequently asked questions
What are the most important Excel formulas to learn first?
SUM, IF, COUNTIF, SUMIFS, and a lookup such as XLOOKUP or VLOOKUP cover most everyday work. Add text and date formulas as the tasks come up.
Do these formulas work in every version of Excel?
All of them work in older desktop versions except XLOOKUP, which needs Microsoft 365, Excel 2021 or later, or Excel for the web.
Why does my formula return a different result than the table?
Check that your ranges match your data, that numbers are not stored as text, and that text keys have no extra spaces. The Result column was computed on the sample table.
How do I remember which function to use?
Match the question to a group: a total is SUM, a conditional total is SUMIFS, a conditional count is COUNTIFS, and a lookup is XLOOKUP.
Practice the skill
Continue with a checked FormulaCamp lesson: Your First Formula Free
Open the free lesson