Named Functions in Google Sheets

Named functions in Google Sheets let you create your own function. You can then use this new function in the same spreadsheet or import and use it in other Google Sheets. If you frequently use the same formulas, you can save time by creating a new Named function. Let’s dive in to see what that means.

Here is the source data we want to summarize:

Make a copy of the spreadsheet with the Named function to follow along.

Contents

1 Write a Complex Formula ONCE
2 Create the Named Function
3 Use The Named Function
4 Result of Named Function
5 Video Tutorial

Write a Complex Formula ONCE

We need a complex formula for inventory analysis that returns a grouped list of each inventory item’s total price and age. You do this same analysis every month with the same formula. This situation is a perfect candidate for a Named function.

The current formula is:

=QUERY(A8:E108,"select A, sum(B)*sum(D), avg(E) group by A label sum(B)*sum(D) 'Total Price', avg(E) 'Days Old' format sum(B)*sum(D) '$#,##0.00', avg(E) '#'")

After we name the function, the new syntax will be:

=INVVALUE(A8:E108)

You can copy the new function into each month’s spreadsheet and reuse it without retyping it. Let’s create the Named function now.

Create the Named Function

First, go to the Data menu and choose Named functions. This menu option opens the Named functions sidebar.

Once this sidebar is open, click on Add new function. Now, you are presented with several choices.

Name and Describe the Named Function

Named function edit window with name and description — Name and Description

Choose a Function name that isn’t already used (like COUNT or SUM). The name needs to be capitalized and can’t start with a number. Let’s call our function INVVALUE for INVentory VALUE.

Now, enter the Function description. As you will see later, Google Sheets will display the Function description when users type the Named function.

Write the Argument Placeholders and Formula Definition

After describing the function, we will define it by pasting it into the Formula definition box. This definition is the formula your Named function will call. This is the last time you will need this formula!

Formula definition and argument placeholders input boxes — Placeholder and Definition

Google Sheets notices the range of A8:E108 inside the formula and suggests that you replace it with an Argument placeholder. We’ll replace the actual range with the placeholder name of range.

When you finish the definitions, click Next. Clicking next will bring you to a screen for additional details.

Add Additional Details

Be sure to fill out these additional details if you want them to show in the formula help when a user types the function.

Input boxes for additional detail for the named function — Additional Details

Next, you will see how Google Sheets shows the argument description and example.

Use The Named Function

Now, you are ready to use the Named function in your Google Sheet.

Result of Named Function

Simple new formula to age our inventory.

Named function output — Named Function Output

After creating the Named function to handle our QUERY, we never have to type that long formula again.