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.
Make a copy of the spreadsheet with the Named function to follow along.
Write a Complex Formula ONCE
Let’s say 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:
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
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!
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
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.
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.