A collection of named Excel/Google Sheets formulas using LET and LAMBDA functions.
- 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.
- 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 have at least one blank cell from sparse data. This is a convenience wrapper around DENSIFY that specifically targets row operations with the "rows-any" mode.
- 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.
- UNPIVOT - Transforms wide-format data into long-format (tidy data) by unpivoting specified columns into attribute-value pairs.
BYROW_COMPLETE_ONLY
Description
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:
""
Description:
LAMBDA function to apply to each complete row. Receives a single row as input.
Example:
LAMBDA(row, SUM(row))
BYROW_NONEMPTY_ONLY
Description
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:
""
Description:
LAMBDA function to apply to each non-empty row. Receives a single row as input.
Example:
LAMBDA(row, TEXTJOIN(", ", TRUE, row))
DENSIFY
Description
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))), ""),
IFNA(FILTER(range, BYROW(range, LAMBDA(r, COUNTA(r) >= threshold))), "")
)
),
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))), ""),
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, COUNTA(c) >= threshold))), "")
)
)
),
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
Removes rows that have at least one blank cell from sparse data. This is a convenience wrapper around DENSIFY that specifically targets row operations with the "rows-any" mode.
Parameters
1. range
Formula
=LET(
actual_mode, IF(OR("rows-any"="", "rows-any"=0), "both", LOWER(TRIM("rows-any"))),
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))), ""),
IFNA(FILTER(range, BYROW(range, LAMBDA(r, COUNTA(r) >= threshold))), "")
)
),
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))), ""),
IFNA(FILTER(transposed, BYROW(transposed, LAMBDA(c, COUNTA(c) >= threshold))), "")
)
)
),
rows_filtered
),
final
)
)
)
Description:
The data range to densify (remove incomplete rows)
Example:
A1:Z100
GROUPBY
Description
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))
UNPIVOT
Description
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, IF(ISBLANK(fillna), "", 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).
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.