Sometimes, 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:
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?
How to install add-ons to Google Sheets
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:
Find duplicates or unique values
Count and sum by colors
Match and merge data
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.
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.
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.
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.
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.
If you prefer built-in number-crunching capabilities…
…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.