Named Functions

A collection of named Excel/Google Sheets formulas using LET and LAMBDA functions.

Formulas

Quick Reference

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.
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.
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.
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.

Detailed Formulas

BLANK

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

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)))

input

Description:

Single value or range to convert (blank cells become empty strings)

Example:

A1:B10

BYROW_COMPLETE_ONLY

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)
    )
  )
)

input_range

Description:

Range of data to process row by row

Example:

A1:D10

value_if_incomplete

Description:

Value to return for rows that contain any blank cells

Example:

BLANK()

row_operation

Description:

LAMBDA function to apply to each complete row. Receives a single row as input.

Example:

LAMBDA(row, SUM(row))

BYROW_NONEMPTY_ONLY

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)
    )
  )
)

input_range

Description:

Range of data to process row by row

Example:

A1:D10

value_if_empty

Description:

Value to return for rows that are completely empty (all cells blank)

Example:

BLANK()

row_operation

Description:

LAMBDA function to apply to each non-empty row. Receives a single row as input.

Example:

LAMBDA(row, TEXTJOIN(", ", TRUE, row))

DENSIFY

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
    )
  )
)

range

Description:

The data range to densify. Example - A1:Z100

Example:

A1:Z100

mode

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

DENSIFYROWS

Description

v1.0.0 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))), 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
    )
  )
)

range

Description:

The data range to densify (remove incomplete rows)

Example:

A1:Z100

EMPTYTOBLANK

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)))

input

Description:

Single value or range to convert (empty strings become blank cells)

Example:

A1:B10

GROUPBY

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)
)

data

Description:

Input dataset without headers (2D array of values to group and aggregate)

Example:

A2:D100

group_cols

Description:

Column indices to group by (1-based). Single integer or array of integers.

Example:

{1, 2}

value_cols

Description:

Column indices to aggregate (1-based). Single integer or array of integers.

Example:

agg_lambda

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

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)
)

data

Description:

Input range including headers (first row must contain column names)

Example:

A1:F100

fixedcols

Description:

Number of leftmost columns to keep as identifiers (not unpivoted)

Example:

attributecol

Description:

Name for the column that will contain the unpivoted header names

Example:

Quarter

valuecol

Description:

Name for the column that will contain the unpivoted cell values

Example:

Sales

select_columns

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"}

fillna

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

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 & ">"
)

delimiter

Description:

The delimiter name (e.g., "div", "span", "strong") used to create the opening and closing wrapper

Example:

div

Contributing

To add a new formula:

Create a new .yaml file in the formulas directory
Follow the schema structure (see existing formulas for reference)
Run uv run generate_readme.py to update the README
The README will also be automatically updated via GitHub Actions on push to main

License

See LICENSE file for details.

Name		Name	Last commit message	Last commit date
Latest commit History 55 Commits
.github/workflows		.github/workflows
formulas		formulas
.gitignore		.gitignore
.readme-template.md		.readme-template.md
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
README.md		README.md
generate_readme.py		generate_readme.py

License

Jython1415/named-functions

Folders and files

Latest commit

History

Repository files navigation

Named Functions

Formulas

Quick Reference

Detailed Formulas

BLANK

BLANKTOEMPTY

input

BYROW_COMPLETE_ONLY

input_range

value_if_incomplete

row_operation

BYROW_NONEMPTY_ONLY

input_range

value_if_empty

row_operation

DENSIFY

range

mode

DENSIFYROWS

range

EMPTYTOBLANK

input

GROUPBY

data

group_cols

value_cols

agg_lambda

UNPIVOT

data

fixedcols

attributecol

valuecol

select_columns

fillna

WRAP

delimiter

contents

Contributing

License

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Contributors 3

Uh oh!

Languages