■ Excel Formulas – Basic to Advanced with
Examples
Formula Description Example
SUM Adds a range of numbers =SUM(A1:A5)
AVERAGE Calculates the average =AVERAGE(A1:A5)
MAX Returns the largest value =MAX(A1:A5)
MIN Returns the smallest value =MIN(A1:A5)
IF Conditional logic =IF(A1>50, "Pass", "Fail")
COUNT Counts numeric cells =COUNT(A1:A10)
COUNTA Counts non-empty cells =COUNTA(A1:A10)
COUNTIF Counts cells with condition =COUNTIF(A1:A10, ">50")
VLOOKUP Vertical lookup =VLOOKUP("Apple", A2:B10, 2, FALSE)
HLOOKUP Horizontal lookup =HLOOKUP("Q1", A1:D2, 2, FALSE)
XLOOKUP Modern replacement for VLOOKUP =XLOOKUP("Apple", A2:A10, B2:B10)
INDEX Returns value at position =INDEX(B2:B10, 3)
MATCH Finds position of value =MATCH("Apple", A2:A10, 0)
INDEX + MATCH More flexible than VLOOKUP =INDEX(B2:B10, MATCH("Apple", A2:A10, 0))
IFERROR Handles errors in formula =IFERROR(A1/B1, "Error")
TEXTJOIN Joins text with delimiter =TEXTJOIN(", ", TRUE, A1:A5)
FILTER Filters range based on condition =FILTER(A2:B10, B2:B10 > 50)
SORT Sorts a range or array =SORT(A2:B10, 2, -1)
UNIQUE Returns unique values =UNIQUE(A2:A20)
SEQUENCE Generates sequential numbers =SEQUENCE(5,1,1,1)
LET Define named variables =LET(x, A1+10, x*2)
LAMBDA Create custom functions =LAMBDA(x, x+1)(5)
SUMPRODUCT Sum of products of arrays =SUMPRODUCT(A1:A5, B1:B5)
AGGREGATE Performs aggregate functions =AGGREGATE(9, 5, A1:A10)
OFFSET Returns range from reference =OFFSET(A1,2,1)
INDIRECT Reference by text =INDIRECT("B" & A1)
SUBTOTAL Calculates subtotal with filters =SUBTOTAL(9, A1:A10)
GETPIVOTDATA Extracts data from PivotTable =GETPIVOTDATA("Sales", A3, "Region", "South")
NETWORKDAYS Working days between dates =NETWORKDAYS(A1, B1)
WORKDAY Next working day =WORKDAY(A1, 5)
EOMONTH End of month date =EOMONTH(A1, 1)
TEXT Formats number/text/date =TEXT(A1, "dd-mmm-yyyy")
MOD Returns remainder =MOD(10,3)
QUOTIENT Returns integer division =QUOTIENT(10,3)
ROUND Rounds to nearest value =ROUND(A1, 2)
RANDBETWEEN Random number in range =RANDBETWEEN(1,100)
CHOOSE Selects from a list =CHOOSE(2, "Red", "Blue", "Green")
AND Returns TRUE if all conditions are TRUE=AND(A1>10, B1<5)
OR Returns TRUE if any condition is TRUE =OR(A1>10, B1<5)
NOT Returns the opposite of a logical value =NOT(A1>10)
ISBLANK Checks if a cell is blank =ISBLANK(A1)
ISNUMBER Checks if the value is a number =ISNUMBER(A1)
ISTEXT Checks if the value is text =ISTEXT(A1)
ISERROR Checks if the value is any error =ISERROR(A1)
IFNA Returns a value if the formula returns #N/A
=IFNA(VLOOKUP("Apple", A2:B10, 2, FALSE), "No
SEARCH Finds position of text in a string (case-insensitive)
=SEARCH("x", A1)
FIND Finds position of text (case-sensitive) =FIND("X", A1)
LEFT Returns characters from the start =LEFT(A1, 3)
RIGHT Returns characters from the end =RIGHT(A1, 3)
MID Returns characters from the middle =MID(A1, 2, 3)
LEN Returns the number of characters =LEN(A1)
TRIM Removes extra spaces =TRIM(A1)
CONCAT Combines text strings =CONCAT(A1, " ", B1)
CONCATENATE Old version of CONCAT =CONCATENATE(A1, " ", B1)
VALUE Converts text to number =VALUE("100")
TEXTSPLIT Splits text by delimiter (Excel 365+) =TEXTSPLIT("A,B,C", ",")
TEXTBEFORE Gets text before a character =TEXTBEFORE("apple-pie", "-")
TEXTAFTER Gets text after a character =TEXTAFTER("apple-pie", "-")
REPLACE Replaces part of a text string =REPLACE(A1, 1, 3, "XYZ")
SUBSTITUTE Replaces all occurrences =SUBSTITUTE(A1, "old", "new")
NOW Returns current date and time =NOW()
TODAY Returns current date =TODAY()
DAY Extracts day from date =DAY(A1)
MONTH Extracts month from date =MONTH(A1)
YEAR Extracts year from date =YEAR(A1)
HOUR Extracts hour from time =HOUR(A1)
MINUTE Extracts minute from time =MINUTE(A1)
SECOND Extracts second from time =SECOND(A1)
WEEKDAY Returns day of the week =WEEKDAY(A1)
WEEKNUM Returns week number of year =WEEKNUM(A1)
DAYS Returns days between two dates =DAYS(A2, A1)
DATEDIF Returns difference between dates =DATEDIF(A1, A2, "d")
TIME Creates a time value =TIME(10,30,0)
DATE Creates a date value =DATE(2025,8,7)
EDATE Returns date after months =EDATE(A1, 3)
HYPERLINK Creates a clickable link =HYPERLINK("[Link] "OpenAI")
CELL Returns info about a cell =CELL("address", A1)
ROW Returns the row number =ROW(A1)
COLUMN Returns the column number =COLUMN(A1)
ROWS Returns number of rows in a range =ROWS(A1:A10)
COLUMNS Returns number of columns =COLUMNS(A1:D1)
TRANSPOSE Changes rows to columns =TRANSPOSE(A1:B3)
SHEET Returns the sheet number =SHEET()
SHEETS Returns number of sheets =SHEETS()
FORMULATEXT Returns the formula as text =FORMULATEXT(A1)
ISFORMULA Checks if a cell has a formula =ISFORMULA(A1)
RAND Returns random number between 0 and 1
=RAND()
RANDARRAY Returns array of random numbers =RANDARRAY(5, 1, 1, 100, TRUE)
BIN2DEC Binary to Decimal =BIN2DEC(1010)
DEC2BIN Decimal to Binary =DEC2BIN(10)
HEX2DEC Hexadecimal to Decimal =HEX2DEC("A")
DEC2HEX Decimal to Hexadecimal =DEC2HEX(10)
OCT2DEC Octal to Decimal =OCT2DEC(12)
DEC2OCT Decimal to Octal =DEC2OCT(10)