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.
Right-click on this function and choose:
View more cell actions > Define named function
Or go to the menu:
Data > Named functions
In the sidebar window, select: Add new function
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.
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.”
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
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.
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
(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.
Open a brand new Google Sheet by typing sheet.new into your browser
Go to the menu: Data > Named functions
Select Import function
From the popup file picker, select the Sheet where you created the named function in steps 1 – 9 above.
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
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:
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:
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.
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).
Thankfully we can turn this into a named function and then use that instead:
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:
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.
See also the Google documentation on Named Functions.
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!