Explore 100+ Excel functions with syntax, examples, VBA implementations, and practical applications
Adds all numbers in a range of cells
SUM(number1, [number2], ...)=SUM(A1:A10)Returns sum of values in A1 to A10
Range("C1").Value = Application.WorksheetFunction.Sum(Range("A1:A10"))Returns the average (arithmetic mean) of the arguments
AVERAGE(number1, [number2], ...)=AVERAGE(A1:A10)Returns average of values in A1 to A10
Range("C1").Value = Application.WorksheetFunction.Average(Range("A1:A10"))Counts the number of cells that contain numbers
COUNT(value1, [value2], ...)=COUNT(A1:A10)Returns count of numeric values in A1 to A10
Range("C1").Value = Application.WorksheetFunction.Count(Range("A1:A10"))Returns the largest value in a set of values
MAX(number1, [number2], ...)=MAX(A1:A10)Returns maximum value in A1 to A10
Range("C1").Value = Application.WorksheetFunction.Max(Range("A1:A10"))Returns the smallest value in a set of values
MIN(number1, [number2], ...)=MIN(A1:A10)Returns minimum value in A1 to A10
Range("C1").Value = Application.WorksheetFunction.Min(Range("A1:A10"))Rounds a number to a specified number of digits
ROUND(number, num_digits)=ROUND(3.14159, 2)Returns 3.14
Range("C1").Value = Application.WorksheetFunction.Round(3.14159, 2)Returns the absolute value of a number
ABS(number)=ABS(-5)Returns 5
Range("C1").Value = Abs(-5)Returns a positive square root
SQRT(number)=SQRT(16)Returns 4
Range("C1").Value = Sqr(16)Returns the result of a number raised to a power
POWER(number, power)=POWER(2, 3)Returns 8
Range("C1").Value = Application.WorksheetFunction.Power(2, 3)Returns the remainder from division
MOD(number, divisor)=MOD(10, 3)Returns 1
Range("C1").Value = 10 Mod 3Performs a logical test and returns one value for TRUE and another for FALSE
IF(logical_test, [value_if_true], [value_if_false])=IF(A1>10, "High", "Low")Returns "High" if A1 > 10, otherwise "Low"
If Range("A1").Value > 10 Then Range("B1").Value = "High" Else Range("B1").Value = "Low"Returns TRUE if all arguments are TRUE
AND(logical1, [logical2], ...)=AND(A1>5, B1<10)Returns TRUE if both conditions are met
If Range("A1").Value > 5 And Range("B1").Value < 10 ThenReturns TRUE if any argument is TRUE
OR(logical1, [logical2], ...)=OR(A1>5, B1<10)Returns TRUE if either condition is met
If Range("A1").Value > 5 Or Range("B1").Value < 10 ThenReverses the logic of its argument
NOT(logical)=NOT(A1>5)Returns TRUE if A1 is NOT greater than 5
If Not Range("A1").Value > 5 ThenChecks multiple conditions and returns a value corresponding to the first TRUE condition
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]...)=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F")Returns grade based on score in A1
Select Case Range("A1").Value: Case Is >= 90: result = "A": Case Is >= 80: result = "B"Looks up a value in the first column and returns a value in the same row from another column
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP("Apple", A1:C10, 2, FALSE)Finds "Apple" in column A and returns corresponding value from column B
result = Application.WorksheetFunction.VLookup("Apple", Range("A1:C10"), 2, False)Looks up a value in the top row and returns a value in the same column from a specified row
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])=HLOOKUP("Q1", A1:E5, 3, FALSE)Finds "Q1" in row 1 and returns value from row 3
result = Application.WorksheetFunction.HLookup("Q1", Range("A1:E5"), 3, False)Modern replacement for VLOOKUP with more flexibility and better performance
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])=XLOOKUP("Apple", A:A, B:B, "Not Found")Finds "Apple" in column A and returns corresponding value from column B
result = Application.WorksheetFunction.XLookup("Apple", Range("A:A"), Range("B:B"), "Not Found")Returns a value from a specific position in an array
INDEX(array, row_num, [column_num])=INDEX(A1:C10, 5, 2)Returns value from 5th row, 2nd column of range A1:C10
result = Application.WorksheetFunction.Index(Range("A1:C10"), 5, 2)Returns the position of an item in an array
MATCH(lookup_value, lookup_array, [match_type])=MATCH("Apple", A1:A10, 0)Returns position of "Apple" in range A1:A10
position = Application.WorksheetFunction.Match("Apple", Range("A1:A10"), 0)Returns a reference to a range that is offset from a starting cell
OFFSET(reference, rows, cols, [height], [width])=OFFSET(A1, 2, 1, 3, 2)Returns reference to B3:C5 (2 rows down, 1 column right from A1)
Set rng = Range("A1").Offset(2, 1).Resize(3, 2)Returns the reference specified by a text string
INDIRECT(ref_text, [a1])=INDIRECT("A" & ROW())Returns reference to cell in column A of current row
result = Range(Range("A1").Value).ValueJoins several text strings into one string
CONCATENATE(text1, [text2], ...)=CONCATENATE("Hello", " ", "World")Returns "Hello World"
result = "Hello" & " " & "World"Returns the leftmost characters from a text value
LEFT(text, [num_chars])=LEFT("Excel", 3)Returns "Exc"
result = Left("Excel", 3)Returns the rightmost characters from a text value
RIGHT(text, [num_chars])=RIGHT("Excel", 3)Returns "cel"
result = Right("Excel", 3)Returns a specific number of characters from a text string starting at the position you specify
MID(text, start_num, num_chars)=MID("Excel", 2, 3)Returns "xce"
result = Mid("Excel", 2, 3)Returns the number of characters in a text string
LEN(text)=LEN("Excel")Returns 5
result = Len("Excel")Converts text to uppercase
UPPER(text)=UPPER("excel")Returns "EXCEL"
result = UCase("excel")Converts text to lowercase
LOWER(text)=LOWER("EXCEL")Returns "excel"
result = LCase("EXCEL")Capitalizes the first letter in each word of a text value
PROPER(text)=PROPER("john doe")Returns "John Doe"
result = StrConv("john doe", vbProperCase)Removes spaces from text except for single spaces between words
TRIM(text)=TRIM(" Hello World ")Returns "Hello World"
result = Trim(" Hello World ")Substitutes new_text for old_text in a text string
SUBSTITUTE(text, old_text, new_text, [instance_num])=SUBSTITUTE("Hello World", "World", "Excel")Returns "Hello Excel"
result = Replace("Hello World", "World", "Excel")Finds one text string within another text string (case-sensitive)
FIND(find_text, within_text, [start_num])=FIND("c", "Excel")Returns 3
result = InStr("Excel", "c")Returns the current date
TODAY()=TODAY()Returns current date (e.g., 12/25/2024)
Range("A1").Value = DateReturns the current date and time
NOW()=NOW()Returns current date and time
Range("A1").Value = NowReturns the serial number of a particular date
DATE(year, month, day)=DATE(2024, 12, 25)Returns December 25, 2024
Range("A1").Value = DateSerial(2024, 12, 25)Returns the year corresponding to a date
YEAR(serial_number)=YEAR(TODAY())Returns current year (e.g., 2024)
result = Year(Date)Returns the month corresponding to a date
MONTH(serial_number)=MONTH(TODAY())Returns current month (1-12)
result = Month(Date)Returns the day of the month corresponding to a date
DAY(serial_number)=DAY(TODAY())Returns current day (1-31)
result = Day(Date)Returns the day of the week corresponding to a date
WEEKDAY(serial_number, [return_type])=WEEKDAY(TODAY())Returns day of week (1=Sunday, 7=Saturday)
result = Weekday(Date)Calculates the number of days, months, or years between two dates
DATEDIF(start_date, end_date, unit)=DATEDIF(A1, B1, "Y")Returns years between dates in A1 and B1
result = DateDiff("yyyy", Range("A1").Value, Range("B1").Value)Returns the median of the given numbers
MEDIAN(number1, [number2], ...)=MEDIAN(A1:A10)Returns middle value in A1:A10
result = Application.WorksheetFunction.Median(Range("A1:A10"))Returns the most common value in a data set
MODE(number1, [number2], ...)=MODE(A1:A10)Returns most frequently occurring value
result = Application.WorksheetFunction.Mode(Range("A1:A10"))Estimates standard deviation based on a sample
STDEV(number1, [number2], ...)=STDEV(A1:A10)Returns standard deviation of sample
result = Application.WorksheetFunction.StDev(Range("A1:A10"))Returns the correlation coefficient between two data sets
CORREL(array1, array2)=CORREL(A1:A10, B1:B10)Returns correlation between two ranges
result = Application.WorksheetFunction.Correl(Range("A1:A10"), Range("B1:B10"))Calculates the payment for a loan based on constant payments and interest rate
PMT(rate, nper, pv, [fv], [type])=PMT(5%/12, 60, 20000)Returns monthly payment for $20,000 loan at 5% for 5 years
result = Pmt(0.05/12, 60, -20000)Returns the present value of an investment
PV(rate, nper, pmt, [fv], [type])=PV(8%/12, 12*20, 1000)Returns present value of annuity
result = PV(0.08/12, 12*20, -1000)Returns the future value of an investment
FV(rate, nper, pmt, [pv], [type])=FV(6%/12, 10*12, -100, -1000)Returns future value of investment
result = FV(0.06/12, 10*12, -100, -1000)Returns the net present value of an investment based on discount rate and cash flows
NPV(rate, value1, [value2], ...)=NPV(10%, A1:A5)Returns NPV of cash flows at 10% discount rate
result = Application.WorksheetFunction.NPV(0.1, Range("A1:A5"))Returns the internal rate of return for a series of cash flows
IRR(values, [guess])=IRR(A1:A5)Returns internal rate of return
result = Application.WorksheetFunction.IRR(Range("A1:A5"))Sums cells that meet a single criteria
SUMIF(range, criteria, [sum_range])=SUMIF(A1:A10, ">5", B1:B10)Sums values in B1:B10 where corresponding A values > 5
result = Application.WorksheetFunction.SumIf(Range("A1:A10"), ">5", Range("B1:B10"))Sums cells that meet multiple criteria
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)=SUMIFS(C1:C10, A1:A10, "Product A", B1:B10, ">100")Sums values where product is "Product A" and value > 100
result = Application.WorksheetFunction.SumIfs(Range("C1:C10"), Range("A1:A10"), "Product A", Range("B1:B10"), ">100")Counts cells that meet a single criteria
COUNTIF(range, criteria)=COUNTIF(A1:A10, ">5")Counts cells in A1:A10 with values greater than 5
result = Application.WorksheetFunction.CountIf(Range("A1:A10"), ">5")Counts cells that meet multiple criteria
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)=COUNTIFS(A1:A10, "Product A", B1:B10, ">100")Counts rows where product is "Product A" and value > 100
result = Application.WorksheetFunction.CountIfs(Range("A1:A10"), "Product A", Range("B1:B10"), ">100")Returns the average of cells that meet a single criteria
AVERAGEIF(range, criteria, [average_range])=AVERAGEIF(A1:A10, ">5", B1:B10)Averages values in B1:B10 where corresponding A values > 5
result = Application.WorksheetFunction.AverageIf(Range("A1:A10"), ">5", Range("B1:B10"))Returns the average of cells that meet multiple criteria
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)=AVERAGEIFS(C1:C10, A1:A10, "Product A", B1:B10, ">100")Averages values where product is "Product A" and value > 100
result = Application.WorksheetFunction.AverageIfs(Range("C1:C10"), Range("A1:A10"), "Product A", Range("B1:B10"), ">100")Returns TRUE if value is blank
ISBLANK(value)=ISBLANK(A1)Returns TRUE if A1 is empty
result = IsEmpty(Range("A1").Value)Returns TRUE if value is a number
ISNUMBER(value)=ISNUMBER(A1)Returns TRUE if A1 contains a number
result = IsNumeric(Range("A1").Value)Returns TRUE if value is text
ISTEXT(value)=ISTEXT(A1)Returns TRUE if A1 contains text
result = VarType(Range("A1").Value) = vbStringReturns TRUE if value is an error
ISERROR(value)=ISERROR(A1)Returns TRUE if A1 contains an error
result = IsError(Range("A1").Value)Returns a value you specify if a formula evaluates to an error
IFERROR(value, value_if_error)=IFERROR(A1/B1, "Division Error")Returns result of A1/B1 or "Division Error" if error occurs
On Error Resume Next: result = Range("A1").Value / Range("B1").Value: If Err.Number <> 0 Then result = "Division Error"Filters a range of data based on criteria you define
FILTER(array, include, [if_empty])=FILTER(A1:C10, B1:B10>100)Returns rows where column B value > 100
Use AutoFilter or loop through data with conditionsSorts the contents of a range or array
SORT(array, [sort_index], [sort_order], [by_col])=SORT(A1:C10, 2, -1)Sorts data by column 2 in descending order
Range("A1:C10").Sort Key1:=Range("B1"), Order1:=xlDescendingReturns a list of unique values in a list or range
UNIQUE(array, [by_col], [exactly_once])=UNIQUE(A1:A10)Returns unique values from A1:A10
Use Dictionary object or Advanced Filter with Unique Records OnlyGenerates a list of sequential numbers in an array
SEQUENCE(rows, [columns], [start], [step])=SEQUENCE(5, 1, 10, 2)Returns array: 10, 12, 14, 16, 18
Use For loop to generate sequenceReturns an array of random numbers
RANDARRAY([rows], [columns], [min], [max], [whole_number])=RANDARRAY(5, 2, 1, 100, TRUE)Returns 5x2 array of random integers between 1-100
Use Rnd function in loops to generate random arraysConverts a number from one measurement system to another
CONVERT(number, from_unit, to_unit)=CONVERT(100, "C", "F")Converts 100 Celsius to Fahrenheit (212)
result = Application.WorksheetFunction.Convert(100, "C", "F")Returns data from a web service
WEBSERVICE(url)=WEBSERVICE("http://api.example.com/data")Returns data from web API
Use XMLHttpRequest object to call web servicesReturns an aggregated value from a cube
CUBEVALUE(connection, [member_expression1], [member_expression2], ...)=CUBEVALUE("Sales", "[Measures].[Sales Amount]")Returns aggregated value from OLAP cube
Use OLAP objects in VBAReturns the sine of an angle
SIN(number)=SIN(PI()/2)Returns 1
result = Sin(3.14159/2)Returns the cosine of an angle
COS(number)=COS(0)Returns 1
result = Cos(0)Returns the tangent of an angle
TAN(number)=TAN(PI()/4)Returns 1
result = Tan(3.14159/4)Returns e raised to the power of a number
EXP(number)=EXP(1)Returns 2.71828
result = Exp(1)Returns the natural logarithm of a number
LN(number)=LN(2.71828)Returns 1
result = Log(2.71828)Calculates standard deviation based on the entire population
STDEVP(number1, [number2], ...)=STDEVP(A1:A10)Returns population standard deviation
result = Application.WorksheetFunction.StDevP(Range("A1:A10"))Returns the quartile of a data set
QUARTILE(array, quart)=QUARTILE(A1:A10, 1)Returns first quartile
result = Application.WorksheetFunction.Quartile(Range("A1:A10"), 1)Returns the k-th largest value in a data set
LARGE(array, k)=LARGE(A1:A10, 3)Returns 3rd largest value
result = Application.WorksheetFunction.Large(Range("A1:A10"), 3)Returns the k-th smallest value in a data set
SMALL(array, k)=SMALL(A1:A10, 2)Returns 2nd smallest value
result = Application.WorksheetFunction.Small(Range("A1:A10"), 2)Joins text from multiple ranges with a delimiter
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)=TEXTJOIN(", ", TRUE, A1:A3)Joins text with comma separator
result = Join(Array("A", "B", "C"), ", ")Joins text from multiple ranges
CONCAT(text1, [text2], ...)=CONCAT(A1, B1, C1)Joins text without separator
result = A1 & B1 & C1Replaces characters within text
REPLACE(old_text, start_num, num_chars, new_text)=REPLACE("Hello", 2, 3, "i")Returns "Hi"
result = Replace("Hello", "ell", "i")Finds one text string within another (case-insensitive)
SEARCH(find_text, within_text, [start_num])=SEARCH("e", "Excel")Returns 2
result = InStr(1, "Excel", "e")Returns the decimal number for a particular time
TIME(hour, minute, second)=TIME(14, 30, 0)Returns 2:30:00 PM
result = TimeSerial(14, 30, 0)Returns the hour as a number from 0 to 23
HOUR(serial_number)=HOUR(NOW())Returns current hour
result = Hour(Now)Returns the serial number of the date before or after a specified number of workdays
WORKDAY(start_date, days, [holidays])=WORKDAY(A1, 5)Returns date 5 workdays later
result = Application.WorksheetFunction.WorkDay(Range("A1"), 5)Returns the number of whole workdays between two dates
NETWORKDAYS(start_date, end_date, [holidays])=NETWORKDAYS(A1, B1)Returns number of workdays
result = Application.WorksheetFunction.NetworkDays(Range("A1"), Range("B1"))Returns the interest rate per period of an annuity
RATE(nper, pmt, pv, [fv], [type], [guess])=RATE(60, -1000, 50000)Returns monthly interest rate
result = Rate(60, -1000, 50000)Returns the number of periods for an investment
NPER(rate, pmt, pv, [fv], [type])=NPER(0.05/12, -1000, 50000)Returns number of payment periods
result = NPer(0.05/12, -1000, 50000)Returns the interest payment for a given period
IPMT(rate, per, nper, pv, [fv], [type])=IPMT(0.05/12, 1, 60, 50000)Returns interest portion of payment
result = IPmt(0.05/12, 1, 60, 50000)Returns the principal payment for a given period
PPMT(rate, per, nper, pv, [fv], [type])=PPMT(0.05/12, 1, 60, 50000)Returns principal portion of payment
result = PPmt(0.05/12, 1, 60, 50000)Uses an index to return a value from a list of values
CHOOSE(index_num, value1, [value2], ...)=CHOOSE(2, "A", "B", "C")Returns "B"
result = Choose(2, "A", "B", "C")Evaluates an expression against a list of values and returns the result corresponding to the first matching value
SWITCH(expression, value1, result1, [default_or_value2, result2], ...)=SWITCH(A1, 1, "One", 2, "Two", "Other")Returns corresponding text
Select Case A1: Case 1: result = "One": Case 2: result = "Two"Returns a reference as text to a single cell in a worksheet
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])=ADDRESS(1, 1)Returns "$A$1"
result = Cells(1, 1).AddressReturns the column number of a reference
COLUMN([reference])=COLUMN(A1)Returns 1
result = Range("A1").ColumnReturns the row number of a reference
ROW([reference])=ROW(A1)Returns 1
result = Range("A1").RowAppends arrays horizontally and in sequence to return a larger array
HSTACK(array1, [array2], ...)=HSTACK(A1:A3, B1:B3)Returns combined horizontal array
Use array concatenation in VBAAppends arrays vertically and in sequence to return a larger array
VSTACK(array1, [array2], ...)=VSTACK(A1:C1, A2:C2)Returns combined vertical array
Use array concatenation in VBACreates custom, reusable functions and call them by a friendly name
LAMBDA([parameter1, parameter2, …], calculation)=LAMBDA(x, x^2)(5)Returns 25
Create custom VBA functionsAssigns names to calculation results
LET(name1, name_value1, [name2, name_value2], ..., calculation)=LET(x, A1, y, B1, x+y)Returns sum of A1 and B1
Use variables in VBAPerform calculations and statistical analysis
Find and retrieve data from tables
Manipulate text and get data information
Work with dates, times, and periods
Calculate loans, investments, and returns
Modern dynamic array functions