Quick Links
While Google Sheets provides you with hundreds offunctions, it also allows you to create your own. Before, you could only do this usingApps Script. Now, there’s a user-friendly tool in your spreadsheet called Named Functions.
Using Named Functions, you title your function, give it a description, add argument placeholders, and enter the definitions. You can then use your custom functions across the sheets in your workbook and import them into other Google Sheets workbooks.
Create a Named Function in Google Sheets
To easily demonstrate how each of the setup elements works, we’ll walk through the process using an example. We’ll create a function that tells us whether or not to apply a bonus to our departments based on total sales.
Because the currentarray formulawe’re using is a lengthy one, we’ll create a simplified version with our own custom function.
Keep in mind that our example is simply for this guide. You can set up any type of function you want.
To get started, open a sheet and head to the Data tab. Select “Named Functions” which opens the sidebar where you’ll create your function.
You can select “View Example” to see one from Google Sheets or choose “Add New Function” to set up your own.
Start by entering a name for your function after theequal sign in a cell to begin your formula. You can also use the default name provided which is MY_FUNCTION1.
Then, add a function description. Although optional, this displays in the Help box for the function which is helpful for both you and your collaborators.
Next, add your Argument Placeholders. While these are also optional, they’re necessary for assembling most formulas.
Examples include value, cell, range, value1, cell2, and range3. As you type each placeholder, it displays directly below with a color to assist in adding the Formula Definition. For our example, we simply add “range.”
To finish off the main area, add the Formula Definition. This is the formula you want to use to define your Named Function. You can use the placeholders you added above within the formula by entering or selecting them.
Below is the formula definition for our example. As you can see, we include the
argument which is the only argument we’ll need to enter for our custom function.
Click “Next.”
On the following preview screen, you can add more details to your function for the Help box. This includes a description and example of each argument. You can see what we include in the screenshot below.
Click “Create” to save your new function.
You’ll then be directed to the main Named Functions sidebar where you’ll see your new function listed. If you walk through the sample provided by Google Sheets when you open the sidebar, you’ll see this function as well.
Using Your Named Function
Now it’s time to test your new function. Add an equal sign and your function name followed by the arguments.
Finish your formula, press Enter or Return, and confirm that it works as expected. As you may see here, we enter our simplified array formula (which is shorter and less complicated) with our custom function and receive the expected results:
If you open the Help box, as you’re able to do with all Google Sheets functions using the question mark in blue, you’ll see the information for the function you entered above.
Edit or Remove a Named Function
If you want to make changes to your function or are seeingerror messageswhen you try to use it, you can edit it. Go to Data > Named Functions. Select the three dots to the right of your function in the sidebar and choose “Edit.”
You’ll see the same screens as the initial setup for the function. Make your adjustments, select “Next,” and then click “Update.”
Your sheet automatically updates to follow your changes.
you’re able to also remove a Named Function if you use one for testing the feature or simply don’t want one you created. Select the three dots to the right in the Named Functions sidebar and pick “Remove.”
You may then need to adjust your sheet if you have a formula for the deleted function. You should seethe #NAME? errorin the cell once the function is removed, like our screenshot below where we deleted MY_FUNCTION6.
Import Named Functions Into Other Workbooks
When you create a Named Function in a workbook, you may use it in all sheets in that book. If you want to use the custom function in a different Google Sheets workbook, you can simplyimport it.
Related:How to Import Different File Types into Google Sheets
Open a sheet in the workbook where you want to use the Named Function. Go to Data > Named Functions to open the sidebar and select “Import Function.”
Use the tabs at the top of the pop-up window to locate the workbook that contains the custom function and pick “Select.”
You’ll see a window open displaying all of the Named Functions in that workbook. Use the checkmarks to select the ones you want and click “Import” or click “Import All” to select them all.
The imported function(s) then display in the Named Functions sidebar and are available for use in your workbook.
If you edit a named function that you imported from another sheet, the changes do not sync with the other sheet. you’re able to import the updated function into your other sheet or manually make the changes to it there as well.
For additional information, examples, and limitations when using Named Functions, check out theGoogle Docs Editors Help pagefor the feature.
Maybe you’ve been using Apps Script withJavaScriptto create your own custom functions. Or maybe, you’re completely new to making a function. Either way, the Named Functions tool is a terrific, useful Google Sheets feature. Give it a try!
