# A Guide To Named Functions In Google Sheets

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:

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

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

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

Data > Named functions

#### 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:

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.

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!

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

Much easier!

And, hereâs how the Unpivot named function is defined:

### 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:

And the definition of the named function is:

## Named Functions Template

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.

## 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!

## 30 thoughts on “A Guide To Named Functions In Google Sheets”

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.

1. Sebastian says:

Totally agree to that!

2. Sumesh Agarwal says:

Please do that and share details

2. Mike says:

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. Shay Capehart says:

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

No fair! âč

4. Schmolle says:

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. E Lee says:

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. Ben says:

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. Ben says:

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. Schmolle says:

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. Eudes Grange says:

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. maateeq says:

i don’t see the named function appear in my sheets. probably doesn’t work in some countries!!

1. Ben says:

They’re rolling out over the next 2 weeks, so you should have them soon.

7. Harish says:

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.

1. Ben says:

No need. They’re rolling out over the next 2 weeks, so you should have them soon.

8. Alberto says:

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 đ

1. Ben says:

Hopefully, they’ll be back soon!

9. Aamir says:

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.

10. Andreas says:

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. _ says:

A workaround is to pass an array literal[1] in and check that array’s values.

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

11. Andreas says:

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?

12. Sean says:

Decided to try and rewrite unpivot using new formulas
=ARRAYFORMULA(LAMBDA(range,label,
MAKEARRAY((ROWS(range)-1)*(COLUMNS(range)-1)+1,3,
LAMBDA(lrow,col,
if(lrow=1,if(col=1,TO_TEXT(INDEX(range,1,1)),if(col=2,label,”value”)),
if(col=1,
TO_TEXT(INDEX(range,ROUNDDOWN((lrow+COLUMNS(range)-3)/(COLUMNS(range)-1))+1,1))
,if(col=2,
TO_TEXT(INDEX(range,1,1+if(mod((lrow-1),(COLUMNS(range)-1))=0,(COLUMNS(range)-1),mod((lrow-1),(COLUMNS(range)-1)))))
,if(col=3,
TO_TEXT(INDEX(range,ROUNDDOWN((lrow+COLUMNS(range)-3)/(COLUMNS(range)-1))+1,1+if(mod((lrow-1),(COLUMNS(range)-1))=0,(COLUMNS(range)-1),mod((lrow-1),(COLUMNS(range)-1)))))
,)))
)
)
)
)(A1:K10,”Stat”))

Hi Ben, Thanks for this useful post.
I tried to create a custom Unpivot formula with only 1 selection : the whole table :
And the formula is :

and it works !!!
translate the french formula if not OK
HIH
Best regards

14. Sumesh Agarwal says:

how do we share the named functions created by us with all users in the same domain?

15. Kauph Melcher says:

Hi there!

How to wrap the IMPORTRANGE to named function?

I would like to have a some kind of sippet that contains static link to a specific sheet and range. I was imaging that I could create named function:

which contains:

But it seems that named function needs variable.

It is possible?

1. Balesh Gupta says:

Yes. I am also trying to do the same thing, but not able to get it. Can you please share how it can be done?

16. Stefan says:

Thank you for the detailed tutorial, Ben! I like the Named Function concept for a few reasons, but one in particular is the ability to reuse a formula in many places and only have to update it once.

Sadly, though, in testing, it seems they suffer from the opposite limitation that Custom (AppsScript-based) Functions do: While they can be used in Data Validations rules, they cannot be used in Conditional Formatting rules. (Whereas the opposite is true of Custom Functions).

Attempting to reference a Named Function in a custom Conditional Formatting formula isn’t possible. You just get a red box telling you your formula is invalid. The only way to get rid of the error is to remove the Named Function from the formula.

17. Alex Tester says:

Hi Ben,

Have you found a way to get your named formulae to work with ArrayFormula? E.g.

=ArrayFormula(NAMEDFUNCTION(A1:A))

I haven’t managed to crack this yet.

Thanks for all your great work.