June 7, 2024
This innovation has already been described by global experts as the biggest revolution in Google Sheets since the introduction of sheet sharing, and I can't help but agree.
The novelty of custom formulas will simplify the creation of increasingly complex tools within what, thanks to these new implementations, confirms itself as the most advanced spreadsheet in the world.
Moreover, if you are not strong in creating complex formulas, this article is for you, as you will be able to comfortably use formulas created by others in your Google Sheets.
By September 15, custom formulas will be released to all Google Sheets accounts, whether they belong to paid Workspace accounts or free Google accounts. So if your account doesn't have this integration yet, you'll just need to be patient. Custom functions are a very simple tool to use that allows the user to create a complex formula and save it as if it were a native Google Sheets function. Let's see how to use them together.
The first step is to create the formula you want to use. In the following example, I created a function that, given a VAT reason and a gross amount, returns the taxable amount and the VAT amount.
In the menu bar, click on the "Data" menu and then on "Named Functions".
When the sidebar opens, click on "Add new function" and start filling in the indicated fields.
Provide the name you want to use to call the function (the name cannot start with a number, contain A1 notations, or contain spaces). Choose a clear and recognizable name that will allow the user to understand the task performed by the function.
Provide a clear and understandable description of what the function does. This description will be shown to users when they use the formula.
We will see how to fill in this point in the next step.
Copy the formula you previously created and paste it inside the box.
Here's an example of how I filled in the function fields for my formula.
The placeholders are "placeholder," keywords that replace all the values written in A1 notation in your formula, allowing the user who will use the formula to enter their own reference values.
In the case of my formula, the placeholders will need to replace the values "B2" and "A2," which respectively referred to the VAT reason and the gross amount.
I will then create 2 placeholders called "gross_amount" and "vat_reason" and replace all the respective values written in A1 notation within the formula with the placeholders.
By clicking "Next," you will reach the last section before being able to use your new custom function. In this section, if you have used argument placeholders as indicated in step 4, you will need to provide a description and an example for each placeholder.
Again, try to be as clear as possible and indicate not only what to enter but also the format in which the value should be entered, for example, text string, numeric, or boolean.
At this point, in your Google Sheet, you can call the newly created function and use it according to the instructions that will be shown to you.
The advantage of using this new Google function is clear:
One wonderful thing is that Google Sheets has made it possible to share custom formulas between sheets. Below you'll find a quick list of the steps needed to import into your Google Sheet functions you've created on different sheets, or that others have created.
Access the sheet where I created the formula.
I wish you good work with the new customizable functions of Google Sheets.