Complete Excel Formulas Reference

Your ultimate cheat sheet for Excel formulas. Bookmark this page!

Math & Statistical Formulas

Function	Description	Example
SUM	Add numbers together	=SUM(A1:A10)
AVERAGE	Calculate mean average	=AVERAGE(A1:A10)
COUNT	Count cells with numbers	=COUNT(A1:A10)
COUNTA	Count non-empty cells	=COUNTA(A1:A10)
COUNTBLANK	Count empty cells	=COUNTBLANK(A1:A10)
MAX	Find largest value	=MAX(A1:A10)
MIN	Find smallest value	=MIN(A1:A10)
MEDIAN	Find middle value	=MEDIAN(A1:A10)
MODE	Find most frequent value	=MODE.SNGL(A1:A10)

9 rows

Function	Description	Example
ROUND	Round to specified decimals	=ROUND(A1, 2)
ROUNDUP	Always round up	=ROUNDUP(A1, 2)
ROUNDDOWN	Always round down	=ROUNDDOWN(A1, 2)
ABS	Absolute value (remove negative)	=ABS(-5) returns 5
SQRT	Square root	=SQRT(16) returns 4
POWER	Raise to power	=POWER(2, 3) returns 8
MOD	Remainder after division	=MOD(10, 3) returns 1
RAND	Random between 0 and 1	=RAND()
RANDBETWEEN	Random in range	=RANDBETWEEN(1, 100)

9 rows

Conditional Formulas

Function	Description	Example
IF	Basic condition	=IF(A1>100, High, Low)
IFS	Multiple conditions (365)	=IFS(A1>90, A, A1>80, B, TRUE, F)
AND	All conditions must be true	=IF(AND(A1>50, B1<100), Yes, No)
OR	Any condition must be true	=IF(OR(A1>50, B1>50), Yes, No)
NOT	Reverse condition	=IF(NOT(A1>50), Small, Large)
IFERROR	Handle errors gracefully	=IFERROR(A1/B1, Error)
IFNA	Handle only #N/A errors	=IFNA(VLOOKUP(...), Not Found)
SWITCH	Multiple exact matches (365)	=SWITCH(A1, 1, Mon, 2, Tue)

8 rows

Text Formulas

Function	Description	Example
CONCATENATE	Join text	=CONCATENATE(A1, B1)
CONCAT	Join text (modern)	=CONCAT(A1:A10)
TEXTJOIN	Join with delimiter (365)	=TEXTJOIN(delimiter, TRUE, A1:A10)
LEFT	Extract from start	=LEFT(A1, 3)
RIGHT	Extract from end	=RIGHT(A1, 3)
MID	Extract from middle	=MID(A1, 2, 5)
LEN	Text length	=LEN(A1)
TRIM	Remove extra spaces	=TRIM(A1)
UPPER	Convert to UPPERCASE	=UPPER(A1)
LOWER	Convert to lowercase	=LOWER(A1)
PROPER	Convert to Title Case	=PROPER(A1)
SUBSTITUTE	Replace text	=SUBSTITUTE(A1, old, new)
REPLACE	Replace by position	=REPLACE(A1, 1, 3, NEW)
FIND	Find position (case-sensitive)	=FIND(text, A1)
SEARCH	Find position (case-insensitive)	=SEARCH(text, A1)
TEXT	Format numbers as text	=TEXT(A1, 0.00)
VALUE	Convert text to number	=VALUE(123)

17 rows

Lookup & Reference Formulas

Function	Description	Example
VLOOKUP	Vertical lookup	=VLOOKUP(A1, Table, 2, FALSE)
HLOOKUP	Horizontal lookup	=HLOOKUP(A1, Table, 2, FALSE)
XLOOKUP	Modern lookup (365)	=XLOOKUP(A1, LookupRange, ReturnRange)
INDEX	Return value by position	=INDEX(A1:A10, 5)
MATCH	Find position	=MATCH(Apple, A1:A10, 0)
INDEX-MATCH	Powerful lookup combo	=INDEX(B1:B10, MATCH(Apple, A1:A10, 0))
OFFSET	Reference offset from cell	=OFFSET(A1, 2, 3)
INDIRECT	Reference from text	=INDIRECT(A & ROW())
CHOOSE	Pick from list	=CHOOSE(2, Red, Blue, Green)
ROW	Get row number	=ROW()
COLUMN	Get column number	=COLUMN()

11 rows

Date & Time Formulas

