Skip to content
v0.1.0latest release21 skills2 categories3 sub-categories22 tested scripts68 reference docsContact us
Vector Agentic Skills
Submit a skill

XLSX Functions Reference

Reference for excel-formulas · 617 lines

Productivity / Excelv1.0.0Updated OCT 20 20263 references1 script

Detailed documentation for Excel functions organized by category.

Mathematical Functions

SUM

Adds all numbers in a range.

excel
=SUM(number1, [number2], ...)
=SUM(A1:A100)
=SUM(A1:A10, C1:C10, 100)
  • Ignores text and logical values
  • Empty cells treated as 0

SUMPRODUCT

Multiplies corresponding components and returns the sum.

excel
=SUMPRODUCT(array1, [array2], ...)
=SUMPRODUCT(A1:A10, B1:B10)
=SUMPRODUCT((A1:A10="East")*(B1:B10))
  • Arrays must have same dimensions
  • Useful for conditional calculations without SUMIFS

AVERAGE

Returns arithmetic mean.

excel
=AVERAGE(number1, [number2], ...)
=AVERAGE(A1:A100)
  • Ignores empty cells
  • Text values cause #VALUE! error

AVERAGEIF / AVERAGEIFS

Conditional average.

excel
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
=AVERAGEIF(A:A, ">0", B:B)

ROUND / ROUNDUP / ROUNDDOWN

Round to specified decimal places.

excel
=ROUND(number, num_digits)
=ROUND(3.14159, 2)         // 3.14
=ROUNDUP(3.141, 2)         // 3.15
=ROUNDDOWN(3.149, 2)       // 3.14
  • Negative num_digits rounds left of decimal

CEILING / FLOOR

Round to nearest multiple.

excel
=CEILING(number, significance)
=FLOOR(number, significance)
=CEILING(23, 5)            // 25
=FLOOR(23, 5)              // 20

ABS

Returns absolute value.

excel
=ABS(number)
=ABS(-5)                   // 5

MOD

Returns remainder after division.

excel
=MOD(number, divisor)
=MOD(10, 3)                // 1

POWER / SQRT

Exponentiation and square root.

excel
=POWER(number, power)
=POWER(2, 8)               // 256
=SQRT(16)                  // 4

MIN / MAX

Returns minimum or maximum.

excel
=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)
=MIN(A1:A100)
=MAX(B1:B100)

MINIFS / MAXIFS

Conditional minimum/maximum.

excel
=MINIFS(min_range, criteria_range1, criteria1, ...)
=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MINIFS(B:B, A:A, "East")

Logical Functions

IF

Conditional evaluation.

excel
=IF(logical_test, value_if_true, value_if_false)
=IF(A1>100, "Over", "Under")
=IF(A1="", "Empty", A1)

IFS

Multiple conditions without nesting.

excel
=IFS(condition1, value1, condition2, value2, ..., TRUE, default)
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")
  • Evaluates conditions in order
  • Use TRUE as final condition for default

SWITCH

Match value to list.

excel
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
=SWITCH(A1, 1, "Jan", 2, "Feb", 3, "Mar", "Unknown")

AND / OR / NOT

Logical operators.

excel
=AND(logical1, [logical2], ...)
=OR(logical1, [logical2], ...)
=NOT(logical)
=AND(A1>0, B1>0)           // TRUE if both positive
=OR(A1="Yes", A1="Y")      // TRUE if either
=NOT(A1="No")              // TRUE if not "No"

XOR

Exclusive OR - TRUE if odd number of arguments are TRUE.

excel
=XOR(logical1, [logical2], ...)
=XOR(A1, B1)               // TRUE if exactly one is TRUE

IFERROR / IFNA

Error handling.

excel
=IFERROR(value, value_if_error)
=IFNA(value, value_if_na)
=IFERROR(A1/B1, 0)
=IFNA(VLOOKUP(E1, A:B, 2, FALSE), "Not Found")
  • IFERROR catches all errors
  • IFNA catches only #N/A

