A Guide To Named Functions In Google Sheets (New For 2022!)

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.

Step 1

Open a brand new Google Sheet by typing sheet.new into your browser

Step 2

Go to the menu: Data > Named functions

Step 3

Select Import function

Step 4

From the popup file picker, select the Sheet where you created the named function in steps 1 – 9 above.

Step 5

Assuming you chose the correct Google Sheet file, you should see the Import named functions selector.

Here, we choose the named function(s) we want to import.

Check the box and hit Import

Step 6

Use the named function like any other regular function!

Star Chart In Google Sheets

How Do Named Functions Work?

Named Functions are essentially a GUI (Graphical User Interface) wrapper on top of a LAMBDA function.

What on earth does that mean?

A LAMBDA function lets you create custom functions with placedholders as inputs.

For example, this function calculates the percentage change between the values in cells B2 and B3:

=LAMBDA(old,new,TO_PERCENT((new-old)/old))(B2,C2)

Notice how the LAMBDA function takes placholders as the arguments, and a function definition, same as named funtions.

But if we create a dedicated named function to do this, it’s much easier to use:

=PERCENTCHANGE(B2,C2)

Other Named Function Examples

Let’s see two more, useful examples: unpivot and rolling average.

The two GIFS at the beginning of this post show them in action.

Unpivot Example

In spreadsheets, we have two orientations for our data: wide data or tall data.

Wide data is where the column headers are a series, e.g. Q1, Q2, Q3, Q4 and the table is wide. It’s used for charts.

In contrast, tall data would have a single column called “Quarter” that recorded whether that row was Q1, Q2, etc. It has more rows than the wide data, but fewer columns typically. It’s how databases store data.

To go from tall data to wide data, we use a pivot table and it’s very easy.

But to go from wide to tall is much more difficult. We need to use a complex unpivot formula (see unpivot formula details here).

=ArrayFormula(SPLIT(FLATTEN(A2:A5&"🪐"&B1:D1&"🪐"&B2:D5),"🪐"))

Thankfully we can turn this into a named function and then use that instead:

=UNPIVOT(A2:A5,B1:D1,B2:D5)

Unpivot Named Formula Example

Much easier!

And, here’s how the Unpivot named function is defined:

unpivot Named Functions Definition

Rolling Average Example

The formula to calculate a rolling average is rather complicated, so it’s a great candidate for a named function. Plus, it lets us add an argument to set the number of days, so it can be a 3-day, 10-day, 30-day, etc.

The named function is:

=ROLLINGAVERAGE(B2:B126,10)

As shown in this example:

rolling Average in Google Sheets

And the definition of the named function is:

rolling Average Definition Named Functions

Named Functions Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings.

In this case, right-click the link to open it in an Incognito window to view it.

See also the Google documentation on Named Functions.

Summary

Wow! Catching my breath here…

It’s hard to understate how big a development named functions are for Sheets re-usability. I’m super excited to see people use this functionality and to watch it develop further over the coming years.

I’m also super excited to see what people do with the other 9 new functions introduced to Google Sheets!

21 thoughts on “A Guide To Named Functions In Google Sheets (New For 2022!)”

  1. This is very cool. Thanks for the great article.

    What do you think of creating a place where all of us can share the Named Functions we create–well, the ones that would interest others, including the ones above. It could be a great resource to be able to share not only within our organization, but also among all your 40k readers. Also another reason for others to come to your site.

  2. I like creating custom functions using Apps Script. This seems like an easier way to do that. I’m hoping that, unlike functions created with Apps Script, named functions will not set off scary alarms when shared.

  3. It’s more like Timmy getting to open his presents a week before Christmas and showing off his new toys.

    No fair! ☹

  4. I’m still waiting to see the functionality show up. Big question regarding imports is if it is a ‘live’ import or a copy operation. In other words: if a bug is fixed in the ‘master sheet’ (I expect people to start creating repositories from day 1) do all the copies update of do the need to be re-imported?

    In the second case, there is a version management problem lurking around the corner.

    1. I was also wondering about “version management” items.

      The reuse part sounds great, but is there an easy way to see “where is this function being used”, so if you update / modify a function you have some idea of what other sheets to check (to see if you broke something there)?
      And does the “final notebook” indicate somewhere that the underlying named functions were changed (maybe in the doc history or similar)? And maybe an option to “roll back” to the previous version (or see/review the change)?
      Without that I could imagine some mysterious / hard to figure out bugs happening if user b “fixes” named functions used by others.

      1. Hi E Lee,

        Not at this stage as far as I know. This is version 1 though, so I think they will try to implement these kinds of things in the future.

        Cheers,
        Ben

    2. Hi Schmolle,

      It’s a copy at this stage, not a live import. But I think they want to work towards that functionality in the future. Hope so!

      For now, you’ll want to test functions extensively before rolling them out across an entire org.

      Cheers,
      Ben

      1. It is generally good advice to always be very, very careful when rolling out homegrown tools at scale. (and see about a panic button / kill switch)

        That said, it seems to me to be a very common use case to reuse one’s own bag of tricks across multiple, if not dozens or more of your own sheets. In other words, there is a lot of reuse I can think of before the first share to another user. And managing copies is likely to get old very quickly.

        The identical problem exists for AppScript homegrown functions; it isn’t trivial, but at least there you can build a custom plugin as a function repo with update functionality.

        Oh, well, it is early days. We’ll see what the user base will bubble up to the dev team…

  5. This is a very nice upgrade in order to share useful work.
    I hope the sharing method will use from a file to from a searchable library, so we don’t have to know in which file the desired formula is.
    As always, top content Ben, thank you very much

  6. HI Am using personal gmail account and using sheets, I dont see option named function in data, should I upgrade my account to use the same please.

    This looks interesting.

  7. They appeared to me a couple of days ago, amazing feature. I converted a bunch of sections of a complex sheet to using them, and today they’ve disappeared and I can’t use them anymore!!!! They must have reverted the rollout for some reason, but now my sheet if completely broken 🙁

  8. If a USER on enterprise level uses this named function feature and links multiple own & shared drive sheets.
    Later one if that USER is deleted including his/her gdrive.
    Will it impact linked sheets?

  9. I couldn’t find how to add a optional input value to a named function, like CONTAINS(search_key,array,[option]). Have you found if it’s possible?

    1. It doesn’t appear to be possible, however if you force an input of something like false, or double quotes, you could drive an if statement off of that. It’s not ideal, but it’s still pretty cool.

      Array params are the biggest need in my opinion (sum(A1:B4,D:D)) etc.)

  10. My named functions don’t appear to work for shared spreadsheets, as in that the formulas don’t compute if opened by anyone else than the one who created the function. Have anyone found a way around that?

Leave a Reply

Your email address will not be published.