ARRAYTOTEXT
Returns an array of text values from any specified range.
TextModern Excel FunctionsText Processing
Modern Excel Functions
Modern Excel Functions are the newer additions that make formulas clearer, faster, and more maintainable—especially when combined with dynamic arrays.
This collection groups Excel functions that are commonly used for modern excel functions workflows. Instead of browsing hundreds of formulas, you can focus on the exact tools that solve this type of task.
Each function page includes a short explanation and practical usage context. Use these functions as standalone tools or combine them to build more advanced, maintainable formulas.
If you frequently work with reports, dashboards, or recurring templates, bookmarking the key functions from this collection will speed up your day-to-day work.
Tips
- Start with the most common functions in this collection and expand as your problems get more complex.
- Combine these functions with structured tables to keep formulas readable and resilient.
Excel functions in this collection (49)
BYCOL
Applies a LAMBDA to each column and returns an array of the results.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
BYROW
Applies a LAMBDA to each row and returns an array of the results.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
CHOOSECOLS
Returns the specified columns from an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
CHOOSEROWS
Returns the specified rows from an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
CONCAT
Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
TextModern Excel FunctionsMost UsedText Processing
DETECTLANGUAGE
Identifies the language of a specified text.
TextModern Excel FunctionsText Processing
DROP
Excludes a specified number of rows or columns from the start or end of an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
EXPAND
Expands or pads an array to specified row and column dimensions.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
FILTER
Filters a range of data based on criteria you define.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
GROUPBY
Helps a user group, aggregate, sort, and filter data based on the fields you specify.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
HSTACK
Appends arrays horizontally and in sequence to return a larger array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
IFS
Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
LogicalLogic & ConditionsModern Excel FunctionsMost Used
IMAGE
Returns an image from a given source.
Lookup & ReferenceLookup & Matching EssentialsModern Excel Functions
ISOMITTED
Checks whether the value in a LAMBDA is missing and returns TRUE or FALSE.
InformationInformation & AuditingModern Excel Functions
LAMBDA
Create custom, reusable and call them by a friendly name.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
LET
Assigns names to calculation results.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
MAKEARRAY
Returns a calculated array of a specified row and column size, by applying a LAMBDA.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
MAP
Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
MAXIFS
Returns the maximum value among cells specified by a given set of conditions or criteria.
StatisticalModern Excel FunctionsStatistics & Analysis
MINIFS
Returns the minimum value among cells specified by a given set of conditions or criteria.
StatisticalModern Excel FunctionsStatistics & Analysis
PERCENTOF
Sums the values in the subset and divides it by all the values.
Math & TrigonometryMath BasicsModern Excel Functions
PIVOTBY
Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
RANDARRAY
Returns an array of random numbers between 0 and 1. However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.
Math & TrigonometryMath BasicsModern Excel Functions
REDUCE
Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
REGEXEXTRACT
Extracts strings within the provided text that matches the pattern.
TextModern Excel FunctionsText Processing
REGEXREPLACE
Replaces strings within the provided text that matches the pattern with replacement.
TextModern Excel FunctionsText Processing
REGEXTEST
Determines whether any part of text matches the pattern.
TextModern Excel FunctionsText Processing
SCAN
Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
SEQUENCE
Generates a list of sequential numbers in an array, such as 1, 2, 3, 4.
Math & TrigonometryDynamic Arrays & LAMBDAMath BasicsModern Excel Functions
SORT
Sorts the contents of a range or array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
SORTBY
Sorts the contents of a range or array based on the values in a corresponding range or array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
SWITCH
Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
LogicalLogic & ConditionsModern Excel Functions
TAKE
Returns a specified number of contiguous rows or columns from the start or end of an array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
TEXTAFTER
Returns text that occurs after given character or string.
TextModern Excel FunctionsText CleaningText Processing
TEXTBEFORE
Returns text that occurs before a given character or string.
TextModern Excel FunctionsText CleaningText Processing
TEXTJOIN
Combines the text from multiple ranges and/or strings.
TextModern Excel FunctionsMost UsedText Cleaning
TEXTSPLIT
Splits text strings by using column and row delimiters.
TextModern Excel FunctionsText CleaningText Processing
TOCOL
Returns the array in a single column.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
TOROW
Returns the array in a single row.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
TRANSLATE
Translates a text from one language to another.
TextModern Excel FunctionsText Processing
TRIMRANGE
Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns.
Lookup & ReferenceLookup & Matching EssentialsModern Excel Functions
UNIQUE
Returns a list of unique values in a list or range.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
VALUETOTEXT
Returns text from any specified value.
TextModern Excel FunctionsText Processing
VSTACK
Appends arrays vertically and in sequence to return a larger array.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
WRAPCOLS
Wraps the provided row or column of values by columns after a specified number of elements.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
WRAPROWS
Wraps the provided row or column of values by rows after a specified number of elements.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
XLOOKUP
Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
Lookup & ReferenceBeginner EssentialsLookup & Matching EssentialsModern Excel Functions
XMATCH
Returns the relative position of an item in an array or range of cells.
Lookup & ReferenceLookup & Matching EssentialsModern Excel FunctionsMost Used