Excel Formulas Cheat Sheet

Look up and filter

• XLOOKUP 2021+ Find A2 in one column and return the value next to it from another.Copy =XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")
• VLOOKUP Find A2 in the first column of a table and return the 3rd column. FALSE means exact match.Copy =VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
• INDEX + MATCH Works in every Excel version, and can look to the left, which VLOOKUP cannot.Copy =INDEX(C:C, MATCH(A2, A:A, 0))
• FILTER 2021+ Every row where column B is East, or None if there aren't any.Copy =FILTER(A2:C100, B2:B100="East", "None")
• UNIQUE 2021+ The list with duplicates removed.Copy =UNIQUE(A2:A100)
• SORT 2021+ Sort the range by its 3rd column, largest first.Copy =SORT(A2:C100, 3, -1)

Sum and count with conditions

Text conditions accept wildcards: "*east*" matches any cell that contains east.

• SUMIF Add up column C wherever column A is East.Copy =SUMIF(A:A, "East", C:C)
• SUMIFS The same with several conditions. Note that the column to add comes first.Copy =SUMIFS(C:C, A:A, "East", B:B, ">=100")
• COUNTIF How many cells in column A are East.Copy =COUNTIF(A:A, "East")
• COUNTIFS Count the rows that meet every condition.Copy =COUNTIFS(A:A, "East", B:B, ">100")
• AVERAGEIF Average column C wherever column A is East.Copy =AVERAGEIF(A:A, "East", C:C)
• SUMPRODUCT Multiply the pairs and add them up, for example quantity times price.Copy =SUMPRODUCT(B2:B10, C2:C10)

Logic and numbers

• IF One thing when the test is true, another when it isn't.Copy =IF(B2>=50, "Pass", "Fail")
• IFS 2019+ Several tests in order, without nesting. The final TRUE is the "otherwise".Copy =IFS(B2>=90, "A", B2>=80, "B", TRUE, "C")
• IF + AND Both tests must pass. Swap AND for OR when either one is enough.Copy =IF(AND(B2>0, C2="Yes"), "OK", "")
• IFERROR Show 0 instead of #DIV/0! or any other error.Copy =IFERROR(A2/B2, 0)
• Find duplicates TRUE when the value in A2 appears more than once in column A.Copy =COUNTIF(A:A, A2)>1
• ROUND Round to 2 decimals. ROUNDUP and ROUNDDOWN force the direction.Copy =ROUND(A2, 2)

Text

• Join text Put two cells together with a space between them.Copy =A2 & " " & B2
• TEXTJOIN 2019+ Join a whole range with a separator, skipping blanks.Copy =TEXTJOIN(", ", TRUE, A2:A10)
• LEFT / RIGHT The first 3 characters. RIGHT takes them from the end, MID from the middle.Copy =LEFT(A2, 3)
• TEXTBEFORE 365 Everything before the first space, such as a first name. TEXTAFTER gives the rest.Copy =TEXTBEFORE(A2, " ")
• TRIM Remove stray spaces. They are the usual reason a lookup can't find a value.Copy =TRIM(A2)
• SUBSTITUTE Replace one piece of text with another. Here it removes the dashes.Copy =SUBSTITUTE(A2, "-", "")

Dates

Format codes such as yyyy follow the language of your Excel, so a German Excel wants JJJJ-MM-TT.

• TODAY Today's date, updated every day. NOW() includes the time.Copy =TODAY()
• Years between Full years between two dates, for example an age. "m" gives months, "d" days.Copy =DATEDIF(A2, TODAY(), "y")
• Working days Weekdays between two dates, counting both ends.Copy =NETWORKDAYS(A2, B2)
• Add months The same day 3 months later. Use a negative number to go back.Copy =EDATE(A2, 3)
• End of month The last day of that month. Use 1 for the following month.Copy =EOMONTH(A2, 0)
• Date as text A date in a fixed format, for joining into text or exporting.Copy =TEXT(A2, "yyyy-mm-dd")