1. The simplest way to do a function in Google Sheets: Using Built-in Functions: Start with an equal sign, e.g., =SUM(), =IF(), =AVERAGE() > Add the required arguments or variables within parentheses > Press Enter to execute the function and display the result.
2. Another way to do a function in Google Sheets: Using Formulas: Formulas begin with an equal sign and require you to define the entire logic, e.g., =IF(A1>10, “Yes”, “No”).
3. Another way to do a function in Google Sheets: Using Custom Functions: Write your custom formula and validate it > Click “Add new Function” (Ctrl + Alt + N) when your formula is open > Provide a name for your function > Write a description of the function’s purpose > Define placeholders for user input (optional) > Specify the formula definition for user guidance (optional) > Review the function details and click “Create“.
Google Sheets is a powerful spreadsheet software that performs complex computations on the data using so many built-in features, formulas, functions, Apps script, extensions, and so many things. Today, we will learn how to do a function in Google Sheets. We will learn what are functions, how to use them, what are the limitations of a function, and how to do a custom function in Google Sheets.
A formula is a valid logical or mathematical expression that is used to tell Google Sheets to perform certain actions and bring a result based on the calculation we directed using that formula. Similarly, a function is also a formula but it’s pre-defined by Google Sheets and we can use them directly with function names instead of defining the entire logic.
So, functions are of many types, in Google Sheets, we have numerous functions, some of which we use in our day-to-day tasks and some are used uncommonly. We can also make a custom formula and make it a named function to use frequently.
Table of Contents
Importance of using Named Function in Google Sheets
What makes Google Sheets a fully dynamic and interactive application is its Functions and Formulas. The difference between a formula and a function is that the formulas can be written with valid logic according to user needs, whereas functions are pre-built formulas that exist in Google Sheets. Google Sheets has already made some functions using some logic. Functions can be used to save time, reduce the error possibility, and perform repetitive work.
For example, when you need to add a row having some data using a formula, you can use the following custom formula into a cell.
The above formula will give you a SUM of all these cells’ values.
When you don’t want to use a formula, then you can use the following function to perform the same task.
You can see the difference in the syntaxes of the function and formula. The functions are very short syntax and have almost zero chances of error, while the formula has a long syntax with so many cell addresses and has more chances of errors.
How to do a Named Function in Google Sheets?
We will learn to use normal functions in Google Sheets, then we will use custom formulas where functions are not enough, and we will do a function from a custom formula and save it as a named function. This is very helpful when we need to use a custom formula again and again in our sheets file.
How to do a Function in Google Sheets – Normal Functions in Sheets
In this section, we will learn how to do a function in Google Sheets, and we will specifically see normal functions in Sheets. This way we can get started with the functions and we can understand the workflow of functions.
Suppose we want to perform simple math operations, now we have a SUM function using which we can perform Addition and Subtraction.
Write the Sum Function
Note: Every built-in function starts with an equal sign. =SUM, =IF, =QUERY etc.
Pass the values, if you want to add, pass comma-separated values, or else pass the values with their operation sign such as + or –
Note: After the function name, we need to write opening and closing round brackets for the function.
=SUM(), =IF(), = or QUERY()
Here is the result, you can see how functions work in Sheets.
Note: Every function has a fixed syntax, and we cannot use more than the allowed variables.
Let’s see another function.
Let’s see the =COUNT function.
Write the function name, then pass the variables, press Enter key, and here is your result
So, these are some basic Google Sheets functions. They work with their allowed syntax only; we cannot add custom variables to them.
On the other side, we have formulas. Let’s see them.
How to do a Function in Google Sheets – Formulas in Sheets
In this section, we will see how to do a function in Google Sheets, and we will see how to use formulas in Google Sheets. Formulas and functions are the same as logic. But functions are bounded and formulas are custom. We can call functions with their names and they have defined tasks, for example, a function named Doctor will always work for your medical problems, and a function named Driver will always drive. But formulas do not have defined tasks. Formulas don’t have any name; they need to be defined every time and they don’t have limited variables like functions.
If you don’t want to use the functions, you can use formulas but you will have to define what these formulas will do.
Note: Custom formulas can have functions inside them.
Let’s say you have a complex problem, so you can write a custom formula using some functions inside it.
=IF(AND(B2=true,C2=True,D2=True,E2=True),”Sit in Exams”,IFS(B2=FALSE,”Get your admit card”,C2=false,”Submit your fees”,D2=False,”Receive your date sheet”,E2=False,”Clear your pre-requisites”))
This is a custom formula that solves a complex problem with countless variables.
But the problem is we have to write it again and again whenever we need it.
Google Sheets have recently added a new feature to add custom formulas in the sheets and use them as normal functions.
How to do a Function in Google Sheets – Named Functions
In this section, we will learn how to do a function in Google Sheets, and how to make them reusable using the newly added feature of Google Sheets. Google Sheets has recently added a new feature just like named ranges, now we can also use named functions to change our custom formulas into reusable functions just like normal functions. I have shown you the differences between these two and now it will be very easy to understand the usage and need of the Named Function feature. Let’s get started with it.
The first step is to write your custom formula and validate it by performing it on some data.
When your formula is open, you will see a suggestion below “Add new Function” keyboard shortcut is Ctrl + Alt + N click on it
A “New Named Function” sidebar will be opened on the left-hand side of your sheet, here you have many options.
The first and most important input is the name of the function. You are making it a function so you have to write a meaningful name here. Now, it depends on the working of the formula, based on what your formula do you can write a name here. This is a mandatory field.
My custom formula is to perform a nested if else function, so I will write the name Nested If Else
Note: You cannot use any special character other than underscore (_) in your named function name.
Below you have another input box, where you need to write a description for this function. This will also appear in the about section of your function when you use it in the future. This is also a mandatory field. I am writing a suitable description here based on my formula works.
It takes multiple inputs and based on each it prints a custom message, you can define input values and also can change custom messages
Now we have an optional field. Here we can define the placeholders for the variables a user is going to enter. We can write cell_values, positions_to_check, custom_message, and similar placeholders here.
Note: Placeholders are not supposed to be equivalent to the function name or its working. You can skip this field if you don’t want to show any placeholders.
Here you can write the formula definition. It is the text that helps a user when using this function. When you use any built-in function in Google Sheets it helps you pass arguments, tell you about your data validation, and also shows errors if something is wrong. You can use this field to specify all these things, or else you can simply keep it as default.
Click on the Next button
Now you will see a final preview of your named function, you can review it and then click on Create button below.
The function is created and now you can use it just like a function with its name starting with an equal sign.
So, this is how to do a function in Google Sheets. The purpose of using a named function is to make your custom formula reusable and use it in the future. A custom formula has so many things behind it, and it’s difficult to write again and again so it’s a better solution from Google Sheets they have added a named function feature that can help in managing custom formulas as functions.
I hope you find this guide helpful.
- A custom formula can be used like a function by saving it as a named function
- It’s recommended to use Formula definition, and placeholders, it makes a function easy to use for beginners.
- You can add as many custom formulas as you want
- You cannot use these named functions to cross sheets, which means you can only access these named functions on the sheets they are created on.
This is all about how to do a function in Google Sheets. In this article, I tried to clarify all the things about a normal function in Google Sheets, I started with simple functions and formulas to make you understand how they work on the very ground level, then we moved next and we learned how to use named functions, why they are important and how they help us.
I hope you find the above explanation useful and that you have learned new things about functions in Google Sheets. Thank you so much, and keep learning with Office Demy.