๐ข Basic Math Formulas
=A1+A2
=A1-A2=A1*A2=A1/A2=A1^2=MOD(A1,A2)=SUM(A1:A10)=PRODUCT(A1:A10)=ROUND(A1,2)=ROUNDUP(A1,0)=ROUNDDOWN(A1,0)=CEILING(A1,5)=FLOOR(A1,5)=ABS(A1)=SQRT(A1)=EXP(A1)=POWER(A1,A2)=PI()=FACT(A1)=RAND()
๐ Statistical Formulas
=AVERAGE(A1:A10)=MEDIAN(A1:A10)=MODE.SNGL(A1:A10)=STDEV.P(A1:A10)=STDEV.S(A1:A10)=VAR.P(A1:A10)=VAR.S(A1:A10)=MAX(A1:A10)=MIN(A1:A10)=LARGE(A1:A10,2)=SMALL(A1:A10,2)=PERCENTILE.EXC(A1:A10,0.9)=PERCENTILE.INC(A1:A10,0.9)=QUARTILE(A1:A10,1)=RANK.EQ(A1,A1:A10)=RANK.AVG(A1,A1:A10)=COUNT(A1:A10)=COUNTA(A1:A10)=COUNTBLANK(A1:A10)=COUNTIF(A1:A10,">50")
๐ Lookup & Reference
=VLOOKUP("apple",A2:B10,2,FALSE)
=HLOOKUP("apple",A1:J2,2,FALSE)=XLOOKUP("apple",A2:A10,B2:B10)=INDEX(A1:C10,2,3)=MATCH(50,A1:A10,0)=OFFSET(A1,2,3)=CHOOSE(2,"Red","Green","Blue")=LOOKUP(10,A1:A10,B1:B10)=TRANSPOSE(A1:A10)=INDIRECT("A1")
๐ง Logical Functions
=IF(A1>50,"Pass","Fail")=IFERROR(A1/B1, "Error")=IFNA(VLOOKUP(...), "Not Found")=AND(A1>0,B1<100)=OR(A1>0,B1<100)=NOT(A1=10)=SWITCH(A1,1,"One",2,"Two","Other")=IFS(A1=1,"One",A1=2,"Two")=ISNUMBER(A1)=ISTEXT(A1)
๐ Date & Time
=TODAY()=NOW()=DAY(A1)=MONTH(A1)=YEAR(A1)=HOUR(A1)=MINUTE(A1)=SECOND(A1)=WEEKDAY(A1)=WEEKNUM(A1)=DATEDIF(A1,B1,"Y")=EDATE(A1,6)=EOMONTH(A1,1)=NETWORKDAYS(A1,B1)=WORKDAY(A1,5)=TEXT(A1,"dd-mm-yyyy")=DATE(2024,4,14)=TIME(14,30,0)=NOW()-A1=ISOWEEKNUM(A1)
๐ค Text Functions
=LEFT(A1,5)=RIGHT(A1,3)=MID(A1,3,4)=LEN(A1)=TRIM(A1)=CONCATENATE(A1," ",B1)=TEXTJOIN(" ",TRUE,A1:A3)=UPPER(A1)=LOWER(A1)=PROPER(A1)=REPLACE(A1,1,5,"Hello")=SUBSTITUTE(A1,"old","new")=FIND("text",A1)=SEARCH("text",A1)=EXACT(A1,B1)=TEXT(A1,"$#,##0.00")=CHAR(65)=CODE("A")=T(A1)=VALUE("123.45")
๐งฎ Financial Functions
=PMT(5%/12,60,-20000)=FV(5%/12,60,-200)=NPV(0.08,B2:B6)=IRR(B2:B6)=RATE(60,-200,10000)=PV(5%/12,60,-200)=DDB(10000,1000,5,1)=SLN(10000,1000,5)=CUMIPMT(0.08/12,60,10000,1,12,0)=CUMPRINC(0.08/12,60,10000,1,12,0)
๐ Information Functions
=ISBLANK(A1)=ISNUMBER(A1)=ISTEXT(A1)=ISLOGICAL(A1)=ISNONTEXT(A1)=ISERROR(A1)=ISERR(A1)=TYPE(A1)=CELL("address",A1)=INFO("osversion")
โ๏ธ Engineering & Complex
=CONVERT(100,"m","ft")=BIN2DEC("1101")=DEC2BIN(13)=HEX2DEC("1A")=COMPLEX(1,2)=IMSUM("3+4i","5+6i")=IMPRODUCT("2+3i","1+4i")=IMABS("3+4i")=IMREAL("3+4i")=IMAGINARY("3+4i")
๐ฆ Array Formulas
=SUM(A1:A10*B1:B10)=TRANSPOSE(A1:C3)=MMULT(A1:B2,C1:D2)=FREQUENCY(A1:A10,B1:B5)=UNIQUE(A1:A10)=FILTER(A1:A10,A1:A10>50)=SORT(A1:A10)=SEQUENCE(5)=RANDARRAY(5,1)=SINGLE(A1:A10)
๐ Web & External
=WEBSERVICE("https://api.example.com/data")=FILTERXML(A1,"//item")=ENCODEURL(A1)=HYPERLINK("https://example.com","Click Here")=INFO("system")=ENVIRON("USERNAME")=WEBSERVICE("...")(with Power Query)=IMPORTDATA(...)(via Google Sheets)=IMPORTXML(...)(via Google Sheets)=IMPORTHTML(...)(via Google Sheets)
๐งฐ Data Tools
=REMOVE_DUPLICATES()(via ribbon)=TEXTSPLIT(A1,",")=TEXTBEFORE(A1,",")=TEXTAFTER(A1,",")=LET(x,A1,x+10)=LAMBDA(x,x+10)(5)=GROUPBY(...)(via Power Query)=SORTBY(A1:A10,B1:B10)=DROP(A1:A10,2)=TAKE(A1:A10,5)
๐ Dynamic Arrays & Spill
=UNIQUE(A1:A20)=SORT(A1:A20)=FILTER(A1:A20,A1:A20>100)=SEQUENCE(10)=RANDARRAY(5,2)=BYROW(A1:B10,LAMBDA(x,SUM(x)))=BYCOL(A1:B10,LAMBDA(x,MAX(x)))=WRAPCOLS(A1:A10,2)=WRAPROWS(A1:A10,3)=SCAN(0,A1:A5,LAMBDA(a,b,a+b))
๐งฎ Custom & Advanced
=SUBTOTAL(9,A1:A10)=AGGREGATE(9,4,A1:A10)=GETPIVOTDATA("Sales",$A$3,"Region","West")=FORMULATEXT(A1)=N(A1)=ERROR.TYPE(A1)=EVALUATE("2+2")(Excel 4.0 macro)=CELL("filename",A1)=SHEET(A1)=SHEETS()
๐งพ Miscellaneous
=REPT("x",5)=BAHTTEXT(1234)=ROMAN(2024)=ARABIC("MMXXIV")=HYPGEOM.DIST(...)=GAMMA(5)=PHI(1)=T.TEST(...)=Z.TEST(...)=NORM.S.DIST(...)
๐ Bonus (Useful Combos & Helpers)
=IF(AND(A1>0,A1<100),"OK","Out")=TEXT(TODAY(),"dddd, mmmm dd, yyyy")=VLOOKUP(A1,Table1[#All],2,FALSE)=INDEX(A:A,MATCH(MAX(B:B),B:B,0))=IF(A1="",NA(),A1)=IF(COUNTA(A1:A10)=0,"Empty","Filled")=IF(SUM(A1:A10)>100,"Too High","OK")=AVERAGEIFS(A1:A10,B1:B10,">50")=COUNTIFS(A1:A10,">50",B1:B10,"<100")=TEXTJOIN(", ",TRUE,IF(A1:A10>50,A1:A10,""))
