Join us on SlackTry for freeSometimes, Google Sheets is missing the perfect feature for your data analysis needs. However, here are some number-crunching add-ons to up your data analysis in Google Sheets.
As you would see from the wide variety of tutorials that we already have in our library, you can do almost anythingwith Google Sheets, from simple calculations to advanced data analysis! Sometimes, the exact feature that you need is either not possible within the current design of Google Sheets or it requires you to code it via the Google Apps Script. For these cases, there is a third option: Google Sheets add-ons! Google Sheets has a wide variety of add-ons that you can install in order to add functionality to any spreadsheet. Some of the additional functionalities that add-ons can provide are the following:
Google Sheets has an impressive arsenal of functions for number-crunching. Some of them are the following:
If the kind of number-crunching you need cannot be suitably done using the existing functions, you can define new ones via the Google Apps Script. This, however, requires intimate understanding of the number-crunching you need and how it is best implemented. In short, you need to be a good programmer and have an advanced command of mathematics beyond those functions to be able to define new functions via the Google Apps Script!
We always have the Google Sheets add-ons as the third option. In this article, we will look at some of the best add-ons for number-crunching in Google Sheets, and see how they work. Are you ready?
To access Google Sheets add-ons, click Extensions in the main toolbar, then Add-ons, then Get add-ons.
Google Workspace Marketplace will be loaded.
The Google Workspace Marketplace is where add-ons can be accessed and then added to your Google Sheets spreadsheets. They are third-party apps that add functionality to Google services such as Google Sheets. Here are the steps, using SyncWith | Any API as an example (we will feature this add-on in this article):
Step 1: Click on an add-on, then the page containing the description of the add-on will appear. To install it, click the Install button.
Step 2: A small box with the label Get ready to install will appear. Click Continue.
Step 3: A new window will appear, asking you to select the Google account where you want to use the add-on.
After selecting the account, the list of permissions will appear. Click Allow.
The add-on will now be listed under Extensions. To select the add-on to use, simply go to the Extensions option in the main toolbar and click the add-on.
Now that we know how to install an add-on to Google Sheets, let’s explore some of the best add-ons for crunching numbers in Google Sheets.
Used by more than six million users, Power Tools contains more than 30 common spreadsheet actions that would otherwise take more than a few clicks to do. This includes both spreadsheet formatting and basic number-crunching.
Some of the number-crunching actions include the following:
These are relatively simple actions that you can already implement with the current toolset of Google Sheets, but Power Tools allows the user to easily do so with more functionality.
Learn more: Power Tools for Google Sheets
With almost 2.5 million users, XLMIner Analysis ToolPak (first available in Microsoft Excel) offers up to 30 statistical functions that you can use in your data analysis. The functions range from basic descriptive statistics to more advanced statistical functions such as ANOVA and correlation.
For each listed function, there are settings you need to set such as the input range and where you want to insert the output. You can specify a single cell in the output range and it will serve as its upper-leftmost cell.
For example, when you choose Descriptive Statistics, you will get the following output:
You can select several columns of data and the add-on will automatically collect all the relevant metrics for each column. Some of the functions are not available in Google Sheets, and the add-on allows for quick and convenient calculations of the relevant statistical functions and methods for those that can be done otherwise but would require more steps from the user.
Learn more: XLMiner Analysis ToolPak | solver
With more than 1.8 million users, Goal Seek is a unique add-on to Google Sheets for predicting the required input for a given formula and output. You need to select the cell containing the formula, the output value you want, and the cells from where the inputs to the formula will come. Goal Seek runs an algorithm that gives an approximate value for a specified level of tolerance.
An example is shown above. This is why even with simple formulas that you may even easily figure out the answer, Goal Seek will output a value with decimals. This is not a problem because you will mostly use Goal Seek for complicated formulas.
Learn more: Goal Seek - Google Workspace Marketplace
With almost 1 million users, EZ Query is a handy tool for querying in Google Sheets. Its main strength is that it helps you assemble QUERY functions without having to write the formula yourself.
Adding filters to the QUERY function can be a bit complicated, but EZ Query allows you to add filters more easily.
The sample output can be seen below:
Since the output is in the form of QUERY function, you can modify it without having to open the add-on.
Learn more: EZ Query - Google Workspace Marketplace
Google Sheets already offers an impressive set of chart types that you can use to present a diverse range of data types. However, a specific chart may not be available by default in Google Sheets. For these cases, ChartExpo comes to the rescue! Its 44,000 users serve as a testament to the diversity of chart types it offers as an add-on to your Google Sheets spreadsheets.
Unlike in some of our tutorials where we worked around the existing features of Google Sheets (examples are the funnel charts and gantt charts), the specific chart types that would otherwise be lumped with the basic classification used in Google Sheets are separately listed on ChartExpo.
The typical settings for creating charts are also offered, but with a more user-friendly interface.
The output is shown below.
The full features of ChartExpo are available as a free trial for the first seven days, after which you need to pay $10/mo to maintain access to the full features.
Learn more: ChartExpo™ Help
With 370,000 users, Text Analytics is the most popular add-on for analyzing text data from different platforms, most prominently from Twitter. It offers the following features:
Learn more: Text Analytics and Sentiment Analysis for Google Sheets
With 136,000 users, Logic Sheet is an all-in-one data processing and data analytics automation tool for Google Sheets. It allows you to import data, clean it, and analyze it. If you do not need the level of sophistication offered by XLMiner Analysis Toolpack but also need data munging tools, then Logic Sheet could be the best fit for you.
For data analysis, it contains the most common methods such as descriptive statistics, correlation, and other tests.
For the example of descriptive statistics, the values are automatically calculated for each column.
Learn more: Logic Sheet
The JOIN command is not included among the wide range of SQL commands that Google Sheets offers. It is one of the most important commands in SQL for consolidating two or more datasets for data analysis. With more than 25,000 users, Bjorn’s Data Join Studio allows you to do so in Google Sheets. The add-on launches in a pop-up window where you can add the data sources (can be the sheets in the Google Sheets or a file in your computer) and then join the selected columns.
Learn more: Bjorn's Data Join Studio
…try Lido. It contains advanced number-crunching capabilities that you will find useful for your data analytics. Most of all, you don’t need to have an advanced knowledge of software engineering to properly construct and use them. Start using it now.
