How to Create Named Functions in Google Sheets

In this article, we will explore the exciting world of named functions in Google Sheets. Named functions are a powerful feature that allows users to create custom functions without relying on Google Apps Script. Whether you want to create, use, edit, delete, or import named functions, we’ve got you covered!

Why Named Functions?

Named functions offer a convenient way to create custom functions in Google Sheets. Let’s say you have a complex formula that you use frequently across different workbooks. Instead of rewriting the formula every time, you can create a named function to simplify your workflow.

For example, let’s say you want to number a column in Google Sheets but only in rows where the referred column has any value. You can create a custom function called SEQUENCE_NB to achieve this.

By creating this named function, you can easily reuse it in any sheet, just like any built-in function in Google Sheets. For instance, you can use =SEQUENCE_NB(A2:A) or =SEQUENCE_NB(Sheet2!I:I).

How to Create Named Functions

Creating a named function in Google Sheets is straightforward. Here’s a step-by-step guide:

SEQUENCE_NB (One Argument)

1. Copy the formula you want to convert into a named function.
2. Go to Data > Named functions > Add new function.
3. Enter a function name, such as SEQUENCE_NB. Make sure to avoid using built-in function names, TRUE or FALSE Boolean values, A1 or R1C1 syntax, spaces, special characters (except for underscores), names starting with a number, and names longer than 255 characters.
4. Provide a meaningful description of the function, such as “Returns the sequence of values in a column skipping blanks.”
5. Under argument placeholders, enter the argument name, such as column_ref_1.
6. In the formula definition, paste the copied formula and replace the range reference with the placeholder column_ref_1.
7. Click “Next” and provide additional details for the placeholder name, such as “The sequence based on values in this range.”
8. Finally, provide an example argument (colum_ref_1), such as B2:B10.
9. Click “Create”.

Congratulations! You have created your first named function in Google Sheets. Now you can use this function in any sheet within the workbook.

FLIP_DATA (Two Arguments)

Let’s create another named function called FLIP_DATA, which flips a data range vertically in Google Sheets.

1. Copy the formula that flips the range. For example, =SORT(A2:C5,ROW(A2:A5)*N(A2:A5<>""),0).
2. Go to Data > Named functions > Add new function.
3. Enter the function name, FLIP_DATA.
4. Provide a description of the function, such as “Provided a range, the function returns a vertically flipped data.”
5. Under argument placeholders, add argument names: flip_range and first_col_range.
6. In the formula definition, paste the copied formula and replace the range references with the corresponding placeholders.
7. Click “Next” and provide additional details for the placeholder names, such as “The data range to flip vertically” and “The first column reference in the data range to flip.”
8. Finally, provide examples for the arguments, such as A2:C5 and A2:A5.
9. Click “Create”.

Now you have a handy named function that can flip data in a single or multiple columns range.

Deleting or Modifying Named Functions

To delete or modify named functions in Google Sheets, follow these simple steps:

1. Go to Data > Named functions.
2. You will see a list of all your custom functions.
3. Click the vertical three dots next to a function and choose either “Edit” or “Remove”.

Please note that you can only delete named functions one at a time. There is no option to delete multiple functions simultaneously.

Importing Named Functions from One Workbook to Another

If you have created a set of named functions in one workbook and want to import them into another, here’s how you can do it:

1. Open a new Google Sheets file.
2. Go to Data > Named functions.
3. Click Import function.
4. Select the file where you have the named functions.
5. Choose to import all functions or select specific functions to import.
6. Click Import All or Import for the selected functions.

By following these steps, you can create and reuse named functions across different workbooks, saving valuable time and effort.

Get My Named Functions Created in Google Sheets

Here are some of the named functions I’ve created in Google Sheets:

1. PUNCH_IN_OUT_SAME_ROW: To Copy Punch Out Time to the Punch In Row.
2. COPY_TO_MASTER_SHEET: Combines Data in Multiple Tabs in Google Sheets.
3. MERGE_TABLE_REMOVE_DUPLICATES: Removing Duplicates by Key Column.
4. COMPARE_ALL_COLUMNS: Compares All Columns with Each Other for Duplicates.
5. NEXT_RENEWAL_DATE: Get the Next Renewal Date (Monthly and Yearly).
6. AGE_CALC: To Calculate Age or Duration.
7. REPT_ROWS: Repeats Each Row in a Table by Varying N.
8. GANTT_CHART: An Easy Way to Create Gantt Charts.
9. LIST_ALL_DATES: Populates Dates Between Two or More Given Dates.
10. AT_EACH_CHANGE: Aggregation Results at Value Change Rows.
11. CUSUM_BY_GROUP: Running Total by Group (Item, Month, or Year).
12. SPARKLINE_NEGATIVE_BAR: SPARKLINE for Positive and Negative Bar Graph (Array Formula).
13. REF_SHEET_TABS: Reference a List of Tab Names in Query in Google Sheets.
14. _3D: Creates a 3-D reference in Google Sheets.
15. SPLIT_EXPENSES: Splits group expenses.
16. NUMBERTOWORDS: Converts numbers to words in Google Sheets.
17. CUSTOMTIMESLOTS (new!): Creates custom time slot sequences.
18. TOSENTENCECASE (new!): Converts a specified string to sentence case.

These named functions can provide a powerful boost to your productivity when working with Google Sheets.

Thanks for reading! For more exciting tips and tricks, visit Crawlan.com. Happy Sheets-ing!