Google Sheets named functions: build your own formula

You finally got the formula right — a 90-character pile of IFERROR(QUERY(...)) that converts a raw export into the one cell value your dashboard needs. Then you paste it into the next sheet and the cell references all shift. You patch it. A week later, a coworker asks how it works. You can’t explain it without scrolling.

Named functions fix that. They let you wrap a long formula in a name, define its arguments, and call it like any built-in. Your sheet now reads =PCT_CHANGE(B2, C2) instead of a wall of nested calls. Below is how to build one, where it quietly breaks, and when you should reach for something else.

What a named function actually is

Google ships named functions as a Data-menu wizard, which makes them feel like a separate feature. They are not. A named function is a thin user-interface on top of LAMBDA, the same anonymous-function primitive Excel introduced in 2021 and Google added shortly after. Knowing this changes how you write them.

A LAMBDA takes a list of argument names and a formula body, then returns a callable. You can use one inline:

=LAMBDA(old, new, (new - old) / old)(180000, 200088)

The arguments are old and new; the body is the percent-change expression; the trailing parentheses call it with two values. The result is 0.1116. A named function is the same thing, with a name attached so you can call it from anywhere in the workbook.

Build your first named function

The fastest way to get a working function is to write the formula in a real cell first, confirm it returns the right value, then promote it. Skipping this step is the single most common reason a named function refuses to save: the wizard rejects formulas that already have #REF! or #NAME? errors, and it gives you almost no feedback about why.

Step-by-step setup

1. In a scratch cell, write =(C2-B2)/B2 and confirm it returns the expected number for one row.
2. Open Data → Named functions, then click Add new function.
3. Name it PCT_CHANGE. Names allow uppercase letters, digits, and underscores only — no spaces, no hyphens.
4. Add two argument placeholders: old and new.
5. In Formula definition, paste =(new - old) / old. Sheets will highlight old and new as recognized arguments.
6. Click Next, fill in one example input per argument, and save.

Call it from any cell: =PCT_CHANGE(B2, C2). The function is now scoped to the workbook. Anyone who opens the sheet sees the name resolve.

Pass ranges and handle blank arguments

Single-cell arguments are easy. Ranges and optional arguments are where most users get stuck, because the wizard’s behavior is not documented in plain terms.

First, the rule Google does state: all literal ranges inside the formula body are absolute. If you write SUM(A:A) in the body, it always reads column A of the sheet the formula is called from. That is usually fine, but it means you cannot use a body-local reference as a substitute for an argument. To accept a range, declare it as an argument:

The SHARE function takes a value and a range, then returns the value as a percentage of the range sum. Its body is one line:

=value / SUM(range)

Arguments value (number) and range (1D range). Pass the range as the second argument and the function reads whatever you gave it, not a sheet-local reference.

Guarding optional and blank inputs

Named functions do not support truly optional arguments — every argument must be supplied at the call site. But you can simulate one by accepting a value and replacing a blank with a default inside the body. A two-argument version of SHARE that defaults the range to column B looks like this:

=value / SUM(IF(ISBLANK(range), B:B, range))

Wrap the whole thing in IFERROR when the function will land in a column where some rows are blank, so a sparse table does not light up with #DIV/0! errors that scare your stakeholders:

Import a named function into another workbook

A named function lives in the workbook where it was defined. To use it elsewhere, open the second workbook, choose Data → Named functions → Import function, pick the source file, and select the function. It now appears in the destination workbook’s list.

What just happened is the thing most tutorials do not say out loud: Sheets copied the definition into the destination workbook. There is no live link. If you edit PCT_CHANGE in the original workbook tomorrow, the copy in the second workbook does not update. The same is true for Apps Script custom functions bound to a single spreadsheet — Sheets has no concept of a shared function library across files.

• ✓ Source workbook is the canonical copy — document it
• ✓ Imported function names match the source exactly
• ✓ Argument count and order match across copies
• ✓ Breaking changes get a new name, not an in-place edit

Debug a named function that will not run

When a named function returns #ERROR! with no detail, work backwards. Replace the named call with the raw formula it expands to, swapping each argument name for the literal cell reference. If the raw version works, the bug is in the function definition. If the raw version also fails, the bug is in your inputs.

=PCT_CHANGE(B2, C2)
→ #ERROR!

=(C2 - B2) / B2
→ #DIV/0!

A #NAME? result usually means you typed an argument the wizard does not recognize. Reopen the definition and check that every name in the body appears in the argument list above. #REF! after a successful save usually means you imported the function into a workbook whose sheet structure does not match what the body assumes.

When not to reach for a named function

Named functions are powerful, but they are not free. Each one is one more thing a future reader has to open to understand, and they cannot do anything a regular formula cannot do. Three alternatives often win.

A helper column is the option people forget. If a calculation runs in exactly one place, a column of =(C2 - B2) / B2 is honest and instantly debuggable. The named function pays off when the same logic appears in three or more places, or when the formula is long enough that giving it a name is genuinely clearer than reading the formula itself. For deeper formula patterns, our guide to Google Sheets array formulas covers cases where one named function can replace a whole column of helpers, and our walkthrough of the QUERY function handles the structured-data case.

If your function needs to fetch a URL, format a JSON response, or run a loop with state, a named function cannot help — those are Apps Script territory. For the full list of what the wizard accepts and the workbook-level constraints, the official Google Sheets named functions reference is the canonical source.

A short answer key

Build the formula in a real cell. Promote it once it works. Pass ranges as arguments, not as body-local references. Wrap errors inside the body. Treat imports as copies and version their names. Reach for a helper column or QUERY before reaching for the wizard, and reach for Apps Script when the wizard cannot follow you.

• ✓ Prototype in a cell, then promote
• ✓ Pass ranges as arguments, never hard-code them in the body
• ✓ Wrap the body in IFERROR once, not at every call
• ✓ Version the name when behavior changes
• ✓ Default to a helper column when the formula lives in one place

The first named function you ship saves you a dozen pastes. The tenth one is where you start writing names like PCT_CHANGE_V2 and thanking yourself for the lab tab.