A Guide To Named Functions In Google Sheets

💡 Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

Named Functions in Google Sheets let you save and name your own custom formulas and then re-use them in other Google Sheet files.

Wow! How exciting is that?!? (Hint: VERY)

For example, here I’m using a named function I created called STARCHART to add a rating chart to my Sheet:

Named Functions in Google Sheets Star Chart Example

And here I’m using a named function I called UNPIVOT to turn my wide data into a tall format:

Named Functions in Google Sheets Unpivot Example

As a final example, here I’m calculating a rolling 10-day average of my data with a named function I created:

Named Functions Rolling Average Example

Named functions are the biggest update to Google Sheets in years, easily the biggest since Connected Sheets.

It’s hard to overstate how useful named functions will be. If you’re a Google Sheets fan, this is like Santa coming early and giving you the biggest Christmas present ever.

Note: it may take up to 15 days from August 24th before you see Named Functions in your Sheets.

Why Use Named Functions?

They open up a world of amazing possibilities.

You can imagine the spreadsheet expert at your organization creates a suite of useful functions. Then anyone else can import and use these custom formulas whenever they want. They don’t even need to understand how they work under the hood.

For example, suppose you use a complex formula to calculate a crucial financial metric in your business. Everyone wants to see it but it’s too difficult for most users to create and it’s easy for errors to creep in. With a named function though, it’s as easy as using the SUM function.

So, how do we create one?

How To Create A Named Function in Google Sheets

Let’s walk through the star chart example above.

In my Google Sheet, I create a formula to add a star rating. It’s a useful formula for the customer service team to use, but it’s too complex for some folks to use. However, if we turn it into a named function then anyone within our organization can use it like a regular function.

First, we have to create the named function in a Sheet.

Creating Named Functions

I recommend creating your regular formula first, to ensure it’s working correctly, before turning it into a named function.

Step 1

To begin, we create the star formula using the CHAR function and the REPT function:

=REPT(CHAR(11088),A2)

Step 2

Right-click on this function and choose:

View more cell actions > Define named function

define Named Function

Or go to the menu:

Data > Named functions

Define Named Functions Via Menu

Step 3

In the sidebar window, select: Add new function

Step 4

We create the named function on the next screen.

To start, type in a name for the new function, e.g. STARCHART

Spaces are not allowed in function names and the text will default to UPPERCASE which is the standard convention for function names.

Step 5

Next, add a description of what the new named function will do. In this example, let’s say “Star symbol repeated a specified number of times.”

Step 6

One big difference between a named function and a regular function is that we put placeholders into the formula as inputs, instead of references to cells or ranges.

In this step, we tell the named function what input variables we want to use. We can also define these directly from the function in Step 7 if that’s easier to understand. So this step is optional.

Think of the SUM function for a second. When you use it, you supply a range of values that it sums. Easy huh?

Same deal here.

In this star chart example, we give the function a single value as the input.

So add an argument placeholder called: value

Step 7

In the final box, we add the formula from our Sheet, including the equals (if it’s not already shown).

The named function editor then prompts us to replace any references (e.g. A2) with placeholders.

Either use the suggested options, or any options you created in Step 6, or type in a new placeholder name here.

The named function editor replaces all occurrences of the reference (e.g. A2) with the placeholder name.

Now, our editor window looks like this:

Named Function In Google Sheets

Click Next to continue.

Step 8

The final step is to provide optional details about the placeholder values. We provide a description and example value to make it easier for users to use this named function.

Add a description for the value “The number of stars to show.”

And add an example value of 5.

Click Update to finish.

And voila! We’ve created our first named function.

Now, we can use this function immediately in this Sheet, by typing in

=STARCHART(A2)

(You don’t need to keep the original formula in cell A2 if you don’t want to.)

Importing And Using Named Functions

Once the named function is created, we can import it into other Sheets and use it there.

Import Named Functions in Google Sheets

And that’s the magic of these new functions. It’s really a big step forward towards reusability. For years, I’ve wished I could save a gallery of useful functions, and now it’s possible.

So let’s see how to import named functions.