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. Equivalent to using the omitted argument syntax in IF (e.g., IF(condition, , value)), but more semantically clear and readable.
- 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.
- 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).
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- UNPIVOT - Transforms wide-format data into long-format (tidy data) by unpivoting specified columns into attribute-value pairs.
- 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.0.1 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. Equivalent to using the omitted argument syntax in IF (e.g., IF(condition, , value)), but more semantically clear and readable.
Formula
LAMBDA(input, IF(,,))(0)
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))
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),
total_rows, ROWS(range),
_validate, IF(header_rows >= total_rows,
ERROR("num_header_rows (" & header_rows & ") must be less than total rows (" & total_rows & ")"),
TRUE
),
OMITROWS(range, SEQUENCE(header_rows))
)
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.3 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).
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),
IF(has_strict,
IFNA(FILTER(range, BYROW(range, LAMBDA(r, SUMPRODUCT((LEN(TRIM(r)) > 0) * 1) >= threshold))), BLANK()),
IFNA(FILTER(range, BYROW(range, LAMBDA(r, COUNTA(r) >= threshold))), BLANK())
)
),
range
),
final, IF(should_remove_cols,
LET(
transposed, TRANSPOSE(rows_filtered),
threshold, IF(has_any, ROWS(rows_filtered), 1),
TRANSPOSE(
IF(has_strict,
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, SUMPRODUCT((LEN(TRIM(c)) > 0) * 1) >= threshold))), BLANK()),
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, COUNTA(c) >= threshold))), BLANK())
)
)
),
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),
IF(has_strict,
IFNA(FILTER(range, BYROW(range, LAMBDA(r, SUMPRODUCT((LEN(TRIM(r)) > 0) * 1) >= threshold))), BLANK()),
IFNA(FILTER(range, BYROW(range, LAMBDA(r, COUNTA(r) >= threshold))), BLANK())
)
),
range
),
final, IF(should_remove_cols,
LET(
transposed, TRANSPOSE(rows_filtered),
threshold, IF(has_any, ROWS(rows_filtered), 1),
TRANSPOSE(
IF(has_strict,
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, SUMPRODUCT((LEN(TRIM(c)) > 0) * 1) >= threshold))), BLANK()),
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, COUNTA(c) >= threshold))), BLANK())
)
)
),
rows_filtered
),
final
)
)
)
Description:
The data range to densify (remove empty rows)
Example:
A1:Z100
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 = "", BLANK(), v)))
Description:
Single value or range to convert (empty strings become blank cells)
Example:
A1:B10
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),
ERROR("Data must have at least 1 row and 1 column"),
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
),
ERROR("Group column indices must be between 1 and " & num_cols),
TRUE
),
_validate_value_cols, IF(
OR(
SUMPRODUCT(--(value_cols_array < 1)) > 0,
SUMPRODUCT(--(value_cols_array > num_cols)) > 0
),
ERROR("Value column indices must be between 1 and " & num_cols),
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),
_validate, IF(rows > ROWS(range),
ERROR("num_rows (" & rows & ") exceeds total rows in range (" & ROWS(range) & ")"),
TRUE
),
IF(rows = 1,
CHOOSEROWS(range, 1),
CHOOSEROWS(range, SEQUENCE(rows))
)
)
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
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
LAMBDA(cell, OR(ISBLANK(cell), cell = ""))
Description:
The cell to check for blank-like condition
Example:
A1
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,
ERROR("Cannot omit all rows from transposed"),
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,
ERROR("Cannot omit all rows from range"),
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}
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,
ERROR("Mappings must be a two-column range"),
TRUE
),
_validate_rows, IF(num_mappings < 1,
ERROR("Mappings must have at least 1 row"),
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
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, BLANKTOEMPTY(fillna),
num_rows, ROWS(data),
num_cols, COLUMNS(data),
_validate_dims, IF(OR(num_rows < 2, num_cols < 2),
ERROR("Data must have at least 2 rows and 2 columns"),
TRUE
),
_validate_fc, IF(OR(fc < 1, fc >= num_cols),
ERROR("fixedcols must be between 1 and " & (num_cols - 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),
ERROR("Column '" & search_name & "' not found in headers"),
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
),
ERROR("Column indices must be between 1 and " & num_cols),
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).
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
LAMBDA(delimiter, contents,
"<" & 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.