BYCOL
Applies a LAMBDA to each column and returns an array of the results.
LogicalDynamic Arrays & LAMBDALogic & ConditionsModern Excel Functions
Dynamic Arrays & LAMBDA
Dynamic Arrays & LAMBDA unlock a modern way of working in Excel: formulas that spill results, scale to entire ranges, and encapsulate logic cleanly.
This collection groups Excel functions that are commonly used for dynamic arrays & lambda 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
- Prefer spill-friendly formulas that work on ranges instead of single cells.
- Use LET to name intermediate steps—your formulas become dramatically easier to read.
Excel functions in this collection (26)
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
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
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
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
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
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
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
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
UNIQUE
Returns a list of unique values in a list or range.
Lookup & ReferenceDynamic Arrays & LAMBDALookup & Matching EssentialsModern Excel Functions
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