March 10, 2023

Google Sheets adds powerful new functions for advanced analysis

We explore some new functions added to Google Sheets for advanced analysis and data manipulation

Google Sheets adds powerful new functions for advanced analysis

Written by

Google Sheets has been a go-to collaboration platform for many individuals and businesses for years, offering a convenient and efficient way to create, edit, and share spreadsheets online. Its user-friendly interface, compatibility with other Google Workspace tools, and seamless integration with various third-party applications have made it a favorite among users worldwide.

However, with the recent addition of some new features, Google Sheets has become even more powerful, making it a must-have tool for businesses and individuals alike. These new features not only enhance the platform's functionality but also provide users with greater flexibility and customisation options.

Named function 

Named Functions in Google Sheets let you save and name your custom formulas and re-use them in other Google Sheet files. This feature will be useful for many users, as you can create custom formulas to calculate specific insights unique to your team. It will also help you save time and manual effort as you can easily save and reuse your custom formulas across any Google Sheets file.

LAMBDA 

The LAMBDA function is the basis of the named function above. It allows you to create and return custom functions, 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. 

Data analytics functions 

The functions mentioned here may not be essential for everyone who uses Google Sheets, but they have the potential to significantly alter the way you use the program. These functions are designed for advanced users who are proficient with Google Sheets or other similar database software. They may prove extremely beneficial in streamlining complex tasks, automating repetitive processes, or performing calculations that would otherwise be challenging or time-consuming. However, if you are a beginner or have limited experience with spreadsheets, these expert functions may not be necessary for your day-to-day tasks. Nonetheless, it is always helpful to have an understanding of the full range of features that Google Sheets offers, as you never know when a particular function may come in handy. Here they are:

  • EPOCHTODATE: Converts a Unix epoch timestamp in seconds, milliseconds, or microseconds to a datetime in UTC. 
  • MARGINOFERROR: Calculates the amount of random sampling error given a range of values and a confidence level. 
  • TOROW: Transforms an array or range of cells into a single row. 
  • TOCOL: Transforms an array or range of cells into a single column. 
  • CHOOSEROWS: Creates a new array from the selected rows in the existing range. 
  • CHOOSECOLS: Creates a new array from the selected columns in the existing range. 
  • WRAPROWS: Wraps the provided row or column of cells by rows after a specified number of elements to form a new array. 
  • WRAPCOLS: Wraps the provided row or column of cells by columns after a specified number of elements to form a new array. 
  • VSTACK: Appends ranges vertically and in sequence to return a larger array. 
  • HSTACK: Appends ranges horizontally and in sequence to return a larger array. 
  • LET: Assigns name with the value_expression results and returns the result of the formula_expression. The formula_expression can use the names defined in the scope of the LET function. The value_expressions are evaluated only once in the LET function even if the following value_expressions or the formula_expression use them multiple times. 

The great news is that these features will be available to everyone who uses Sheets regardless of your plan, including personal and work accounts. 

Why Choose a Google Partner for Workspace?

Working with a Google Workspace partner like CloudSmiths, offers many benefits, including expertise, customisation, ongoing support, and cost and time savings. By getting invoiced monthly (instead of putting the charges on your credit card), you can better manage your budget and cash flow. Additionally, Cloudsmiths can assist you with implementing and migrating users, ensuring a smooth transition to Google Workspace. Our Managed Services team also provide ongoing support for any changes or issues that may arise with your system. Overall, partnering with Cloudsmiths can help you maximise the benefits of Google Workspace while minimising the hassle and potential problems that come with implementation and management.

Final thoughts 

The new functions added to Google Sheets have made it an even more powerful tool for advanced analysis and data manipulation. The named function, LAMBDA, and data analytics functions offer advanced capabilities to automate complex tasks and streamline workflows. These functions are especially useful for those who are proficient with Google Sheets or similar database software. However, the best part is that these features are available to all Sheets users, whether they have a personal or work account. With the addition of these new functions, Google Sheets continues to evolve as a go-to platform for collaborative and advanced data analysis.

Start using Google Cloud today

Africa's most accredited Google Cloud Partner

Start using Google Workspace today

Africa's most accredited Google Cloud Partner

Start using Salesforce today

Africa's most experienced Salesforce Partner.

Free Generative AI Workshops

Discover use cases specific to your industry, choose the optimal AI solutions for your business and equip yourself to lead in an AI-driven future.

Latest Posts

Speak to one of our cloud experts today