Google has recently announced 10 new Google Sheets functions that will be available to all users!
Google Sheets functions are a great way to make the most out of your data in the simplest of ways. With intelligent formulas that do the calculations for you, you can save time and manual effort whilst obtaining the insights you need. With the newest Google Sheets functions that are now available on the app, users have even more opportunities to optimize their data and streamline their data management for a seamless Google Sheets experience.
Without further ado, let’s explore the 10 new functions in Google Sheets and how you can use them in your own data management.
1. Named Functions
Named Functions is perhaps the most exciting new function update from the latest Google Sheets rollout.
The Named Functions feature allows users to create and save their own custom formulas in Google Sheets, and then reuse these across all datasets.
This feature will be great for all users, as you can create your own custom formulas to calculate specific insights unique to your team or role. It will also help you save a huge amount of time and manual effort as you can easily save and then reuse your custom formulas across any Google Sheets file.
In the example below, I have created my own named function to automatically assign the correct quarter label to each month of company revenue.
My named function uses the CONCATENATE formula to assign the correct year and quarter to each month of data. As a result, when I add more rows of data to my spreadsheet, I simply have to input my named function in the ‘Quarter’ column to quickly assign the correct quarter data.
2. LAMBDA Function
The LAMBDA function is the basis of the new Named Functions mentioned above. The LAMBDA function allows you to create custom formulas using custom placeholders, which can then be reused.
The LAMBDA function will be preferable for users who cannot find an existing function to carry out their objectives.
In this example, I have created my own custom formula using LAMBDA to convert Fahrenheit temperatures into Celsius.
The LAMBDA function is also part of the next few functions mentioned below. MAP, REDUCE, SCAN, MAKEARRAY, BYCOL, and BYROW are all considered LAMBDA helper functions.
3. MAP Function
The Google Sheets MAP function allows you to apply a LAMBDA function to an array of cells and then returns the new values in the same dimensions as the original array. Essentially, it allows you to “map out” the new values in the same way as the original values.
The MAP function is useful if you need new values of a dataset whilst keeping the dimensions the same and keeping the original values intact.
In this example, I have used the MAP function to take a set of grades in percentages and adjust them to a simple pass or fail based on my LAMBDA formula. This way, I have the original grade results as percentages and a pass or fail mark in the same table format.
4. REDUCE Function
With the REDUCE function, you can turn a range of values into a single value, by applying a LAMBDA formula.
This function allows users who need to simplify their datasets. Currently, the REDUCE function doesn’t seem to have any unique functionalities that other functions like SUM do not already offer.
In this example, I have used the REDUCE function to total the number of renewals of subscriptions. However, you can do the same with a simpler SUM or SUMIF function.
5. MAKEARRAY Function
The Google Sheets MAKEARRAY function generates an array of a defined size, where each value is calculated by a custom LAMBDA function.
This is a good function for users who may usually use SEQUENCE or RANDARRAY functions; MAKEARRAY allows you to create more complex arrays thanks to the application of your own LAMBDA function.
In this example, I have used the MAKARRAY to quickly create a large table of all multiplications from 1-12.
6. SCAN Function
The SCAN function in Google Sheets scans an array by applying your custom LAMBDA function to each value, moving row by row.
This is a helpful function for those dealing with long rows of data and looking for a way to quickly calculate cumulative totals of data.
In this example, I have used the SCAN function to keep a running total of the number of sales recorded for each sales rep.
7. BYROW Function
The Google Sheets BYROW function allows you to apply a custom LAMBDA function to each row of data and return a single value for each column.
You can use this function if you are dealing with independent rows of data that need to use the same calculation, such as finding an average value across multiple rows.
In this example, I can calculate how many users on average renewed their subscription per month. Using BYROW, I can see that the users in May had a higher renewal percentage than users signing up in other months.
8. BYCOL Function
The BYCOL function is very similar to the BYROW function, just the opposite way around. You can apply a custom LAMBDA function to each column of data, and return a single value for each in a row.
Again, this is great if you’re dealing with multiple independent columns of data where you need to apply the same formula, such as finding an average value across multiple columns.
Similar to the example above, I have used BYCOL to calculate the average percentage of renewals per month. You can see a gradual decline in the percentage of users who continue to renew their subscriptions per month.
9. XMATCH Function
XMATCH is a more powerful and flexible version of the MATCH function.
With XMATCH, you have access to more search mode and match mode options, giving you more flexibility when identifying positions.
The XMATCH function is a great upgrade for users who use the MATCH function regularly and can be used to identify positionings in a range of things.
In the example below, I have used the XMATCH function to identify the positioning of ‘Sealion’ in a list of animals. As you can see, the flexibility of this function is reflected by the various Mode and Search modes.
Context: 0 = exact match, 1 = an exact match or the next value greater than, 2 = wildcard mode; XMATCH allows you to use * for wildcards.
10. XLOOKUP Function
The Google Sheets XLOOKUP function is a more powerful and flexible version of the VLOOKUP function.
With XLOOKUP, you can lookup from the bottom up, to the left, and even use binary search if needed.
This new function is a great addition for those dealing with large datasets who need to search and identify a specific value quickly and easily.
In the example below, I have demonstrated the difference between XLOOKIP and VLOOKUP. As you can see, I must add the ‘ID’ column to the far left column when using VLOOKUP as it cannot look to the left. XLOOKUP can, and so it does not limit you to where you place your identifier column.
What’s new in Google Sheets – new functions
And there you have it! These new Google Sheets formulas will be a great addition to any dataset you wish to optimize with intelligent formulas. In particular, the LAMBDA function is a great basis for the majority of these new functions, in which you can create custom formulas to fulfill a specific goal.
We recommend learning and practicing these formulas now in order to make the most of this new update as soon as possible. If you’d like to learn more about spreadsheet functions and the best tips and tricks, look at the spreadsheet category on the Sheetgo blog page.
Alternatively, check out our related blog posts below!
