HomeTechnologyNewsHow to create custom functions in Google Sheets

How to create custom functions in Google Sheets

- Advertisement -
- Advertisement -
- Advertisement -
- Advertisement -

[ad_1]

While Google Sheets gives you hundreds of features, it also lets you create your own. Before, you could only do this using Apps Script. Now, there’s an easy-to-use tool in your spreadsheet called Named Functions.

Using named functions, you title your function, give it a description, add argument placeholders, and enter definitions. You can then use your custom functions on your workbook sheets and import them into other Google Sheets workbooks.

Create a named function in Google Sheets

To easily demonstrate how each of the configuration items works, we’ll walk through the process with an example. We will create a function that tells us whether or not to apply a bonus to our departments based on the total sales.

Since the current array formula we’re using is long, we’ll create a simplified version with our own custom function.

Note: Please note that our example is just for this guide. You can set any kind of function you want.

To get started, open a sheet and go to the Data tab. Select “Named Functions” which opens the sidebar where you will create your function.

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

Named functions in the Data menu with the sidebar

Start by entering 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 description of the role. Although optional, this is displayed in the Help box for the feature which is helpful to both you and your collaborators.

New name and description of the function

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

Examples include value, cell, range, value1, cell2, and range3. As you type each placeholder, it is displayed directly below it in color to help you add the formula definition. For our example, we simply add “range”.

Argument Placeholder Section

To finish 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 earlier within the formula by entering or selecting them.

Below is the definition of the formula for our example. As you can see, we include the range argument which is the only argument we will need to enter for our custom function.

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

Formula definition in the sidebar

Click Next.”

On the next preview screen, you can add more details to your function for the Help box. This includes a description and an example of each argument. You can see what we included in the screenshot below.

Feature Preview in Sidebar

Click “Create” to save your new role.

You will then be taken to the main Named Features sidebar where you will see your new feature listed. If you walk through the sample provided by Google Sheets when you open the sidebar, you’ll also see this feature.

Named Feature Sidebar

Using your named function

Now it’s time to test your new feature. Add an equals sign and the name of your function followed by the arguments.

Enter the new function and formula.

Finish your formula, press Enter or Return, and confirm that it works as expected. As you can see here, we entered our simplified array formula (which is shorter and less complicated) with our custom function and received the expected results:

=BONUS(D2:D6)

Results of the new function and formula

If you open the Help box, as you can with all Google Sheets functions using the blue question mark, you’ll see the function information you entered earlier.

Help box for new feature

Edit or delete 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 feature in the sidebar and choose “Edit.”

Edit a named function

You will see the same screens as the initial setup of the feature. Make your settings, select “Next” and then click “Update”.

Update button after editing a feature

Your sheet automatically updates to follow your changes.

You can also delete a named function if you use one to test the function or just don’t want one you’ve created. Select the three dots to the right in the Named Features sidebar and choose “Delete.”

Delete a named function

You may need to adjust your sheet if you have a formula for the removed function. Should you see the #NAME? error in the cell once the function is removed, like our screenshot below, where we remove MY_FUNCTION6.

NAME error for a deleted custom function

Import named functions into other workbooks

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

RELATED: How to import different types of files into Google Sheets

Open a sheet in the workbook on which you want to use the named function. Go to Data > Named Functions to open the sidebar and select “Import Function”.

Import function in the sidebar

Use the tabs at the top of the popup window to locate the workbook containing the custom function and select “Select”.

Locations to import a function

You will see a window open showing all the named functions in that workbook. Use the check marks to select the ones you want and click “Import” or click “Import All” to select them all.

Functions available for import

Imported functions are displayed in the Named Functions sidebar and are available for use in your workbook.

Function imported and displayed in the sidebar

If you edit a named function that you imported from another sheet, the changes Do not do sync with the other sheet. You can import the updated function to your other sheet or manually make the changes there as well.

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

Perhaps you have been using Apps Script with JavaScript to create your own custom functions. Or maybe, you’re completely new to doing a feature. Either way, the Named Functions tool is a great and useful feature of Google Sheets. Give it a try!

[ad_2]

- Advertisement -
- Advertisement -
Stay Connected
16,985FansLike
2,458FollowersFollow
61,453SubscribersSubscribe
Must Read
- Advertisement -
Related News
- Advertisement -