[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.
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.
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”.
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"))
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.
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.
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.
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)
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.
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.”
You will see the same screens as the initial setup of the feature. Make your settings, select “Next” and then click “Update”.
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.”
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.
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”.
Use the tabs at the top of the popup window to locate the workbook containing the custom function and select “Select”.
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.
Imported functions are displayed 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 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]