ISERROR / ISNA / ISBLANK

Error and state checking.

excel
=ISERROR(value)
=ISNA(value)
=ISBLANK(value)
=ISNUMBER(value)
=ISTEXT(value)

Lookup Functions

XLOOKUP (Excel 365/2021+)

Modern lookup function.

excel
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Parameters:

  • lookup_value: Value to find
  • lookup_array: Range to search
  • return_array: Range to return from
  • if_not_found: Value if not found (optional)
  • match_mode: 0=exact, -1=exact or next smaller, 1=exact or next larger, 2=wildcard
  • search_mode: 1=first-to-last, -1=last-to-first, 2=binary ascending, -2=binary descending
excel
=XLOOKUP(E1, A:A, B:B, "Not Found", 0)
=XLOOKUP(E1, A:A, B:C, , 0)           // returns multiple columns

XMATCH (Excel 365/2021+)

Returns position of value.

excel
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
=XMATCH("Apple", A:A, 0)

VLOOKUP

Vertical lookup (legacy).

excel
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: Value to find in first column
  • table_array: Table range
  • col_index_num: Column number to return (1-based)
  • range_lookup: FALSE for exact match, TRUE for approximate
excel
=VLOOKUP(E1, A1:C100, 2, FALSE)

Limitations:

  • Only searches leftmost column
  • Column index breaks if columns inserted
  • Cannot look left

HLOOKUP

Horizontal lookup.

excel
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
=HLOOKUP("Q1", A1:D5, 2, FALSE)

INDEX

Returns value at intersection.

excel
=INDEX(array, row_num, [col_num])
=INDEX(A1:C10, 5, 2)               // row 5, column 2
=INDEX(A:A, 5)                     // row 5 of column A

MATCH

Returns position of value.

excel
=MATCH(lookup_value, lookup_array, [match_type])
  • match_type: 0=exact, 1=less than or equal (sorted asc), -1=greater than or equal (sorted desc)
excel
=MATCH("Apple", A:A, 0)

INDEX + MATCH Combined

Flexible lookup in any direction.

excel
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
=INDEX(B:B, MATCH(E1, A:A, 0))

Two-way lookup:

excel
=INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0))

OFFSET

Returns reference offset from starting point.

excel
=OFFSET(reference, rows, cols, [height], [width])
=OFFSET(A1, 2, 3)                  // 2 rows down, 3 cols right
=SUM(OFFSET(A1, 0, 0, 10, 1))     // dynamic range

INDIRECT

Returns reference specified by text string.

excel
=INDIRECT(ref_text, [a1])
=INDIRECT("A" & B1)
=SUM(INDIRECT("Sheet2!A1:A10"))

CHOOSE

Returns value from list by index.

excel
=CHOOSE(index_num, value1, [value2], ...)
=CHOOSE(A1, "Jan", "Feb", "Mar", "Apr")

Text Functions

CONCAT / TEXTJOIN

Combine text.

excel
=CONCAT(text1, [text2], ...)
=CONCAT(A1, " ", B1)

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
=TEXTJOIN(", ", TRUE, A1:A10)

LEFT / RIGHT / MID

Extract text portions.

excel
=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)
=LEFT("Hello", 2)                  // "He"
=RIGHT("Hello", 2)                 // "lo"
=MID("Hello", 2, 3)                // "ell"

LEN

Returns text length.

excel
=LEN(text)
=LEN("Hello")                      // 5

Find position of substring.

excel
=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])
=FIND("o", "Hello")                // 5
  • FIND is case-sensitive
  • SEARCH allows wildcards, case-insensitive

SUBSTITUTE / REPLACE

Replace text.

excel
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=SUBSTITUTE("Hello", "l", "L")     // "HeLLo"
=SUBSTITUTE("Hello", "l", "L", 1)  // "HeLlo"

=REPLACE(old_text, start_num, num_chars, new_text)
=REPLACE("Hello", 2, 3, "XX")      // "HXXo"

TRIM / CLEAN

