Common Excel Functions
Common Excel Functions
This page highlights some of the most useful and frequently used Microsoft Excel functions for data analysis, reporting, and productivity.
They are grouped by purpose to help quickly identify functions that assist in certain categories.
Video content on all the Excel Functions seen below can be found on the Finsyte Academy (opens in new tab) as well.
Overview by Category
Lookup & Reference
- XLOOKUP — Search for a value and return the corresponding result.
- XMATCH — Find the position of an item within a range.
- INDIRECT — Create a cell reference from text.
- OFFSET — Return a range reference offset from another cell.
Aggregation & Calculation
- SUMIFS — Add values that meet multiple criteria.
- AVERAGEIFS — Calculate an average with multiple criteria.
- COUNTIFS — Count values that meet multiple criteria.
- LARGE — Return the k-th largest value.
- SMALL — Return the k-th smallest value.
Logical & Conditional
- IFS — Test multiple conditions and return results.
- SWITCH — Match a value against options and return the corresponding result.
- IFERROR — Return a fallback value if a formula errors.
Array & Dynamic Range
- FILTER — Filter a dataset based on given criteria.
- UNIQUE — Return a list of unique items.
- SORT — Sort an array or range.
Text & Concatenation
- CONCAT — Combine text strings or ranges.
- TEXTJOIN — Combine text strings with delimiters and options to ignore blanks.
XLOOKUP
Searches a range or an array and returns the item corresponding to the first exact or approximate match it finds.
| Argument | Description | Required |
|---|---|---|
| lookup_value | The value to search for | ✔ |
| lookup_array | The array or range to search | ✔ |
| return_array | The array or range to return a value from | ✔ |
| if_not_found | Value to return if no match found | X |
| match_mode | Type of match (0=exact, -1=next smaller, 1=next larger) | X |
| search_mode | Search direction (1=first-to-last, -1=last-to-first) | X |
Example:
=XLOOKUP("Widget", A2:A10, B2:B10, "Not Found")
This function looks for the word "Widget" in the range A2 through A10 and, if found, returns the first result in the same row in column B. Otherwise, it will return "Not Found".
FILTER
Filters a range of data based on criteria you define to return an array of matching results.
| Argument | Description | Required |
|---|---|---|
| array | The range or array to filter. | ✔ |
| include | The Boolean condition or logical test to determine which rows or columns to return. | ✔ |
| if_empty | The value to return if no records meet the criteria. | X |
Example:
=FILTER(A2:C100, C2:C100="East", "No Results")
UNIQUE
Returns a list of unique values from a list or range.
| Argument | Description | Required |
|---|---|---|
| array | The range or array from which to return unique values. | ✔ |
| by_col | Determines how to compare values — use FALSE to compare by row (default) or TRUE to compare by column. | X |
| exactly_once | If TRUE, returns only values that appear exactly once in the array. If FALSE or omitted, returns all distinct values. | X |
Example:
=UNIQUE(A2:A50)
This function returns all unique or distinct values as an array from the range A2 through A50.
SORT
Sorts the contents of a range or array based on one or more columns or rows.
| Argument | Description | Required |
|---|---|---|
| array | The range or array to sort. | ✔ |
| sort_index | The column or row number to sort by (default is 1). | X |
| sort_order | Sort order: 1 for ascending (default) or -1 for descending. | X |
| by_col | Determines the sort orientation — FALSE to sort by rows (default) or TRUE to sort by columns. | X |
Example:
=SORT(A2:B20, 2, -1)
This function returns an array of cells A2 through B20 sorted on Column B (2) in descending order (-1).
SUMIFS
Adds up all values in a range that meet one or more specified criteria.
| Argument | Description | Required |
|---|---|---|
| sum_range | The range containing values to sum. | ✔ |
| criteria_range1 | The first range to evaluate. | ✔ |
| criteria1 | The condition to apply to the first range. | ✔ |
| [criteria_range2, criteria2], ... | Additional ranges and criteria to evaluate. | X |
Example:
=SUMIFS(C2:C100, A2:A100, "North", B2:B100, ">500")
This function summarizes everything in cells C2 through C100 that have "North" in column A and a value greater than 500 in column B.
AVERAGEIFS
Calculates the average of cells that meet one or more specified criteria.
| Argument | Description | Required |
|---|---|---|
| average_range | The range containing values to average. | ✔ |
| criteria_range1 | The first range to evaluate. | ✔ |
| criteria1 | The condition to apply to the first range. | ✔ |
| [criteria_range2, criteria2], ... | Additional ranges and criteria to evaluate. | X |
Example:
=AVERAGEIFS(C2:C100, A2:A100, "South", B2:B100, ">1000")
This function averages all values in cells C2 through C100 that have "South" in column A and a value greater than 1000 in column B.
COUNTIFS
Counts the number of cells across one or more ranges that meet all specified criteria.
| Argument | Description | Required |
|---|---|---|
| criteria_range1 | The first range to evaluate. | ✔ |
| criteria1 | The condition to apply to the first range. | ✔ |
| [criteria_range2, criteria2], ... | Additional ranges and criteria to evaluate. | X |
Example:
=COUNTIFS(A2:A100, "East", B2:B100, ">500")
This function counts all values in rows in the array of rows 2 through 100 that have "East" in column A and a value greater than 500 in column B.
IFS
Checks whether one or more conditions are met and returns a value corresponding to the first condition that evaluates to TRUE.
| Argument | Description | Required |
|---|---|---|
| logical_test1 | The first condition to evaluate. | ✔ |
| value_if_true1 | The value to return if the first condition is TRUE. | ✔ |
| [logical_test2, value_if_true2], ... | Additional conditions and corresponding return values. | X |
Example:
=IFS(A2>90, "A", A2>80, "B", A2>70, "C", TRUE, "F")
This function returns a value of "A" if A2 is greater than 90, "B" if A2 is greater than 80, "C" if A2 is greater than 70 and "F" if none of those conditions are true. The function will stop evaluating as soon as a condition is true.
SWITCH
Evaluates a single value against a list of possibilities and returns the corresponding result for the first match found.
| Argument | Description | Required |
|---|---|---|
| expression | The value or expression to evaluate. | ✔ |
| value1 | The first possible match to compare against the expression. | ✔ |
| result1 | The result to return if the first match is found. | ✔ |
| [value2, result2], ... | Additional values and corresponding results. | X |
| [default] | The value to return if no matches are found. | X |
Example:
=SWITCH(A2, "Red", "Stop", "Yellow", "Caution", "Green", "Go", "Unknown")
This function will look at cell A2 and return "Stop" if the value is "Red", "Caution" if the value is "Yellow", "Go" if the value is "Green" and "Unknown" otherwise.
IFERROR
Handles formula errors by returning a specified value if an error occurs, otherwise returns the result of the formula.
| Argument | Description | Required |
|---|---|---|
| value | The formula or expression to evaluate. | ✔ |
| value_if_error | The value to return if the formula results in an error. | ✔ |
Example:
=IFERROR(A2/B2, "Error: Division by Zero")
This function will return the message "Error: Division by Zero" in the cell if the function A2/B2 results in an Excel error.
INDIRECT
Returns the reference specified by a text string, allowing dynamic cell references within formulas.
| Argument | Description | Required |
|---|---|---|
| ref_text | A text string representing a cell or range reference. | ✔ |
| a1 | Logical value: TRUE for A1-style reference (default) or FALSE for R1C1-style reference. | X |
Example:
=INDIRECT("A" & 5)
This function will construct the value of "A5" from its contents and return the value from that cell.
XMATCH
Searches for a specified item in an array or range of cells and returns the item’s relative position.
| Argument | Description | Required |
|---|---|---|
| lookup_value | The value to search for. | ✔ |
| lookup_array | The range or array to search within. | ✔ |
| match_mode | Defines the type of match: 0 for exact (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard match. | X |
| search_mode | Defines the search direction: 1 for first-to-last (default) or -1 for last-to-first. | X |
Example:
=XMATCH("Widget", A2:A10)
This function will returns the relative position (ex: A3) from the range of A2 through A10 once it sees the value "Widget".
OFFSET
Returns a reference to a range that is a specified number of rows and columns away from a given cell or range. The returned reference can be a single cell or multiple cells.
| Argument | Description | Required |
|---|---|---|
| reference | The starting point from which to base the offset. | ✔ |
| rows | The number of rows to move up (negative) or down (positive) from the starting reference. | ✔ |
| cols | The number of columns to move left (negative) or right (positive) from the starting reference. | ✔ |
| [height] | The number of rows to include in the returned range. | X |
| [width] | The number of columns to include in the returned range. | X |
Example:
=SUM(OFFSET(A1, 1, 0, 5, 1))
This function will return the sum of 5 rows (height) starting 1 row down (rows) from cell A1 (reference).
LARGE
Returns the k-th largest value in a dataset, allowing you to identify top results such as highest sales or scores.
| Argument | Description | Required |
|---|---|---|
| array | The range or array of numeric values to evaluate. | ✔ |
| k | The position (rank) in the list of the value to return — 1 for the largest, 2 for the second largest, and so on. | ✔ |
Example:
=LARGE(A2:A20, 3)
This function is identifying the third largest value in the range A2 through A20.
SMALL
Returns the k-th smallest value in a dataset, allowing you to identify the lowest results such as minimum costs or smallest quantities.
| Argument | Description | Required |
|---|---|---|
| array | The range or array of numeric values to evaluate. | ✔ |
| k | The position (rank) in the list of the value to return — 1 for the smallest, 2 for the second smallest, and so on. | ✔ |
Example:
=SMALL(A2:A20, 2)
This function is identifying the second smallest value in the range A2 through A20.
CONCAT
Combines text from multiple ranges or strings into a single continuous text value.
| Argument | Description | Required |
|---|---|---|
| text1 | The first text item or range to join. | ✔ |
| [text2], ... | Additional text items or ranges to join. | X |
Example:
=CONCAT(A2, " ", B2)
This function is concatenating cells A2 and B2 with a space in between.
TEXTJOIN
Combines text from multiple cells, ranges, or strings into a single text string, using a specified delimiter between each item.
| Argument | Description | Required |
|---|---|---|
| delimiter | The character(s) to insert between each text value (e.g., comma, space). | ✔ |
| ignore_empty | TRUE to skip empty cells, or FALSE to include them. | ✔ |
| text1 | The first text item or range to join. | ✔ |
| [text2], ... | Additional text items or ranges to join. | X |
Example:
=TEXTJOIN(", ", TRUE, A2:A10)
This function is using the delimiter of comma space (", ") to concatenate all cell values from cells A2 to A10 into one cell. Because ignore_empty is set to true, any empty value in that range will be skipped over and will not result in duplicated delimiters.