FormulaCamp
Completed
All Excel how-to articles
Practical Excel how-to

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.

ABCDEF
1DateItemRegionQtyPriceCustomer
22026-01-15PensEast102.5␣␣ana␣␣lopez␣
32026-02-03InkWest48bo chen
42026-02-20PensWest62.5cy diaz
52026-03-09TapeEast121.25di wu
Math
FunctionWhat it doesExampleResult
SUMAdds the numbers in a range.=SUM(D2:D5)32
AVERAGEReturns the mean of a range.=AVERAGE(D2:D5)8
ROUNDRounds a number to a set number of decimals.=ROUND(AVERAGE(E2:E5),1)3.6
MAXReturns the largest value.=MAX(D2:D5)12
MINReturns the smallest value.=MIN(E2:E5)1.25
SUMPRODUCTMultiplies matching cells and adds the products, such as quantity times price.=SUMPRODUCT(D2:D5,E2:E5)87
Logic
FunctionWhat it doesExampleResult
IFReturns one result when a test is true and another when it is false.=IF(D2>5,"Large","Small")"Large"
ANDTrue only when every test is true.=AND(D2>5,C2="East")TRUE
ORTrue when at least one test is true.=OR(C3="East",D3>3)TRUE
IFERRORReplaces any error with a value you choose.=IFERROR(D2/0,"n/a")"n/a"
Text
FunctionWhat it doesExampleResult
TRIMRemoves leading and trailing spaces and collapses doubled spaces.=TRIM(F2)"ana lopez"
PROPERCapitalizes the first letter of each word.=PROPER(TRIM(F2))"Ana Lopez"
LEFTReturns the first characters of a text.=LEFT(F3,2)"bo"
LENCounts the characters in a text.=LEN(B2)4
SUBSTITUTEReplaces one piece of text with another.=SUBSTITUTE(F3," ","_")"bo_chen"
Lookup
FunctionWhat it doesExampleResult
VLOOKUPFinds a key in the first column and returns a value from a column to its right.=VLOOKUP("Tape",B2:E5,4,FALSE)1.25
XLOOKUPFinds a key in one range and returns the matching value from another.=XLOOKUP("Tape",B2:B5,E2:E5,"Not found")1.25
INDEXReturns the value at a given position in a range.=INDEX(E2:E5,3)2.5
MATCHReturns the position of a value in a range.=MATCH("Tape",B2:B5,0)4
Date
FunctionWhat it doesExampleResult
DATEBuilds a date from year, month, and day.=DATE(2026,3,9)=A5TRUE
YEARReturns the year of a date.=YEAR(A2)2026
MONTHReturns the month number of a date.=MONTH(A3)2
EDATEMoves a date forward or back by whole months.=TEXT(EDATE(A2,1),"yyyy-mm-dd")"2026-02-15"
EOMONTHReturns the last day of a month.=TEXT(EOMONTH(A2,0),"yyyy-mm-dd")"2026-01-31"
DATEDIFCounts complete months, years, or days between two dates.=DATEDIF(A2,A5,"m")1
NETWORKDAYSCounts working days between two dates, both included.=NETWORKDAYS(A2,A3)14
Counting and conditional totals
FunctionWhat it doesExampleResult
COUNTCounts cells that hold numbers.=COUNT(D2:D5)4
COUNTACounts every nonblank cell, headers included.=COUNTA(B1:B5)5
COUNTIFCounts cells that meet one condition.=COUNTIF(C2:C5,"West")2
COUNTIFSCounts rows that meet several conditions.=COUNTIFS(C2:C5,"East",D2:D5,">5")2
SUMIFAdds the values that meet one condition.=SUMIF(B2:B5,"Pens",D2:D5)16
SUMIFSAdds the values that meet several conditions.=SUMIFS(D2:D5,B2:B5,"Pens",C2:C5,"West")6
AVERAGEIFAverages 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.

Example spreadsheet for sales by region
ABCDE
1ItemRegionQtyMugs West
2MugsWest6
3CupsEast10
4MugsEast3
5MugsWest5
6CupsWest7
Not yet solved.

Worked example

Formulas that combine the building blocks above, evaluated on the sample sales table shown at the top of the formula tables.

FormulaResult
=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 COUNT when numbers are stored as text; COUNTA shows how many cells are nonblank.
  • Looking up a key that carries a trailing space instead of cleaning it with TRIM.
  • Using IFERROR to 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