Clean text.

excel
=TRIM(text)                        // removes extra spaces
=CLEAN(text)                       // removes non-printable chars

UPPER / LOWER / PROPER

Change case.

excel
=UPPER("hello")                    // "HELLO"
=LOWER("HELLO")                    // "hello"
=PROPER("hello world")             // "Hello World"

TEXT

Format number as text.

excel
=TEXT(value, format_text)
=TEXT(A1, "0.00")                  // "123.45"
=TEXT(A1, "$#,##0.00")             // "$1,234.56"
=TEXT(A1, "yyyy-mm-dd")            // "2024-01-15"

VALUE

Convert text to number.

excel
=VALUE(text)
=VALUE("123.45")                   // 123.45

REPT

Repeat text.

excel
=REPT(text, number_times)
=REPT("*", 5)                      // "*****"

Date and Time Functions

TODAY / NOW

Current date/time.

excel
=TODAY()                           // current date (no time)
=NOW()                             // current date and time

DATE / TIME

Create date/time from components.

excel
=DATE(year, month, day)
=DATE(2024, 1, 15)

=TIME(hour, minute, second)
=TIME(14, 30, 0)

YEAR / MONTH / DAY

Extract date components.

excel
=YEAR(date)
=MONTH(date)
=DAY(date)
=YEAR(A1)                          // extracts year

HOUR / MINUTE / SECOND

Extract time components.

excel
=HOUR(time)
=MINUTE(time)
=SECOND(time)

DATEDIF

Calculate date difference.

excel
=DATEDIF(start_date, end_date, unit)

Units:

  • "Y" = complete years
  • "M" = complete months
  • "D" = days
  • "YM" = months ignoring years
  • "YD" = days ignoring years
  • "MD" = days ignoring months and years
excel
=DATEDIF(A1, B1, "Y")              // years between dates

EDATE / EOMONTH

Add months to date.

excel
=EDATE(start_date, months)
=EOMONTH(start_date, months)       // end of resulting month
=EDATE(A1, 3)                      // 3 months later
=EOMONTH(A1, 0)                    // end of current month

WEEKDAY / WEEKNUM

Week information.

excel
=WEEKDAY(date, [return_type])
=WEEKNUM(date, [return_type])
=WEEKDAY(A1, 2)                    // 1=Monday, 7=Sunday

NETWORKDAYS / WORKDAY

Business day calculations.

excel
=NETWORKDAYS(start_date, end_date, [holidays])
=WORKDAY(start_date, days, [holidays])
=NETWORKDAYS(A1, B1, C1:C10)       // excludes weekends and holidays
=WORKDAY(A1, 10, C1:C10)           // 10 business days later

Statistical Functions

COUNT / COUNTA / COUNTBLANK

Count cells.

excel
=COUNT(value1, [value2], ...)      // counts numbers
=COUNTA(value1, [value2], ...)     // counts non-empty
=COUNTBLANK(range)                 // counts empty

COUNTIF / COUNTIFS

Conditional count.

excel
=COUNTIF(range, criteria)
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=COUNTIF(A:A, ">100")
=COUNTIF(A:A, "Apple")
=COUNTIF(A:A, "A*")                // wildcard
=COUNTIFS(A:A, "East", B:B, ">1000")

SUMIF / SUMIFS

Conditional sum.

excel
=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, ...)

=SUMIF(A:A, "East", B:B)
=SUMIFS(C:C, A:A, "East", B:B, ">1000")

MEDIAN

Returns middle value.

excel
=MEDIAN(number1, [number2], ...)
=MEDIAN(A1:A100)

MODE

Returns most frequent value.

excel
=MODE(number1, [number2], ...)
=MODE.SNGL(A1:A100)                // single mode
=MODE.MULT(A1:A100)                // array of modes

STDEV / VAR

Standard deviation and variance.

excel
=STDEV.S(number1, [number2], ...)  // sample
=STDEV.P(number1, [number2], ...)  // population
=VAR.S(number1, [number2], ...)
=VAR.P(number1, [number2], ...)