Function	Description	Example
TODAY	Current date	=TODAY()
NOW	Current date and time	=NOW()
DATE	Create date	=DATE(2025, 11, 27)
YEAR	Extract year	=YEAR(A1)
MONTH	Extract month	=MONTH(A1)
DAY	Extract day	=DAY(A1)
WEEKDAY	Day of week (1=Sunday)	=WEEKDAY(A1)
WORKDAY	Calculate business days	=WORKDAY(A1, 10)
NETWORKDAYS	Count business days	=NETWORKDAYS(A1, B1)
DATEDIF	Date difference	=DATEDIF(A1, B1, d)
EDATE	Add months	=EDATE(A1, 3)
EOMONTH	End of month	=EOMONTH(A1, 0)
TIME	Create time	=TIME(14, 30, 0)
HOUR	Extract hour	=HOUR(A1)
MINUTE	Extract minute	=MINUTE(A1)
SECOND	Extract second	=SECOND(A1)

16 rows

Statistical Formulas (Advanced)

Function	Description	Example
SUMIF	Conditional sum	=SUMIF(A1:A10, >50, B1:B10)
SUMIFS	Multiple criteria sum	=SUMIFS(C1:C10, A1:A10, Sales, B1:B10, >50)
COUNTIF	Conditional count	=COUNTIF(A1:A10, >50)
COUNTIFS	Multiple criteria count	=COUNTIFS(A1:A10, Sales, B1:B10, >50)
AVERAGEIF	Conditional average	=AVERAGEIF(A1:A10, >50)
AVERAGEIFS	Multiple criteria average	=AVERAGEIFS(C1:C10, A1:A10, Sales, B1:B10, >50)
SUMPRODUCT	Array multiplication & sum	=SUMPRODUCT(A1:A10, B1:B10)

7 rows

Function	Description	Example
LARGE	Nth largest value	=LARGE(A1:A10, 2)
SMALL	Nth smallest value	=SMALL(A1:A10, 2)
RANK	Rank values	=RANK(A1, A1:A10, 0)
PERCENTILE	Find percentile	=PERCENTILE(A1:A10, 0.9)
QUARTILE	Find quartile	=QUARTILE(A1:A10, 1)
STDEV.S	Standard deviation (sample)	=STDEV.S(A1:A10)
STDEV.P	Standard deviation (population)	=STDEV.P(A1:A10)
VAR.S	Variance (sample)	=VAR.S(A1:A10)
VAR.P	Variance (population)	=VAR.P(A1:A10)
CORREL	Correlation coefficient	=CORREL(A1:A10, B1:B10)

10 rows

Array Formulas (Excel 365)

Function	Description	Example
FILTER	Filter data by condition	=FILTER(A1:C10, B1:B10>50)
SORT	Sort data	=SORT(A1:C10, 2, 1)
SORTBY	Sort by another range	=SORTBY(A1:A10, B1:B10, -1)
UNIQUE	Get unique values	=UNIQUE(A1:A10)
SEQUENCE	Generate number sequence	=SEQUENCE(10)
RANDARRAY	Random numbers array	=RANDARRAY(10)
TRANSPOSE	Flip rows/columns	=TRANSPOSE(A1:C10)

7 rows

Financial Formulas

Function	Description	Example
PMT	Loan payment calculation	=PMT(5%/12, 60, 10000)
FV	Future value	=FV(5%/12, 60, -200)
PV	Present value	=PV(5%/12, 60, -200)
NPV	Net present value	=NPV(10%, B1:B10)
IRR	Internal rate of return	=IRR(A1:A10)
RATE	Interest rate	=RATE(60, -200, 10000)
NPER	Number of periods	=NPER(5%/12, -200, 10000)

7 rows

Information Formulas

Function	Description	Example
ISBLANK	Check if blank	=ISBLANK(A1)
ISNUMBER	Check if number	=ISNUMBER(A1)
ISTEXT	Check if text	=ISTEXT(A1)
ISERROR	Check if error	=ISERROR(A1)
ISNA	Check if #N/A	=ISNA(A1)
TYPE	Get value type	=TYPE(A1)
CELL	Get cell information	=CELL(address, A1)

7 rows

Quick Reference Table

Category	Most Used Formulas
Math	SUM, AVERAGE, COUNT, ROUND
Conditional	IF, IFS, SUMIF, COUNTIF
Lookup	VLOOKUP, XLOOKUP, INDEX-MATCH
Text	CONCATENATE, LEFT, RIGHT, TRIM
Date	TODAY, DATEDIF, WORKDAY
Array (365)	FILTER, SORT, UNIQUE

6 rows

Pro Tips

1. Absolute References

=$A$1  // Lock both row & column
=$A1   // Lock column only
=A$1   // Lock row only

