How to Create Custom Functions in Google Sheets

While Google Sheets gives you hundreds of features, you can also create your own. Previously this was only possible with Apps Script. Now your spreadsheet has an easy-to-use tool called Named Functions.

With Named Functions, you name your function, give it a description, add placeholders for arguments, and fill in the definitions. You can then use your custom functions in all worksheets in your workbook and import them into other Google Sheets workbooks.

Create a named function in Google Sheets

To demonstrate in a simple way how each furnishing element works, we will walk through the process with an example. We’ll create a function that will tell us whether or not we should give a bonus to our departments based on total sales.

As the current array formula we are using is very long, let’s create a simplified version using our own custom function.

Note: Remember that our example is only for this guide. You can set up any type of function you want.

First, open a sheet and go to the Data tab. Choose Named Functions, which opens the sidebar where you create your function.

You can select “View Sample” to see one from Google Sheets, or “Add New Feature” to set up your own.

Start typing a name for your function after the equals sign in a cell to start your formula. You can also use the default name provided, which is MY_FUNCTION1.

Then add a feature description. Although optional, this is displayed in the help box for the feature, which is helpful for both you and your collaborators.

Next add your argument placeholders. Although these are also optional, they are required to build most formulas.

Examples are value, cell, range, value1, cell2, and range3. As you type each placeholder, it appears directly below with a color to make adding the formula definition easier. For our example, let’s just add “area”.

To complete the main section, add the formula definition. This is the formula you want to use to define your named function. You can use the placeholders added above within the formula by typing or selecting them.

Below is the formula definition for our example. As you can see, we’re including those range Argument, which is the only argument we need to supply for our custom function.

=ARRAYFORMULA(IF(range>=20000,"Cha-ching", "Boo"))

Click next.”

On the following preview screen you can add more details about your help box function. This includes a description and an example for each argument. You can see what we include in the screenshot below.

Click “Create” to save your new function.

You will then be taken to the main Named Functions sidebar, where you will see your new function listed. If you go through the sample provided by Google Sheets when opening the sidebar, you will see this feature as well.

Using your named function

Now it’s time to test your new feature. Add an equals 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 can see here, we input our simplified array formula (which is shorter and less complicated) with our custom function and get the expected results:

=BONUS(D2:D6)

If you open the help box, as you can do with all Google Sheets functions via the blue question mark, you will see the information about the function entered above.

Edit or remove a named function

If you want to make changes to your function or see error messages when trying 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 will see the same screens as when initially setting up the feature. Make your adjustments, select Next, and then click Update.

Your sheet will automatically update to follow your changes.

You can also remove a named function if you’re using one to test the function or just don’t want one you’ve created. Select the three dots on the right side of the Named Features sidebar and choose Remove.

You may then need to adjust your sheet if you have a formula for the deleted function. You should see the #NAME? Error in cell once function removed as in our screenshot below where we deleted MY_FUNCTION6.

Import named functions into other workbooks

If you create a named function in a workbook, you can use it in all sheets in that book. If you want to use the custom function in another Google Sheets workbook, you can simply import it.

TIED TOGETHER: 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 choose Select.

A window will open showing all named functions in this 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 appear 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 will be do not sync with the other sheet. You can import the updated function to your other sheet or manually make the changes there as well.

Tip: For more information, examples, and limitations when using named functions, see the Google Docs editors help page for the function.

Perhaps you’ve used Apps Script with JavaScript to create your own custom functions. Or maybe you’re completely new to creating a function. Either way, the Named Functions tool is a great, useful Google Sheets feature. Try it!