70 formulas
LookupXLOOKUP with multiple criteria
=XLOOKUP(1,(A2:A7=B9)*(B2:B7=B10),C2:C7,"Not found")LookupINDEX MATCH with multiple criteria=INDEX(C2:C7,MATCH(1,(A2:A7=B9)*(B2:B7=B10),0))LookupTwo-way lookup in Excel (INDEX MATCH MATCH)=INDEX(B2:E5,MATCH(B7,A2:A5,0),MATCH(B8,B1:E1,0))LookupVLOOKUP to the left (return a value from a column on the left)=INDEX(A2:A5,MATCH(B7,C2:C5,0))LookupFind the last match in Excel (XLOOKUP last value)=XLOOKUP(B9,B2:B7,C2:C7,"Not found",0,-1)LookupExcel lookup within a range of values (tiered rates)=VLOOKUP(B7,A2:B5,2,TRUE)LookupCase-sensitive lookup in Excel=INDEX(B2:B4,MATCH(TRUE,EXACT(A2:A4,B6),0))Dynamic arraysExcel FILTER function with multiple criteria=FILTER(A2:C8,(B2:B8="East")*(C2:C8>100),"No match")Dynamic arraysExcel formula for a sorted unique list=SORT(UNIQUE(A2:A9))Dynamic arraysSort by multiple columns with a formula (SORTBY)=SORTBY(A2:C8,B2:B8,1,C2:C8,-1)Dynamic arraysExcel formula for a sequence of dates (SEQUENCE)=SEQUENCE(6,1,A2,7)Dynamic arraysExcel LET function example=LET(subtotal,SUMPRODUCT(B2:B4,C2:C4),tax,subtotal*B6,subtotal+tax)Dynamic arraysExcel LAMBDA function: create a custom function=LAMBDA(price,cost,(price-cost)/price)(A2,B2)Dynamic arraysExcel formula for the top 3 values with names=LARGE(B2:B7,{1;2;3})TextSplit text by delimiter in Excel (TEXTSPLIT)=TEXTSPLIT(A2,",")TextExtract text before or after a character in Excel=TEXTBEFORE(A2,"@")TextExcel formula to extract numbers from text=--TEXTJOIN("",TRUE,IFERROR(--MID(A2,SEQUENCE(LEN(A2)),1),""))TextExtract text between two characters in Excel=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)TextREGEXEXTRACT Google Sheets examples=REGEXEXTRACT(A2,"INV-[0-9]+")TextGoogle Sheets FILTER with REGEXMATCH (contains text)=FILTER(A2:B6,REGEXMATCH(A2:A6,"(?i)refund"))TextExcel formula to count words in a cell=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)TextExcel formula to extract the last word=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))DatesNETWORKDAYS excluding holidays=NETWORKDAYS(A2,B2,D2:D3)DatesNETWORKDAYS.INTL custom weekend (Friday-Saturday, 6-day week)=NETWORKDAYS.INTL(A2,B2,"0000110",D2:D3)DatesAdd business days to a date in Excel (WORKDAY)=WORKDAY(A2,B2,D2:D3)DatesExcel formula to calculate age from date of birth=DATEDIF(A2,B2,"Y")DatesExcel formula for fiscal quarter and fiscal year=INT(MOD(MONTH(A2)-$B$2,12)/3)+1DatesExcel EOMONTH: first and last day of the month=EOMONTH(A2,0)DatesAdd months to a date in Excel (EDATE)=EDATE(A2,B2)DatesExcel ISO week number formula=ISOWEEKNUM(A2)DatesExcel formula to calculate hours worked overnight=MOD(B2-A2,1)FinanceExcel PMT formula for a loan or mortgage payment=PMT(B1/12,B2*12,-B3)FinanceLoan amortization schedule formula in Excel (IPMT, PPMT)=IPMT(B1/12,B4,B2*12,-B3)FinanceExcel formula for total interest paid on a loan=-CUMIPMT(B1/12,B2*12,B3,1,B2*12,0)FinanceIRR vs XIRR in Excel (with dates)=XIRR(B2:B6,A2:A6)FinanceExcel NPV formula (with initial investment)=NPV(B8,B3:B6)+B2FinanceCAGR formula in Excel=(B2/B1)^(1/B3)-1FinanceCompound interest formula in Excel=B1*(1+B2/B3)^(B3*B4)FinanceCompound interest with monthly contributions in Excel=FV(B2/12,B3*12,-B4,-B1)FinanceExcel formula: how much to save each month to reach a goal=PMT(B2/12,B3*12,B4,-B1)FinanceConvert APR to APY in Excel (EFFECT)=EFFECT(B1,B2)Conditional sums & countsSUMIFS between two dates=SUMIFS(C2:C9,A2:A9,">="&B11,A2:A9,"<="&B12)Conditional sums & countsSum by month in Excel with SUMIFS=SUMIFS($C$2:$C$9,$A$2:$A$9,">="&E2,$A$2:$A$9,"<="&EOMONTH(E2,0))Conditional sums & countsSUMIFS with multiple criteria in the same column (OR)=SUM(SUMIFS(C2:C7,A2:A7,{"East","West"}))Conditional sums & countsCount unique values with criteria in Excel (COUNTIFS unique)=IFERROR(ROWS(UNIQUE(FILTER(B2:B9,A2:A9=B11))),0)Conditional sums & countsRunning total formula in Excel=SUM($C$2:C2)Conditional sums & countsWeighted average formula in Excel=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)Conditional sums & countsCOUNTIFS between two numbers=COUNTIFS(C2:C9,">="&B11,C2:C9,"<="&B12)Conditional sums & countsRank with criteria in Excel (RANK IF)=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1Conditional sums & countsSum only visible (filtered) cells in Excel=SUBTOTAL(109,C2:C7)Data cleaningRemove duplicates in Excel with a formula=UNIQUE(A2:A8)Data cleaningExcel formula to find duplicates=COUNTIF($A$2:$A$8,A2)>1Data cleaningRemove extra spaces in Excel (TRIM not working)=TRIM(CLEAN(SUBSTITUTE(A2,UNICHAR(160)," ")))Data cleaningExcel formula to split first and last name=LEFT(A2,FIND(" ",A2)-1)Data cleaningExcel formula to convert text to numbers=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""))Data cleaningAdd leading zeros in Excel=TEXT(A2,"00000")Data cleaningCombine cells with a comma in Excel (TEXTJOIN)=TEXTJOIN(", ",TRUE,A2:A5)Trading & cryptoPosition size formula in Excel (risk per trade)=ROUNDDOWN(B1*B2/ABS(B3-B4),4)Trading & cryptoExcel formula for profit and loss percentage (long & short)=(C2-B2)*D2*IF(A2="Short",-1,1)Trading & cryptoMax drawdown formula in Excel=MAX($B$2:B2)Trading & cryptoMoving average formula in Excel (SMA & EMA)=AVERAGE(B2:B4)Trading & cryptoWin rate, expectancy and profit factor formulas in Excel=COUNTIF(B2:B9,">0")/COUNT(B2:B9)Trading & cryptoRisk reward ratio formula in Excel (R-multiple)=ABS(B3-B1)/ABS(B1-B2)Trading & cryptoAverage entry price formula in Excel (DCA)=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)BusinessProfit margin formula in Excel=A2-B2BusinessExcel formula: price from cost and target margin=(B2-A2)/A2BusinessBreak-even formula in Excel=ROUNDUP(B1/(B2-B3),0)BusinessExcel formula to add or remove VAT=A2*(1+C2)BusinessPercentage change formula in Excel=(B2-A2)/A2BusinessPercentage of total formula in Excel=C2/SUM($C$2:$C$7)No formulas match. Try a function name like FILTER or a task like "split names".
Download all 70 formulas as an .xlsx cheat sheet
Free, no sign-up. One sheet per formula with the example data and the working formula already entered, plus an index. Excel 365-only and Google Sheets-only formulas are included as text.
Download the cheat sheet (.xlsx)SKIP THE SETUP
Want the finished spreadsheet?
Gosuoo templates already have these formulas built in: trading journals, bookkeeping, budgets and more, for Excel and Google Sheets.
All 8 templates — $39See all bundlesOr try the 24 free calculators