2. Named Ranges Instead of: =SUM(A1:A100) Use: =SUM(SalesData)

3. Array Formulas (Older Excel) Press Ctrl+Shift+Enter to create: {=formula}

4. Formula Auditing

F2: Edit formula
F9: Evaluate part of formula
Ctrl+`: Show all formulas

5. Error Values

#DIV/0! = Division by zero
#N/A = Not available
#NAME? = Excel doesn't recognize text
#NULL! = Wrong range operator
#NUM! = Invalid numeric value
#REF! = Invalid cell reference
#VALUE! = Wrong data type

All Excel Formulas at a Glance

Here's every formula in one comprehensive table for quick reference:

Formula	Category	Description	Example
SUM	Math	Add numbers	=SUM(A1:A10)
AVERAGE	Math	Calculate average	=AVERAGE(A1:A10)
COUNT	Math	Count numbers	=COUNT(A1:A10)
COUNTA	Math	Count non-empty cells	=COUNTA(A1:A10)
COUNTBLANK	Math	Count blank cells	=COUNTBLANK(A1:A10)
MAX	Math	Find largest value	=MAX(A1:A10)
MIN	Math	Find smallest value	=MIN(A1:A10)
MEDIAN	Math	Find median	=MEDIAN(A1:A10)
MODE	Math	Most frequent value	=MODE.SNGL(A1:A10)
ROUND	Math	Round number	=ROUND(A1, 2)
ROUNDUP	Math	Round up	=ROUNDUP(A1, 2)
ROUNDDOWN	Math	Round down	=ROUNDDOWN(A1, 2)
ABS	Math	Absolute value	=ABS(-5)
SQRT	Math	Square root	=SQRT(16)
POWER	Math	Power	=POWER(2, 3)
MOD	Math	Remainder	=MOD(10, 3)
RAND	Math	Random number	=RAND()
RANDBETWEEN	Math	Random in range	=RANDBETWEEN(1, 100)
IF	Conditional	Basic condition	=IF(A1>100, "High", "Low")
IFS	Conditional	Multiple conditions	=IFS(A1>90, "A", A1>80, "B")
AND	Conditional	All true	=AND(A1>50, B1<100)
OR	Conditional	Any true	=OR(A1>50, B1>50)
NOT	Conditional	Reverse condition	=NOT(A1>50)
IFERROR	Conditional	Handle errors	=IFERROR(A1/B1, "Error")
IFNA	Conditional	Handle #N/A	=IFNA(VLOOKUP(...), "Not Found")
SWITCH	Conditional	Multiple matches	=SWITCH(A1, 1, "Mon", 2, "Tue")
CONCATENATE	Text	Join text	=CONCATENATE(A1, " ", B1)
CONCAT	Text	Join text modern	=CONCAT(A1:A10)
TEXTJOIN	Text	Join with delimiter	=TEXTJOIN(", ", TRUE, A1:A10)
LEFT	Text	Extract from left	=LEFT(A1, 3)
RIGHT	Text	Extract from right	=RIGHT(A1, 3)
MID	Text	Extract from middle	=MID(A1, 2, 5)
LEN	Text	Text length	=LEN(A1)
TRIM	Text	Remove spaces	=TRIM(A1)
UPPER	Text	Uppercase	=UPPER(A1)
LOWER	Text	Lowercase	=LOWER(A1)
PROPER	Text	Title case	=PROPER(A1)
SUBSTITUTE	Text	Replace text	=SUBSTITUTE(A1, "old", "new")
REPLACE	Text	Replace by position	=REPLACE(A1, 1, 3, "NEW")
FIND	Text	Find position (case)	=FIND("text", A1)
SEARCH	Text	Find position (no case)	=SEARCH("text", A1)
TEXT	Text	Format as text	=TEXT(A1, "0.00")
VALUE	Text	Text to number	=VALUE("123")
VLOOKUP	Lookup	Vertical lookup	=VLOOKUP(A1, Table, 2, FALSE)
HLOOKUP	Lookup	Horizontal lookup	=HLOOKUP(A1, Table, 2, FALSE)
XLOOKUP	Lookup	Modern lookup	=XLOOKUP(A1, Range1, Range2)
INDEX	Lookup	Get by position	=INDEX(A1:A10, 5)
MATCH	Lookup	Find position	=MATCH("Apple", A1:A10, 0)
INDEX-MATCH	Lookup	Powerful combo	=INDEX(B:B, MATCH("Apple", A:A, 0))
OFFSET	Lookup	Offset reference	=OFFSET(A1, 2, 3)
INDIRECT	Lookup	Text to reference	=INDIRECT("A" & ROW())
CHOOSE	Lookup	Pick from list	=CHOOSE(2, "Red", "Blue")
ROW	Lookup	Row number	=ROW()
COLUMN	Lookup	Column number	=COLUMN()
TODAY	Date	Current date	=TODAY()
NOW	Date	Current date & time	=NOW()
DATE	Date	Create date	=DATE(2025, 11, 27)
YEAR	Date	Extract year	=YEAR(A1)
MONTH	Date	Extract month	=MONTH(A1)
DAY	Date	Extract day	=DAY(A1)
WEEKDAY	Date	Day of week	=WEEKDAY(A1)
WORKDAY	Date	Business days	=WORKDAY(A1, 10)
NETWORKDAYS	Date	Count workdays	=NETWORKDAYS(A1, B1)
DATEDIF	Date	Date difference	=DATEDIF(A1, B1, "d")
EDATE	Date	Add months	=EDATE(A1, 3)
EOMONTH	Date	End of month	=EOMONTH(A1, 0)
TIME	Date	Create time	=TIME(14, 30, 0)
HOUR	Date	Extract hour	=HOUR(A1)
MINUTE	Date	Extract minute	=MINUTE(A1)
SECOND	Date	Extract second	=SECOND(A1)
SUMIF	Statistics	Conditional sum	=SUMIF(A:A, ">50", B:B)
SUMIFS	Statistics	Multi-criteria sum	=SUMIFS(C:C, A:A, "Sales", B:B, ">50")
COUNTIF	Statistics	Conditional count	=COUNTIF(A:A, ">50")
COUNTIFS	Statistics	Multi-criteria count	=COUNTIFS(A:A, "Sales", B:B, ">50")
AVERAGEIF	Statistics	Conditional average	=AVERAGEIF(A:A, ">50")
AVERAGEIFS	Statistics	Multi-criteria average	=AVERAGEIFS(C:C, A:A, "Sales")
SUMPRODUCT	Statistics	Array multiply & sum	=SUMPRODUCT(A:A, B:B)
LARGE	Statistics	Nth largest	=LARGE(A:A, 2)
SMALL	Statistics	Nth smallest	=SMALL(A:A, 2)
RANK	Statistics	Rank value	=RANK(A1, A:A, 0)
PERCENTILE	Statistics	Percentile	=PERCENTILE(A:A, 0.9)
QUARTILE	Statistics	Quartile	=QUARTILE(A:A, 1)
STDEV.S	Statistics	Std dev sample	=STDEV.S(A:A)
STDEV.P	Statistics	Std dev population	=STDEV.P(A:A)
VAR.S	Statistics	Variance sample	=VAR.S(A:A)
VAR.P	Statistics	Variance population	=VAR.P(A:A)
CORREL	Statistics	Correlation	=CORREL(A:A, B:B)
FILTER	Array	Filter data	=FILTER(A:C, B:B>50)
SORT	Array	Sort data	=SORT(A:C, 2, 1)
SORTBY	Array	Sort by range	=SORTBY(A:A, B:B, -1)
UNIQUE	Array	Unique values	=UNIQUE(A:A)
SEQUENCE	Array	Number sequence	=SEQUENCE(10)
RANDARRAY	Array	Random array	=RANDARRAY(10)
TRANSPOSE	Array	Flip rows/columns	=TRANSPOSE(A:C)
PMT	Financial	Loan payment	=PMT(5%/12, 60, 10000)
FV	Financial	Future value	=FV(5%/12, 60, -200)
PV	Financial	Present value	=PV(5%/12, 60, -200)
NPV	Financial	Net present value	=NPV(10%, B:B)
IRR	Financial	Internal rate return	=IRR(A:A)
RATE	Financial	Interest rate	=RATE(60, -200, 10000)
NPER	Financial	Number periods	=NPER(5%/12, -200, 10000)
ISBLANK	Information	Check if blank	=ISBLANK(A1)
ISNUMBER	Information	Check if number	=ISNUMBER(A1)
ISTEXT	Information	Check if text	=ISTEXT(A1)
ISERROR	Information	Check if error	=ISERROR(A1)
ISNA	Information	Check if #N/A	=ISNA(A1)
TYPE	Information	Value type	=TYPE(A1)
CELL	Information	Cell info	=CELL("address", A1)

108 rows

Summary

This reference covers 100+ formulas across all categories:

✅ Math & Statistics
✅ Conditional Logic
✅ Text Manipulation
✅ Lookups & References
✅ Date & Time
✅ Array Functions (Excel 365)
✅ Financial Calculations

Bookmark this page and refer back anytime you need a formula! 📚