A collection of named Excel/Google Sheets formulas using LET and LAMBDA functions.
- BLANK - Returns a truly blank cell value. This is useful in LAMBDA functions and MAP operations where you want to return blank cells rather than empty strings or other values. Uses the omitted argument syntax in IF (IF(,,)) to generate a blank value.
- BLANKTOEMPTY - Converts blank cells to empty strings. Accepts either a single value or a range. When given a range, automatically applies the conversion to all cells using MAP. Useful for ensuring consistent handling of empty values where blank cells need to be represented as empty strings ("").
- BYROW_COMPLETE_ONLY - Applies a row operation only to complete rows (rows with no blank cells). Incomplete rows return a specified fallback value. Useful for processing data while gracefully handling missing values.
- BYROW_NONEMPTY_ONLY - Applies a row operation only to non-empty rows (rows with at least one non-blank cell). Completely empty rows return a specified fallback value. Useful for filtering out empty rows during processing.
- CELLREF - Converts a cell or range reference to its A1 notation string representation. Returns the relative reference (e.g., "N6") for the given cell. This is useful for generating dynamic cell references, creating hyperlinks, or building formulas programmatically.
- DATAROWS - Extracts all data rows (excluding header rows) from a data range. This is useful for separating data from headers, especially when performing operations that should only apply to data rows.
- DENSIFY - Removes empty or incomplete rows and columns from sparse data. Use mode to control which dimensions to process and how strict to be. Supports data validation (remove incomplete records) and whitespace handling (treat spaces as empty). Preserves all error types (#N/A, #DIV/0!, etc.) - rows/columns containing errors are kept and error values remain intact. Returns BLANK() when all rows/columns are filtered out.
- DENSIFYROWS - Removes rows that are entirely blank from sparse data. This is a convenience wrapper around DENSIFY that specifically targets row operations with the "rows" mode.
- DROP - Drops a specified number of rows and columns from a range (both dimensions). This is the Excel-compatible DROP function. Positive row/col values drop from start, negative from end. Both parameters are required.
- DROPCOLS - Drops the first or last N columns from a range. Positive num_cols drops from the start, negative num_cols drops from the end. Uses the transpose pattern to work with columns.
- DROPROWS - Drops the first or last N rows from a range. Positive num_rows drops from the start, negative num_rows drops from the end. Cannot drop all rows.
- EMPTYTOBLANK - Converts empty strings to blank cells. Accepts either a single value or a range. When given a range, automatically applies the conversion to all cells using MAP. Useful for cleaning data where empty strings should be represented as true blanks.
- ERROR - Displays a custom error message as an #N/A error with a configurable tooltip. Uses an empty array trick to ensure the error always triggers, regardless of cell contents. Useful for validation and user-friendly error reporting.
- ERRORFILTER - Filters rows and columns based on error status. Use mode to control which dimensions to process and which error conditions to filter for. Useful for data validation, debugging, and cleaning error-prone data.
- ERRORSTO - Replaces error cells with a specified value while preserving non-error cells. Accepts either a single value or a range. When given a range, automatically applies the replacement to all cells using MAP. Useful for error handling and data cleaning where errors need to be replaced with a default value.
- EXPAND - Expands or pads an array to specified dimensions. If target dimensions exceed current array size, fills new cells with the pad_with value. If target dimensions are less than current size, dimensions remain unchanged (no shrinking).
- GROUPBY - Groups data by one or more columns and applies custom aggregation logic via LAMBDA functions, implementing SQL-like GROUP BY functionality. Does not handle headers - provide data without header row.
- HEADERS - Extracts the header row (first row) from a data range. This is useful for separating headers from data, especially when working with structured data.
- HSTACKBLANK - Stacks two arrays horizontally, padding shorter arrays with blank cells to match dimensions. Convenience wrapper for HSTACKFILL using BLANK().
- HSTACKFILL - Stacks two arrays horizontally, padding shorter arrays with a specified fill value to match dimensions. Prevents #N/A errors from mismatched heights.
- ISBLANKLIKE - Checks if a cell is either truly blank (ISBLANK) or an empty string (""). This is useful for identifying cells that appear empty but may contain empty strings from formulas or data imports. Returns TRUE if the cell is blank-like, FALSE otherwise.
- NONERRORSTO - Replaces non-error cells with a specified value while preserving error cells. Accepts either a single value or a range. When given a range, automatically applies the replacement to all cells using MAP. Useful for masking valid data while keeping errors visible for debugging or validation purposes.
- OMITCOLS - Excludes specified columns from a range. This is the negation of CHOOSECOLS - instead of selecting columns to keep, it selects columns to remove.
- OMITROWS - Excludes specified rows from a range. This is the negation of CHOOSEROWS - instead of selecting rows to keep, it selects rows to remove.
- PERCENTOF - Calculates the percentage contribution of a subset relative to a total. Returns a decimal value where 0.25 represents 25%. Fails with #DIV/0! if the total sum is zero.
- RANGEREF - Converts a range reference to its A1 notation string representation. Returns the range in A1 notation (e.g., "A1:B10") for the given range. For single-cell ranges, returns just the cell reference (e.g., "A1" instead of "A1:A1"). This is useful for generating dynamic range references, creating hyperlinks, or building formulas programmatically.
- SINGLE - Returns a single value from an array at the specified row and column position. This function extracts a single cell from a range using 1-based indexing. It is useful for retrieving a specific value from a larger array or range.
- SUBSTITUTEMULTI - Applies multiple SUBSTITUTE operations sequentially using a two-column mapping range. Substitutions are applied in row order, with later substitutions operating on the results of earlier ones. This enables powerful multi-stage text transformations.
- TAKE - Takes a rectangular region from a range (both rows and columns). This is the Excel-compatible TAKE function. Positive row/col values take from start, negative from end. Both parameters are required.
- TAKECOLS - Takes the first or last N columns from a range. Positive num_cols takes from the start, negative num_cols takes from the end. Uses the transpose pattern to work with columns.
- TAKEROWS - Takes the first or last N rows from a range. Positive num_rows takes from the start, negative num_rows takes from the end. Useful for extracting top or bottom rows from a dataset.
- TEXTAFTER - Returns text that appears after a specified delimiter. Supports forward/backward search, case-sensitive/insensitive matching, and customizable error handling. Replicates Excel's TEXTAFTER function for Google Sheets.
- TEXTBEFORE - Returns text that appears before a specified delimiter. Supports forward/backward search, case-sensitive/insensitive matching, and customizable error handling. Replicates Excel's TEXTBEFORE function for Google Sheets.
- TEXTSPLIT - Splits text into an array using a delimiter (1D version - columns only). This is a simplified version of Excel's TEXTSPLIT function that splits text into a single row with multiple columns. Wraps Google Sheets' SPLIT function with consistent naming. Future versions may support 2D splitting with row delimiters.
- TRIMRANGE - Removes empty rows and columns from the outer edges of a range, similar to how TRIM removes leading and trailing spaces from text. Only affects the boundaries (top, bottom, left, right) - preserves empty rows/columns in the middle of data. A row/column is considered empty if all cells are blank or empty strings. Returns BLANK() if the entire range is empty after trimming.
- UNPIVOT - Transforms wide-format data into long-format (tidy data) by unpivoting specified columns into attribute-value pairs.
- VSTACKBLANK - Stacks two arrays vertically, padding narrower arrays with blank cells to match dimensions. Convenience wrapper for VSTACKFILL using BLANK().
- VSTACKFILL - Stacks two arrays vertically, padding narrower arrays with a specified fill value to match dimensions. Prevents #N/A errors from mismatched widths.
- WRAP - Wraps content with opening and closing delimiters. Useful for generating HTML/XML tags, brackets, or any paired delimiter pattern around text or cell values.
BLANK
Description
v1.1.0 Returns a truly blank cell value. This is useful in LAMBDA functions and MAP operations where you want to return blank cells rather than empty strings or other values. Uses the omitted argument syntax in IF (IF(,,)) to generate a blank value.
Formula
IF(,,)
BLANKTOEMPTY
Description
v1.1.0 Converts blank cells to empty strings. Accepts either a single value or a range. When given a range, automatically applies the conversion to all cells using MAP. Useful for ensuring consistent handling of empty values where blank cells need to be represented as empty strings ("").
Parameters
1. input
Formula
MAP(input, LAMBDA(v, IF(ISBLANK(v), "", v)))
Description:
Single value or range to convert (blank cells become empty strings)
Example:
A1:B10
BYROW_COMPLETE_ONLY
Description
v1.0.1 Applies a row operation only to complete rows (rows with no blank cells). Incomplete rows return a specified fallback value. Useful for processing data while gracefully handling missing values.
Parameters
1. input_range
2. value_if_incomplete
3. row_operation
Formula
BYROW(input_range,
LAMBDA(row,
IF(
COUNTBLANK(row) > 0,
value_if_incomplete,
row_operation(row)
)
)
)
Description:
Range of data to process row by row
Example:
A1:D10
Description:
Value to return for rows that contain any blank cells
Example:
BLANK()
Description:
LAMBDA function to apply to each complete row. Receives a single row as input.
Example:
LAMBDA(row, SUM(row))
BYROW_NONEMPTY_ONLY
Description
v1.0.1 Applies a row operation only to non-empty rows (rows with at least one non-blank cell). Completely empty rows return a specified fallback value. Useful for filtering out empty rows during processing.
Parameters
1. input_range
2. value_if_empty
3. row_operation
Formula
BYROW(input_range,
LAMBDA(row,
IF(
COUNTBLANK(row) = COLUMNS(row),
value_if_empty,
row_operation(row)
)
)
)
Description:
Range of data to process row by row
Example:
A1:D10
Description:
Value to return for rows that are completely empty (all cells blank)
Example:
BLANK()
Description:
LAMBDA function to apply to each non-empty row. Receives a single row as input.
Example:
LAMBDA(row, TEXTJOIN(", ", TRUE, row))
CELLREF
Description
v1.0.0 Converts a cell or range reference to its A1 notation string representation. Returns the relative reference (e.g., "N6") for the given cell. This is useful for generating dynamic cell references, creating hyperlinks, or building formulas programmatically.
Parameters
1. cell
Formula
ADDRESS(ROW(cell), COLUMN(cell), 4)
Description:
The cell or range to convert to A1 notation
Example:
N6
DATAROWS
Description
v1.0.0 Extracts all data rows (excluding header rows) from a data range. This is useful for separating data from headers, especially when performing operations that should only apply to data rows.
Parameters
1. range
2. num_header_rows
Formula
=LET(
header_rows, IF(OR(num_header_rows = "", ISBLANK(num_header_rows)), 1, num_header_rows),
(LET(
total_rows, ROWS(range),
_validate_zero, IF(header_rows = 0,
(XLOOKUP("header_rows cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(header_rows > 0,
header_rows,
total_rows + header_rows + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count >= total_rows),
(XLOOKUP("header_rows has absolute value (" & ABS(header_rows) & ") exceeds available rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
rows_to_keep, total_rows - normalized_count,
IF(rows_to_keep = 1,
CHOOSEROWS(range, normalized_count + 1),
CHOOSEROWS(range, SEQUENCE(rows_to_keep, 1, normalized_count + 1))
)
))
)
Description:
The data range including headers
Example:
A1:Z100
Description:
Number of header rows to skip (default: 1). Use this when you have multi-row headers.
Example:
1
DENSIFY
Description
v1.0.6 Removes empty or incomplete rows and columns from sparse data. Use mode to control which dimensions to process and how strict to be. Supports data validation (remove incomplete records) and whitespace handling (treat spaces as empty). Preserves all error types (#N/A, #DIV/0!, etc.) - rows/columns containing errors are kept and error values remain intact. Returns BLANK() when all rows/columns are filtered out.
Parameters
1. range
2. mode
Formula
=LET(
actual_mode, IF(OR(mode="", mode=0), "both", LOWER(TRIM(mode))),
mode_parts, SPLIT(actual_mode, "-"),
dimension, INDEX(mode_parts, 1),
has_any, IFERROR(FIND("any", actual_mode) > 0, FALSE),
has_strict, IFERROR(FIND("strict", actual_mode) > 0, FALSE),
valid_dimension, OR(dimension = "both", dimension = "rows", dimension = "cols"),
IF(NOT(valid_dimension),
NA(),
LET(
should_remove_rows, OR(dimension = "both", dimension = "rows"),
should_remove_cols, OR(dimension = "both", dimension = "cols"),
rows_filtered, IF(should_remove_rows,
LET(
threshold, IF(has_any, COLUMNS(range), 1),
result, IF(has_strict,
FILTER(range, BYROW(range, LAMBDA(r, SUMPRODUCT((IFERROR(LEN(TRIM(r)) > 0, TRUE)) * 1) >= threshold))),
FILTER(range, BYROW(range, LAMBDA(r, COUNTA(r) >= threshold)))
),
IF(ISNA(ROWS(result)), (IF(,,)), result)
),
range
),
final, IF(should_remove_cols,
LET(
transposed, TRANSPOSE(rows_filtered),
threshold, IF(has_any, ROWS(rows_filtered), 1),
result, IF(has_strict,
FILTER(transposed, BYROW(transposed, LAMBDA(c, SUMPRODUCT((IFERROR(LEN(TRIM(c)) > 0, TRUE)) * 1) >= threshold))),
FILTER(transposed, BYROW(transposed, LAMBDA(c, COUNTA(c) >= threshold)))
),
IF(ISNA(ROWS(result)), (IF(,,)), TRANSPOSE(result))
),
rows_filtered
),
final
)
)
)
Description:
The data range to densify. Example - A1:Z100
Example:
A1:Z100
Description:
Controls dimension and strictness. Basic modes - both (default), rows, cols. Add -any to remove incomplete rows/cols. Add -strict to treat whitespace as empty. Combine both - rows-any-strict. Case-insensitive.
Example:
rows-any
DENSIFYROWS
Description
v2.0.0 Removes rows that are entirely blank from sparse data. This is a convenience wrapper around DENSIFY that specifically targets row operations with the "rows" mode.
Parameters
1. range
Formula
=LET(
actual_mode, IF(OR("rows"="", "rows"=0), "both", LOWER(TRIM("rows"))),
mode_parts, SPLIT(actual_mode, "-"),
dimension, INDEX(mode_parts, 1),
has_any, IFERROR(FIND("any", actual_mode) > 0, FALSE),
has_strict, IFERROR(FIND("strict", actual_mode) > 0, FALSE),
valid_dimension, OR(dimension = "both", dimension = "rows", dimension = "cols"),
IF(NOT(valid_dimension),
NA(),
LET(
should_remove_rows, OR(dimension = "both", dimension = "rows"),
should_remove_cols, OR(dimension = "both", dimension = "cols"),
rows_filtered, IF(should_remove_rows,
LET(
threshold, IF(has_any, COLUMNS(range), 1),
result, IF(has_strict,
FILTER(range, BYROW(range, LAMBDA(r, SUMPRODUCT((IFERROR(LEN(TRIM(r)) > 0, TRUE)) * 1) >= threshold))),
FILTER(range, BYROW(range, LAMBDA(r, COUNTA(r) >= threshold)))
),
IF(ISNA(ROWS(result)), (IF(,,)), result)
),
range
),
final, IF(should_remove_cols,
LET(
transposed, TRANSPOSE(rows_filtered),
threshold, IF(has_any, ROWS(rows_filtered), 1),
result, IF(has_strict,
FILTER(transposed, BYROW(transposed, LAMBDA(c, SUMPRODUCT((IFERROR(LEN(TRIM(c)) > 0, TRUE)) * 1) >= threshold))),
FILTER(transposed, BYROW(transposed, LAMBDA(c, COUNTA(c) >= threshold)))
),
IF(ISNA(ROWS(result)), (IF(,,)), TRANSPOSE(result))
),
rows_filtered
),
final
)
)
)
Description:
The data range to densify (remove empty rows)
Example:
A1:Z100
DROP
Description
v1.0.0 Drops a specified number of rows and columns from a range (both dimensions). This is the Excel-compatible DROP function. Positive row/col values drop from start, negative from end. Both parameters are required.
Parameters
1. range
2. rows
3. cols
Formula
=LET(
rows_dropped, (LET(
total_rows, ROWS(range),
_validate_zero, IF(rows = 0,
(XLOOKUP("rows cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(rows > 0,
rows,
total_rows + rows + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count >= total_rows),
(XLOOKUP("rows has absolute value (" & ABS(rows) & ") exceeds available rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
rows_to_keep, total_rows - normalized_count,
IF(rows_to_keep = 1,
CHOOSEROWS(range, normalized_count + 1),
CHOOSEROWS(range, SEQUENCE(rows_to_keep, 1, normalized_count + 1))
)
)),
(LET(
transposed, TRANSPOSE(rows_dropped),
result, (LET(
total_rows, ROWS(transposed),
_validate_zero, IF(cols = 0,
(XLOOKUP("cols cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(cols > 0,
cols,
total_rows + cols + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count >= total_rows),
(XLOOKUP("cols has absolute value (" & ABS(cols) & ") exceeds available rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
rows_to_keep, total_rows - normalized_count,
IF(rows_to_keep = 1,
CHOOSEROWS(transposed, normalized_count + 1),
CHOOSEROWS(transposed, SEQUENCE(rows_to_keep, 1, normalized_count + 1))
)
)),
TRANSPOSE(result)
))
)
Description:
The input data range
Example:
A1:Z100
Description:
Number of rows to drop. Positive values drop from start, negative from end. Cannot be 0 or have absolute value exceeding or equaling total rows.
Example:
1
Description:
Number of columns to drop. Positive values drop from start, negative from end. Cannot be 0 or have absolute value exceeding or equaling total columns.
DROPCOLS
Description
v1.0.0 Drops the first or last N columns from a range. Positive num_cols drops from the start, negative num_cols drops from the end. Uses the transpose pattern to work with columns.
Parameters
1. range
2. num_cols
Formula
=LET(
transposed, TRANSPOSE(range),
result, (LET(
total_rows, ROWS(transposed),
_validate_zero, IF(num_cols = 0,
(XLOOKUP("num_cols cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(num_cols > 0,
num_cols,
total_rows + num_cols + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count >= total_rows),
(XLOOKUP("num_cols has absolute value (" & ABS(num_cols) & ") exceeds available rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
rows_to_keep, total_rows - normalized_count,
IF(rows_to_keep = 1,
CHOOSEROWS(transposed, normalized_count + 1),
CHOOSEROWS(transposed, SEQUENCE(rows_to_keep, 1, normalized_count + 1))
)
)),
TRANSPOSE(result)
)
Description:
The input data range
Example:
A1:Z100
Description:
Number of columns to drop. Positive values drop from start, negative from end. Cannot be 0 or have absolute value exceeding or equaling total columns.
Example:
5
DROPROWS
Description
v1.0.0 Drops the first or last N rows from a range. Positive num_rows drops from the start, negative num_rows drops from the end. Cannot drop all rows.
Parameters
1. range
2. num_rows
Formula
=LET(
total_rows, ROWS(range),
_validate_zero, IF(num_rows = 0,
(XLOOKUP("num_rows cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(num_rows > 0,
num_rows,
total_rows + num_rows + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count >= total_rows),
(XLOOKUP("num_rows has absolute value (" & ABS(num_rows) & ") exceeds available rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
rows_to_keep, total_rows - normalized_count,
IF(rows_to_keep = 1,
CHOOSEROWS(range, normalized_count + 1),
CHOOSEROWS(range, SEQUENCE(rows_to_keep, 1, normalized_count + 1))
)
)
Description:
The input data range
Example:
A1:Z100
Description:
Number of rows to drop. Positive values drop from start, negative from end. Cannot be 0 or have absolute value exceeding or equaling total rows.
Example:
5
EMPTYTOBLANK
Description
v1.1.0 Converts empty strings to blank cells. Accepts either a single value or a range. When given a range, automatically applies the conversion to all cells using MAP. Useful for cleaning data where empty strings should be represented as true blanks.
Parameters
1. input
Formula
MAP(input, LAMBDA(v, IF(v = "", (IF(,,)), v)))
Description:
Single value or range to convert (empty strings become blank cells)
Example:
A1:B10
ERROR
Description
v1.0.0 Displays a custom error message as an #N/A error with a configurable tooltip. Uses an empty array trick to ensure the error always triggers, regardless of cell contents. Useful for validation and user-friendly error reporting.
Parameters
1. message
Formula
XLOOKUP(message, IF(FALSE, {1}), IF(FALSE, {1}))
Description:
The error message to display in the tooltip
Example:
"Value must be between 1 and 100"
ERRORFILTER
Description
v1.0.0 Filters rows and columns based on error status. Use mode to control which dimensions to process and which error conditions to filter for. Useful for data validation, debugging, and cleaning error-prone data.
Parameters
1. range
2. mode
Formula
=LET(
actual_mode, IF(OR(mode="", mode=0), "both", LOWER(TRIM(mode))),
mode_parts, SPLIT(actual_mode, "-"),
dimension, INDEX(mode_parts, 1),
has_any, IFERROR(FIND("any", actual_mode) > 0, FALSE),
has_all, IFERROR(FIND("all", actual_mode) > 0, FALSE),
valid_dimension, OR(dimension = "both", dimension = "rows", dimension = "cols"),
IF(NOT(valid_dimension),
NA(),
LET(
should_filter_rows, OR(dimension = "both", dimension = "rows"),
should_filter_cols, OR(dimension = "both", dimension = "cols"),
rows_filtered, IF(should_filter_rows,
LET(
num_cols, COLUMNS(range),
IF(has_any,
IFNA(FILTER(range, BYROW(range, LAMBDA(r, SUMPRODUCT((ISERROR(r)) * 1) > 0))), (IF(,,))),
IF(has_all,
IFNA(FILTER(range, BYROW(range, LAMBDA(r, SUMPRODUCT((ISERROR(r)) * 1) = num_cols))), (IF(,,))),
IFNA(FILTER(range, BYROW(range, LAMBDA(r, SUMPRODUCT((ISERROR(r)) * 1) = 0))), (IF(,,)))
)
)
),
range
),
final, IF(should_filter_cols,
LET(
transposed, TRANSPOSE(rows_filtered),
num_rows, ROWS(rows_filtered),
TRANSPOSE(
IF(has_any,
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, SUMPRODUCT((ISERROR(c)) * 1) > 0))), (IF(,,))),
IF(has_all,
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, SUMPRODUCT((ISERROR(c)) * 1) = num_rows))), (IF(,,))),
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, SUMPRODUCT((ISERROR(c)) * 1) = 0))), (IF(,,)))
)
)
)
),
rows_filtered
),
final
)
)
)
Description:
The data range to filter. Example - A1:Z100
Example:
A1:Z100
Description:
Controls dimension and error filtering. Basic modes - both (default), rows, cols. Add -any to keep rows/cols with at least one error. Add -all to keep rows/cols where all cells are errors. Default keeps rows/cols with no errors. Case-insensitive. Examples: "rows", "cols-any", "both-all"
Example:
rows-any
ERRORSTO
Description
v1.0.0 Replaces error cells with a specified value while preserving non-error cells. Accepts either a single value or a range. When given a range, automatically applies the replacement to all cells using MAP. Useful for error handling and data cleaning where errors need to be replaced with a default value.
Parameters
1. input
2. replacement
Formula
MAP(input, LAMBDA(v, IF(ISERROR(v), replacement, v)))
Description:
Single value or range to process (error cells will be replaced)
Example:
A1:B10
Description:
Value to use in place of errors
EXPAND
Description
v1.0.0 Expands or pads an array to specified dimensions. If target dimensions exceed current array size, fills new cells with the pad_with value. If target dimensions are less than current size, dimensions remain unchanged (no shrinking).
Parameters
1. array
2. rows
3. cols
4. pad_with
Formula
LET(
current_rows, ROWS(array),
current_cols, COLUMNS(array),
rows_expanded, IF(rows > current_rows,
VSTACK(array, MAKEARRAY(rows - current_rows, current_cols, LAMBDA(r, c, pad_with))),
array),
rows_after_expansion, ROWS(rows_expanded),
IF(cols > current_cols,
HSTACK(rows_expanded, MAKEARRAY(rows_after_expansion, cols - current_cols, LAMBDA(r, c, pad_with))),
rows_expanded)
)
Description:
The input array to expand
Example:
A1:C5
Description:
Target number of rows (must be >= 1). If less than current rows, no change.
Example:
10
Description:
Target number of columns (must be >= 1). If less than current cols, no change.
Example:
5
Description:
Value to use for padding new cells (use BLANK() for empty cells)
Example:
BLANK()
GROUPBY
Description
v1.0.0 Groups data by one or more columns and applies custom aggregation logic via LAMBDA functions, implementing SQL-like GROUP BY functionality. Does not handle headers - provide data without header row.
Parameters
1. data
2. group_cols
3. value_cols
4. agg_lambda
Formula
=LET(
num_rows, ROWS(data),
num_cols, COLUMNS(data),
_validate_dims, IF(OR(num_rows < 1, num_cols < 1),
(XLOOKUP("Data must have at least 1 row and 1 column", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
group_cols_array, IF(ROWS(group_cols) = 1,
IF(COLUMNS(group_cols) = 1, {group_cols}, group_cols),
TRANSPOSE(group_cols)
),
value_cols_array, IF(ROWS(value_cols) = 1,
IF(COLUMNS(value_cols) = 1, {value_cols}, value_cols),
TRANSPOSE(value_cols)
),
_validate_group_cols, IF(
OR(
SUMPRODUCT(--(group_cols_array < 1)) > 0,
SUMPRODUCT(--(group_cols_array > num_cols)) > 0
),
(XLOOKUP("Group column indices must be between 1 and " & num_cols, IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
_validate_value_cols, IF(
OR(
SUMPRODUCT(--(value_cols_array < 1)) > 0,
SUMPRODUCT(--(value_cols_array > num_cols)) > 0
),
(XLOOKUP("Value column indices must be between 1 and " & num_cols, IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
group_data, MAKEARRAY(num_rows, COLUMNS(group_cols_array), LAMBDA(r, c,
INDEX(data, r, INDEX(group_cols_array, 1, c))
)),
unique_groups, UNIQUE(group_data),
num_groups, ROWS(unique_groups),
aggregated, BYROW(unique_groups, LAMBDA(group_row,
LET(
matches, BYROW(group_data, LAMBDA(data_row,
IF(
SUMPRODUCT(--(data_row = group_row)) = COLUMNS(group_row),
TRUE,
FALSE
)
)),
filtered_values, FILTER(
MAKEARRAY(num_rows, COLUMNS(value_cols_array), LAMBDA(r, c,
INDEX(data, r, INDEX(value_cols_array, 1, c))
)),
matches
),
agg_lambda(filtered_values)
)
)),
HSTACK(unique_groups, aggregated)
)
Description:
Input dataset without headers (2D array of values to group and aggregate)
Example:
A2:D100
Description:
Column indices to group by (1-based). Single integer or array of integers.
Example:
{1, 2}
Description:
Column indices to aggregate (1-based). Single integer or array of integers.
Example:
3
Description:
LAMBDA function that receives filtered values and returns aggregation result(s). Input is 2D array (N rows × M columns) for one group. Output should be 1×K array where K = number of aggregation results.
Example:
LAMBDA(v, SUM(v))
HEADERS
Description
v1.0.0 Extracts the header row (first row) from a data range. This is useful for separating headers from data, especially when working with structured data.
Parameters
1. range
2. num_rows
Formula
=LET(
rows, IF(OR(num_rows = "", ISBLANK(num_rows)), 1, num_rows),
(LET(
total_rows, ROWS(range),
_validate_zero, IF(rows = 0,
(XLOOKUP("rows cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(rows > 0,
rows,
total_rows + rows + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count > total_rows),
(XLOOKUP("rows has absolute value (" & ABS(rows) & ") exceeding total rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
IF(normalized_count = 1,
CHOOSEROWS(range, 1),
CHOOSEROWS(range, SEQUENCE(normalized_count))
)
))
)
Description:
The data range including headers
Example:
A1:Z100
Description:
Number of header rows to extract (default: 1). Use this when you have multi-row headers.
Example:
1
HSTACKBLANK
Description
v1.0.0 Stacks two arrays horizontally, padding shorter arrays with blank cells to match dimensions. Convenience wrapper for HSTACKFILL using BLANK().
Parameters
1. array1
2. array2
Formula
HSTACKFILL(array1, array2, (IF(,,)))
Description:
First array to stack
Example:
A1:C10
Description:
Second array to stack
Example:
E1:F5
HSTACKFILL
Description
v1.0.0 Stacks two arrays horizontally, padding shorter arrays with a specified fill value to match dimensions. Prevents #N/A errors from mismatched heights.
Parameters
1. array1
2. array2
3. fill_value
Formula
LET(
rows1, ROWS(array1),
rows2, ROWS(array2),
max_rows, MAX(rows1, rows2),
padded1, IF(rows1 < max_rows,
VSTACK(array1, MAKEARRAY(max_rows - rows1, COLUMNS(array1), LAMBDA(r, c, fill_value))),
array1),
padded2, IF(rows2 < max_rows,
VSTACK(array2, MAKEARRAY(max_rows - rows2, COLUMNS(array2), LAMBDA(r, c, fill_value))),
array2),
HSTACK(padded1, padded2)
)
Description:
First array to stack
Example:
A1:C10
Description:
Second array to stack
Example:
E1:F5
Description:
Value to use for padding cells (use BLANK() for empty cells)
Example:
BLANK()
ISBLANKLIKE
Description
v1.0.0 Checks if a cell is either truly blank (ISBLANK) or an empty string (""). This is useful for identifying cells that appear empty but may contain empty strings from formulas or data imports. Returns TRUE if the cell is blank-like, FALSE otherwise.
Parameters
1. cell
Formula
OR(ISBLANK(cell), cell = "")
Description:
The cell to check for blank-like condition
Example:
A1
NONERRORSTO
Description
v1.0.0 Replaces non-error cells with a specified value while preserving error cells. Accepts either a single value or a range. When given a range, automatically applies the replacement to all cells using MAP. Useful for masking valid data while keeping errors visible for debugging or validation purposes.
Parameters
1. input
2. replacement
Formula
MAP(input, LAMBDA(v, IF(ISERROR(v), v, replacement)))
Description:
Single value or range to process (non-error cells will be replaced)
Example:
A1:B10
Description:
Value to use in place of non-errors
Example:
""
OMITCOLS
Description
v1.0.0 Excludes specified columns from a range. This is the negation of CHOOSECOLS - instead of selecting columns to keep, it selects columns to remove.
Parameters
1. range
2. col_nums
Formula
=LET(
transposed, TRANSPOSE(range),
result, (LET(
total_rows, ROWS(transposed),
rows_to_omit, FLATTEN(col_nums),
normalized_omit, MAKEARRAY(ROWS(rows_to_omit), COLUMNS(rows_to_omit), LAMBDA(r, c,
LET(
idx, INDEX(rows_to_omit, r, c),
IF(idx < 0, total_rows + idx + 1, idx)
)
)),
all_rows, SEQUENCE(total_rows, 1),
rows_to_keep, FILTER(all_rows, ISNA(MATCH(all_rows, FLATTEN(normalized_omit), 0))),
_validate, IF(ROWS(rows_to_keep) = 0,
(XLOOKUP("Cannot omit all rows from transposed", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
CHOOSEROWS(transposed, rows_to_keep)
)),
TRANSPOSE(result)
)
Description:
The input data range
Example:
A1:Z100
Description:
Column numbers to exclude (1-based indices). Can be a single number, an array of numbers, or a sequence. Negative numbers count from the end (-1 is the last column, -2 is second to last, etc).
Example:
{2, 5, 7}
OMITROWS
Description
v1.0.0 Excludes specified rows from a range. This is the negation of CHOOSEROWS - instead of selecting rows to keep, it selects rows to remove.
Parameters
1. range
2. row_nums
Formula
=LET(
total_rows, ROWS(range),
rows_to_omit, FLATTEN(row_nums),
normalized_omit, MAKEARRAY(ROWS(rows_to_omit), COLUMNS(rows_to_omit), LAMBDA(r, c,
LET(
idx, INDEX(rows_to_omit, r, c),
IF(idx < 0, total_rows + idx + 1, idx)
)
)),
all_rows, SEQUENCE(total_rows, 1),
rows_to_keep, FILTER(all_rows, ISNA(MATCH(all_rows, FLATTEN(normalized_omit), 0))),
_validate, IF(ROWS(rows_to_keep) = 0,
(XLOOKUP("Cannot omit all rows from range", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
CHOOSEROWS(range, rows_to_keep)
)
Description:
The input data range
Example:
A1:Z100
Description:
Row numbers to exclude (1-based indices). Can be a single number, an array of numbers, or a sequence. Negative numbers count from the end (-1 is the last row, -2 is second to last, etc).
Example:
{1, 5, 10}
PERCENTOF
Description
v1.0.0 Calculates the percentage contribution of a subset relative to a total. Returns a decimal value where 0.25 represents 25%. Fails with #DIV/0! if the total sum is zero.
Parameters
1. data_subset
2. data_all
Formula
LET(
subset_sum, SUM(data_subset),
total_sum, SUM(data_all),
subset_sum / total_sum
)
Description:
Range or value representing the part of the total to calculate percentage for
Example:
A1:A10
Description:
Range or value representing the complete total
Example:
A1:A100
RANGEREF
Description
v1.0.0 Converts a range reference to its A1 notation string representation. Returns the range in A1 notation (e.g., "A1:B10") for the given range. For single-cell ranges, returns just the cell reference (e.g., "A1" instead of "A1:A1"). This is useful for generating dynamic range references, creating hyperlinks, or building formulas programmatically.
Parameters
1. range
Formula
LET(
topLeftCell, ADDRESS(ROW(range), COLUMN(range), 4),
bottomRightCell, ADDRESS(ROW(range) + ROWS(range) - 1, COLUMN(range) + COLUMNS(range) - 1, 4),
IF(
topLeftCell = bottomRightCell,
topLeftCell,
topLeftCell & ":" & bottomRightCell
)
)
Description:
The range to convert to A1 notation
Example:
A1:B10
SINGLE
Description
v1.0.0 Returns a single value from an array at the specified row and column position. This function extracts a single cell from a range using 1-based indexing. It is useful for retrieving a specific value from a larger array or range.
Parameters
1. array
2. row
3. column
Formula
INDEX(array, row, column)
Description:
The input array or range to extract from
Example:
A1:C10
Description:
The row index (1-based) of the value to extract
Example:
2
Description:
The column index (1-based) of the value to extract
Example:
3
SUBSTITUTEMULTI
Description
v1.0.0 Applies multiple SUBSTITUTE operations sequentially using a two-column mapping range. Substitutions are applied in row order, with later substitutions operating on the results of earlier ones. This enables powerful multi-stage text transformations.
Parameters
1. text
2. mappings
Formula
=LET(
num_mappings, ROWS(mappings),
_validate_mappings, IF(COLUMNS(mappings) <> 2,
(XLOOKUP("Mappings must be a two-column range", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
_validate_rows, IF(num_mappings < 1,
(XLOOKUP("Mappings must have at least 1 row", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
substitution_func, LAMBDA(accumulated_text, row_num,
LET(
search_text, INDEX(mappings, row_num, 1),
replace_text, INDEX(mappings, row_num, 2),
IF(OR(search_text = "", ISBLANK(search_text)),
accumulated_text,
SUBSTITUTE(accumulated_text, search_text, replace_text)
)
)
),
IF(OR(ROWS(text) > 1, COLUMNS(text) > 1),
MAP(text, LAMBDA(cell,
REDUCE(
cell,
SEQUENCE(num_mappings),
substitution_func
)
)),
REDUCE(
text,
SEQUENCE(num_mappings),
substitution_func
)
)
)
Description:
Text to perform substitutions on (single cell or range)
Example:
A1:A10
Description:
Two-column range where column 1 contains text to find and column 2 contains replacement text. Substitutions are applied sequentially in row order.
Example:
B1:C5
TAKE
Description
v1.0.0 Takes a rectangular region from a range (both rows and columns). This is the Excel-compatible TAKE function. Positive row/col values take from start, negative from end. Both parameters are required.
Parameters
1. range
2. rows
3. cols
Formula
=LET(
rows_taken, (LET(
total_rows, ROWS(range),
_validate_zero, IF(rows = 0,
(XLOOKUP("rows cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(rows > 0,
rows,
total_rows + rows + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count > total_rows),
(XLOOKUP("rows has absolute value (" & ABS(rows) & ") exceeding total rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
IF(normalized_count = 1,
CHOOSEROWS(range, 1),
CHOOSEROWS(range, SEQUENCE(normalized_count))
)
)),
(LET(
transposed, TRANSPOSE(rows_taken),
result, (LET(
total_rows, ROWS(transposed),
_validate_zero, IF(cols = 0,
(XLOOKUP("cols cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(cols > 0,
cols,
total_rows + cols + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count > total_rows),
(XLOOKUP("cols has absolute value (" & ABS(cols) & ") exceeding total rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
IF(normalized_count = 1,
CHOOSEROWS(transposed, 1),
CHOOSEROWS(transposed, SEQUENCE(normalized_count))
)
)),
TRANSPOSE(result)
))
)
Description:
The input data range
Example:
A1:Z100
Description:
Number of rows to take. Positive values take from start, negative from end. Cannot be 0 or have absolute value exceeding total rows.
Example:
10
Description:
Number of columns to take. Positive values take from start, negative from end. Cannot be 0 or have absolute value exceeding total columns.
Example:
5
TAKECOLS
Description
v1.0.0 Takes the first or last N columns from a range. Positive num_cols takes from the start, negative num_cols takes from the end. Uses the transpose pattern to work with columns.
Parameters
1. range
2. num_cols
Formula
=LET(
transposed, TRANSPOSE(range),
result, (LET(
total_rows, ROWS(transposed),
_validate_zero, IF(num_cols = 0,
(XLOOKUP("num_cols cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(num_cols > 0,
num_cols,
total_rows + num_cols + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count > total_rows),
(XLOOKUP("num_cols has absolute value (" & ABS(num_cols) & ") exceeding total rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
IF(normalized_count = 1,
CHOOSEROWS(transposed, 1),
CHOOSEROWS(transposed, SEQUENCE(normalized_count))
)
)),
TRANSPOSE(result)
)
Description:
The input data range
Example:
A1:Z100
Description:
Number of columns to take. Positive values take from start, negative from end. Cannot be 0 or have absolute value exceeding total columns.
Example:
5
TAKEROWS
Description
v1.0.0 Takes the first or last N rows from a range. Positive num_rows takes from the start, negative num_rows takes from the end. Useful for extracting top or bottom rows from a dataset.
Parameters
1. range
2. num_rows
Formula
=LET(
total_rows, ROWS(range),
_validate_zero, IF(num_rows = 0,
(XLOOKUP("num_rows cannot be 0", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
normalized_count, IF(num_rows > 0,
num_rows,
total_rows + num_rows + 1
),
_validate_excess, IF(OR(normalized_count < 0, normalized_count > total_rows),
(XLOOKUP("num_rows has absolute value (" & ABS(num_rows) & ") exceeding total rows (" & total_rows & ")", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
IF(normalized_count = 1,
CHOOSEROWS(range, 1),
CHOOSEROWS(range, SEQUENCE(normalized_count))
)
)
Description:
The input data range
Example:
A1:Z100
Description:
Number of rows to take. Positive values take from start, negative from end. Cannot be 0 or have absolute value exceeding total rows.
Example:
5
TEXTAFTER
Description
v1.0.0 Returns text that appears after a specified delimiter. Supports forward/backward search, case-sensitive/insensitive matching, and customizable error handling. Replicates Excel's TEXTAFTER function for Google Sheets.
Parameters
1. text
2. delimiter
3. instance_num
4. match_mode
5. match_end
6. if_not_found
Formula
LET(
IF(instance_num = 0, NA(),
IF(delimiter = "",
IF(instance_num > 0, text, ""),
LET(
search_text, IF(match_mode = 1, UPPER(text), text),
search_delim, IF(match_mode = 1, UPPER(delimiter), delimiter),
total_count, (LEN(search_text) - LEN(SUBSTITUTE(search_text, search_delim, ""))) / LEN(search_delim),
positive_inst, IF(instance_num < 0, total_count + instance_num + 1, instance_num),
found, positive_inst > 0 AND positive_inst <= total_count,
IF(found,
LET(
marker, "§§§TEXTAFTER§§§",
text_with_marker, SUBSTITUTE(search_text, search_delim, marker, positive_inst),
delim_pos, FIND(marker, text_with_marker),
after_pos, delim_pos + LEN(delimiter),
MID(text, after_pos, LEN(text))
),
IF(match_end = 1,
IF(instance_num > 0, "", text),
if_not_found
)
)
)
)
)
)
Description:
The source text to search within
Example:
"john.doe@example.com"
Description:
The text marking where extraction begins (extracts text after this)
Example:
"@"
Description:
Which occurrence to use. Positive counts from left (1=first), negative from right (-1=last). Cannot be 0.
Example:
1
Description:
Case sensitivity. 0 for case-sensitive (default), 1 for case-insensitive
Description:
End-of-text handling. 0 requires exact match (default), 1 treats end of text as delimiter
Description:
Value to return if delimiter not found. Use NA() for
Example:
NA()
TEXTBEFORE
Description
v1.0.0 Returns text that appears before a specified delimiter. Supports forward/backward search, case-sensitive/insensitive matching, and customizable error handling. Replicates Excel's TEXTBEFORE function for Google Sheets.
Parameters
1. text
2. delimiter
3. instance_num
4. match_mode
5. match_end
6. if_not_found
Formula
LET(
IF(instance_num = 0, NA(),
IF(delimiter = "",
IF(instance_num > 0, "", text),
LET(
search_text, IF(match_mode = 1, UPPER(text), text),
search_delim, IF(match_mode = 1, UPPER(delimiter), delimiter),
total_count, (LEN(search_text) - LEN(SUBSTITUTE(search_text, search_delim, ""))) / LEN(search_delim),
positive_inst, IF(instance_num < 0, total_count + instance_num + 1, instance_num),
found, positive_inst > 0 AND positive_inst <= total_count,
IF(found,
LET(
marker, "§§§TEXTBEFORE§§§",
text_with_marker, SUBSTITUTE(search_text, search_delim, marker, positive_inst),
delim_pos, FIND(marker, text_with_marker),
MID(text, 1, delim_pos - 1)
),
IF(match_end = 1,
IF(instance_num > 0, text, ""),
if_not_found
)
)
)
)
)
)
Description:
The source text to search within
Example:
"john.doe@example.com"
Description:
The text marking where extraction ends (extracts text before this)
Example:
"@"
Description:
Which occurrence to use. Positive counts from left (1=first), negative from right (-1=last). Cannot be 0.
Example:
1
Description:
Case sensitivity. 0 for case-sensitive (default), 1 for case-insensitive
Description:
End-of-text handling. 0 requires exact match (default), 1 treats end of text as delimiter
Description:
Value to return if delimiter not found. Use NA() for
Example:
NA()
TEXTSPLIT
Description
v1.0.0 Splits text into an array using a delimiter (1D version - columns only). This is a simplified version of Excel's TEXTSPLIT function that splits text into a single row with multiple columns. Wraps Google Sheets' SPLIT function with consistent naming. Future versions may support 2D splitting with row delimiters.
Parameters
1. text
2. col_delimiter
Formula
LET(
result, SPLIT(text, col_delimiter),
result
)
Description:
The text to split
Example:
"apple,banana,cherry"
Description:
The delimiter to use for splitting text into columns
Example:
","
TRIMRANGE
Description
v1.0.0 Removes empty rows and columns from the outer edges of a range, similar to how TRIM removes leading and trailing spaces from text. Only affects the boundaries (top, bottom, left, right) - preserves empty rows/columns in the middle of data. A row/column is considered empty if all cells are blank or empty strings. Returns BLANK() if the entire range is empty after trimming.
Parameters
1. range
2. trim_rows
3. trim_cols
Formula
=LET(
rows_result, IF(trim_rows,
LET(
row_nonempty, BYROW(range, LAMBDA(r, COUNTA(r) > 0)),
first_row, XMATCH(TRUE, row_nonempty),
last_row, XMATCH(TRUE, row_nonempty, 0, -1),
IF(ISERROR(first_row),
(IF(,,)),
CHOOSEROWS(range, SEQUENCE(last_row - first_row + 1, 1, first_row))
)
),
range
),
IF(ISBLANK(rows_result),
(IF(,,)),
IF(trim_cols,
LET(
transposed, TRANSPOSE(rows_result),
col_nonempty, BYROW(transposed, LAMBDA(c, COUNTA(c) > 0)),
first_col, XMATCH(TRUE, col_nonempty),
last_col, XMATCH(TRUE, col_nonempty, 0, -1),
IF(ISERROR(first_col),
(IF(,,)),
TRANSPOSE(CHOOSEROWS(transposed, SEQUENCE(last_col - first_col + 1, 1, first_col)))
)
),
rows_result
)
)
)
Description:
The data range to trim from edges
Example:
A1:E10
Description:
Whether to remove empty rows from top and bottom edges. Use TRUE to trim rows, FALSE to keep all rows.
Example:
TRUE
Description:
Whether to remove empty columns from left and right edges. Use TRUE to trim columns, FALSE to keep all columns.
Example:
TRUE
UNPIVOT
Description
v1.0.2 Transforms wide-format data into long-format (tidy data) by unpivoting specified columns into attribute-value pairs.
Parameters
1. data
2. fixedcols
3. attributecol
4. valuecol
5. select_columns
6. fillna
Formula
=LET(
fc, IF(OR(fixedcols = "", ISBLANK(fixedcols)), 1, fixedcols),
ac, IF(OR(attributecol = "", ISBLANK(attributecol)), "Attribute", attributecol),
vc, IF(OR(valuecol = "", ISBLANK(valuecol)), "Value", valuecol),
fillna_val, (MAP(fillna, LAMBDA(v, IF(ISBLANK(v), "", v)))),
num_rows, ROWS(data),
num_cols, COLUMNS(data),
_validate_dims, IF(OR(num_rows < 2, num_cols < 2),
(XLOOKUP("Data must have at least 2 rows and 2 columns", IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
_validate_fc, IF(OR(fc < 1, fc >= num_cols),
(XLOOKUP("fixedcols must be between 1 and " & (num_cols - 1), IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
all_headers, INDEX(data, 1, SEQUENCE(1, num_cols)),
selected_cols, IF(OR(select_columns = "", ISBLANK(select_columns)),
SEQUENCE(1, num_cols - fc, fc + 1),
IF(ISTEXT(INDEX(select_columns, 1, 1)),
LET(
flat_selection, FILTER(FLATTEN(select_columns), FLATTEN(select_columns) <> ""),
matched_indices, MAKEARRAY(1, COLUMNS(flat_selection), LAMBDA(r, c,
LET(
search_name, INDEX(flat_selection, 1, c),
match_result, MATCH(search_name, all_headers, 0),
IF(ISNA(match_result),
(XLOOKUP("Column '" & search_name & "' not found in headers", IF(FALSE, {1}), IF(FALSE, {1}))),
match_result
)
)
)),
matched_indices
),
LET(
flat_indices, FLATTEN(select_columns),
_validate_indices, IF(
OR(
SUMPRODUCT(--(flat_indices < 1)) > 0,
SUMPRODUCT(--(flat_indices > num_cols)) > 0
),
(XLOOKUP("Column indices must be between 1 and " & num_cols, IF(FALSE, {1}), IF(FALSE, {1}))),
TRUE
),
flat_indices
)
)
),
ncols, COLUMNS(selected_cols),
nrows, num_rows - 1,
total_output, nrows * ncols,
unpivoted, MAKEARRAY(total_output, fc + 2, LAMBDA(r, c,
LET(
source_row, INT((r - 1) / ncols) + 2,
col_idx, MOD(r - 1, ncols) + 1,
value_col_num, INDEX(selected_cols, 1, col_idx),
cell_value, IF(c <= fc,
INDEX(data, source_row, c),
IF(c = fc + 1,
INDEX(data, 1, value_col_num),
INDEX(data, source_row, value_col_num)
)
),
IF(AND(c = fc + 2, cell_value = "", fillna_val <> ""),
fillna_val,
cell_value
)
)
)),
output_headers, MAKEARRAY(1, fc + 2, LAMBDA(r, c,
IF(c <= fc,
INDEX(data, 1, c),
IF(c = fc + 1, ac, vc)
)
)),
VSTACK(output_headers, unpivoted)
)
Description:
Input range including headers (first row must contain column names)
Example:
A1:F100
Description:
Number of leftmost columns to keep as identifiers (not unpivoted)
Example:
2
Description:
Name for the column that will contain the unpivoted header names
Example:
Quarter
Description:
Name for the column that will contain the unpivoted cell values
Example:
Sales
Description:
Specifies which columns to unpivot. Can be array of strings (column names) or array of integers (1-based column indices). Empty string unpivots all non-fixed columns.
Example:
{"Q1", "Q2", "Q3"}
Description:
Value to replace empty cells with in the value column only. Default keeps blanks as-is. Different from filtering (use FILTER() wrapper to remove rows).
VSTACKBLANK
Description
v1.0.0 Stacks two arrays vertically, padding narrower arrays with blank cells to match dimensions. Convenience wrapper for VSTACKFILL using BLANK().
Parameters
1. array1
2. array2
Formula
VSTACKFILL(array1, array2, (IF(,,)))
Description:
First array to stack
Example:
A1:C10
Description:
Second array to stack
Example:
E1:F5
VSTACKFILL
Description
v1.0.0 Stacks two arrays vertically, padding narrower arrays with a specified fill value to match dimensions. Prevents #N/A errors from mismatched widths.
Parameters
1. array1
2. array2
3. fill_value
Formula
LET(
cols1, COLUMNS(array1),
cols2, COLUMNS(array2),
max_cols, MAX(cols1, cols2),
padded1, IF(cols1 < max_cols,
HSTACK(array1, MAKEARRAY(ROWS(array1), max_cols - cols1, LAMBDA(r, c, fill_value))),
array1),
padded2, IF(cols2 < max_cols,
HSTACK(array2, MAKEARRAY(ROWS(array2), max_cols - cols2, LAMBDA(r, c, fill_value))),
array2),
VSTACK(padded1, padded2)
)
Description:
First array to stack
Example:
A1:C10
Description:
Second array to stack
Example:
E1:F5
Description:
Value to use for padding cells (use BLANK() for empty cells)
Example:
BLANK()
WRAP
Description
v1.0.0 Wraps content with opening and closing delimiters. Useful for generating HTML/XML tags, brackets, or any paired delimiter pattern around text or cell values.
Parameters
1. delimiter
2. contents
Formula
"<" & delimiter & ">" & contents & "</" & delimiter & ">"
Description:
The delimiter name (e.g., "div", "span", "strong") used to create the opening and closing wrapper
Example:
div
Description:
The content to be wrapped
Example:
A1
To add a new formula:
- Create a new
.yamlfile in theformulasdirectory - Follow the schema structure (see existing formulas for reference)
- Run
uv run generate_readme.pyto update the README - The README will also be automatically updated via GitHub Actions on push to main
See LICENSE file for details.