PERCENTILE / QUARTILE

Distribution analysis.

excel
=PERCENTILE.INC(array, k)
=QUARTILE.INC(array, quart)
=PERCENTILE.INC(A1:A100, 0.9)      // 90th percentile
=QUARTILE.INC(A1:A100, 1)          // first quartile

RANK

Returns rank of number.

excel
=RANK.EQ(number, ref, [order])
=RANK.AVG(number, ref, [order])
=RANK.EQ(A1, A:A, 0)               // 0=descending, 1=ascending

LARGE / SMALL

Returns nth largest/smallest.

excel
=LARGE(array, k)
=SMALL(array, k)
=LARGE(A1:A100, 1)                 // largest
=LARGE(A1:A100, 3)                 // third largest

Dynamic Array Functions (Excel 365/2021+)

FILTER

Filter array by condition.

excel
=FILTER(array, include, [if_empty])
=FILTER(A2:D100, B2:B100>1000)
=FILTER(A2:D100, (B2:B100>1000)*(C2:C100="East"))  // AND
=FILTER(A2:D100, (B2:B100>1000)+(C2:C100="East"))  // OR
=FILTER(A2:D100, B2:B100>1000, "No results")

SORT

Sort array.

excel
=SORT(array, [sort_index], [sort_order], [by_col])
=SORT(A2:C100)                     // sort by first column ascending
=SORT(A2:C100, 2, -1)              // sort by column 2 descending
=SORT(A2:C100, {2,1}, {-1,1})      // multi-level sort

SORTBY

Sort by another array.

excel
=SORTBY(array, by_array1, [sort_order1], ...)
=SORTBY(A2:C100, D2:D100, -1)      // sort A:C by D descending

UNIQUE

Extract unique values.

excel
=UNIQUE(array, [by_col], [exactly_once])
=UNIQUE(A2:A100)                   // unique values
=UNIQUE(A2:A100, , TRUE)           // values appearing exactly once

SEQUENCE

Generate number sequence.

excel
=SEQUENCE(rows, [columns], [start], [step])
=SEQUENCE(10)                      // 1-10
=SEQUENCE(5, 3)                    // 5 rows, 3 columns
=SEQUENCE(10, 1, 0, 0.5)           // 0, 0.5, 1, 1.5, ...

RANDARRAY

Generate random numbers.

excel
=RANDARRAY([rows], [columns], [min], [max], [integer])
=RANDARRAY(5, 3)                   // 5x3 random decimals 0-1
=RANDARRAY(10, 1, 1, 100, TRUE)    // 10 random integers 1-100

Spill Range Operator (#)

Reference entire spill range.

excel
=SUM(A2#)                          // sum entire spill range starting at A2
=COUNTA(A2#)                       // count spill range

Information Functions

ISNUMBER / ISTEXT / ISBLANK

Type checking.

excel
=ISNUMBER(value)
=ISTEXT(value)
=ISBLANK(value)
=ISLOGICAL(value)
=ISFORMULA(reference)

TYPE

Returns type number.

excel
=TYPE(value)

Returns: 1=number, 2=text, 4=logical, 16=error, 64=array

CELL / INFO

Cell and system information.

excel
=CELL("type", A1)                  // cell properties
=INFO("osversion")                 // system info

NA

Returns #N/A error.

excel
=NA()

Useful for placeholder values in charts.

More in Excel

excel-advanced-formulas

Modern Excel 365/2021+ formulas — LAMBDA and LAMBDA helpers (MAP, REDUCE, SCAN, BYROW, BYCOL), LET, dynamic arrays (FILTER, SORT, UNIQUE, HSTACK, VSTACK, TAKE, DROP), TEXTSPLIT, REGEX functions, GROUPBY and PIVOTBY.

excel-fundamentals

Excel workbook fundamentals — cells, ranges, absolute/relative references, named ranges, number formats, conditional formatting, sheets and views, sorting, filtering, tables and keyboard shortcuts.