The Google Workspace blog announced a new Sheets feature that allows you to create custom functions that can be reused across files. The functionality, called “Named Functions,” is similar to Named Ranges in which you can select a specific range of cells and give them a unique name to reference them by in a formula.
Named functions can be imported from existing Sheets files, allowing for more flexibility when using complex formulas and ultimately simplifying the process. The feature builds upon the recent intelligent corrections and formula suggestions enhancements in Google Sheets.
To create a new Named Functions in Sheets, go to Data > Named functions > Add new function > enter details > Next > Create. Once the custom function has been created, you can then enter it into a Sheet’s cell to use it. Note that Named Functions that are created in a file are available only within that file and will need to be imported into the new file in order to use it. You can do this by selecting Data > Named functions > import function > and picking the function you want. In addition, the below new formulas are now available in Sheets to make your formulas even more powerful:
• LAMBDA: Creates and returns a custom function, given a set of names and a formula_expression which uses them. The formula_expression can be calculated by calling the returned function with as many values as the names declared.
• Lambda Helper Functions – MAP, REDUCE, BYROW, BYCOL, SCAN, MAKEARRAY: Advanced array-operating functions which accept a reusable lambda as an argument along with an input array(s).
• XLOOKUP: Returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match.
• XMATCH: Returns the relative position of an item in an array or range that matches a specified value. XMATCH supports enhanced match and search functionality and allows wildcard matches with a question mark (?) or asterisk (*).
These changes will have no Admin impact and began rolling out yesterday, August 24th. Rapid and Scheduled Release domains will start to see the new feature show up gradually for up to two weeks. The below Google Workspace tiers are eligible:
• Available to: Google Workspace Essentials, Business Starter, Business Standard, Business Plus, Enterprise Essentials, Enterprise Standard, Enterprise Plus, Education Fundamentals, Education Plus customers, and users with personal Google Accounts
• Not available to: Frontline, Nonprofits, as well as legacy G Suite Basic and Business customers
It’s great to see these new features being made available to users with personal accounts, not just corporate users. I can see myself using Named Functions if I’m reusing the same formula repeatedly, even for simple Sheets like a budget or an invoice. I hope this is a trend that continues.