If you’re like most people, you use Google Sheets to store your data and manage your finances. But what if you want to do more than just crunch numbers? What if you want to create custom functions to help you analyze your data? In this article, we’ll show you how to create custom functions in Google Sheets. First, open up Google Sheets and click on the “File” tab at the top of the screen. Then, click on “New” and select “Function.” Next, enter a name for your function in the “Function Name” field and select the type of function that you want to create from the “Type” dropdown menu. We’ll be creating a simple function to calculate the average price of a product. Now, we need to specify some parameters for our function. In the “Inputs” section, we’ll specify which cells our function will use as input data. We’ll choose cell A1 in our spreadsheet as our input cell and set its value to 1. This will tell our function that we want it to calculate the average price of all products in column A1. In the “Outputs” section, we’ll specify which cells our function will output data into. We’ll choose cell B1 as our output cell and set its value to 0 (zero). This will tell our function that we want it to return an average price for each product in column B1. Finally, we need to specify how often our function should run. We can choose either a fixed frequency or an interval frequency. With a fixed frequency, our function will run every time that it’s triggered (for example, when someone clicks on one of its inputs). With an interval frequency, our function will run every X number of times (for example, every 5 minutes). We’ll choose an interval frequency here because we don’t want ourfunction running constantly – we just want it run once every time ..
While Google Sheets provides you with hundreds of functions, it also allows you to create your own. Before, you could only do this using Apps 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 current array formula we’re using is a lengthy one, we’ll create a simplified version with our own custom function.
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 the equal 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 range 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 can 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 can 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 seeing error messages when 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 can 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 see the #NAME? error in 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 can use it in all sheets in that book. If you want to use the custom function in a different Google Sheets workbook, you can simply import 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 can import the updated function into your other sheet or manually make the changes to it there as well.
Maybe you’ve been using Apps Script with JavaScript to 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!
