How to Create Custom Named Functions in Google Sheets

There are 513 built-in functions in Google Sheets at the time of this writing. But what if you needed a custom function that wasn't included?

In this tutorial, I'll show you how to create and use your own custom function in Google Sheets.

Why would you need a custom function? There could be many reasons. It could minimize typing if you have a lot of instances where you need a few things calculated. It can tidy up an otherwise messy arrangement of manually typed formulas. You may just want to flex your spreadsheet muscles.

I'll walk through a simple example below where we find the percent change between two numbers. I've written extensively about 5 ways to do this particular exercise in another post here.

Percent Change Example

First, here's a video walkthrough of creating custom named functions if you'd like to see a live demonstration.

Here's the demo spreadsheet.

To find the percent change between two numbers, we need to make a simple calculation by dividing the difference between them by the first number.

If we have the following table of sales data in columns E, F and G, we can find the percent change between the years 1 and 2 by manually writing the formula (F7-E7)/E7. And in the same way for years 2 to 3 by writing (G7-F7)/F7.

This is fine and good. But, Google Sheets allows for custom functions that we can define once and then use without having to manually type as much. For this example, it's quite simple, but for more complex formulas, it can save a lot of time and mistakes.

How to Create a Custom Named Function

To access named functions, click Data, Named functions from the toolbar.

Select Add new function from the bottom:

And from here we can fill in the details of our new function starting with the name and the description:

Next we have optional argument placeholders. For our function, we need two of these: one for each year.

Then we fill in the actual formula definition. This is where we describe what we need the formula to do. We use the argument placeholders like variables instead of the specific cell references:

And in the final menu we can add additional details describing and giving examples of our arguments. This can be as detailed or sparse as need be. In our case, I filled in only the bare minimum:

Check it out! Now we have a working custom named function to find the percent change between two numbers. Instead of having to enter the whole formula, now we just enter the cells for year1 and year2:

Summary

The built in functions in Google Sheets can take you far, but if you ever have the need to define a unique function of your own, now you can.

I hope this has been helpful for you!

Please check out my YouTube channel here & LinkedIn page here.

Have a great one!