How to use XLMiner Analysis ToolPak add-on for Google Sheets

Excel fans turned Google Sheets users can rejoice. XLMiner Analysis ToolPak is another add-on that increases the power of Google Sheets. When Google first launched Sheets, it changed the game for many spreadsheet users, allowing for real-time collaboration from any device at any time. But as amazing as this new style of spreadsheets was, advanced Excel users were left out of the excitement. This because Google Sheets was pretty basic and did not have some of the advanced features that select users need to process and perform statistical analyses. However, Google Sheets and Excel both changed a lot over the years. Excel has created a collaborative, online component. Whereas Google Sheets has become more advanced to keep up with the competition. Sheets has improved their own features and introduced add-ons, a feature that allows 3rd party developers to create tools that boost the capabilities of Sheets.

Solver created an add-on that solved one of those Google Sheets issues. It mimics the same tool used in Excel and allows users to perform essentially the exact same analyses. This is great for the many users who are making the switch from Excel to Google Sheets because it is free, cloud-based, and so easy to share and collaborate with using Google’s unique sharing permission. There are also many users, like myself, who work across both programs! The ease of being able to switch between programs without having to change the way you operate features is extremely valuable for individuals and teams who need to create and share important data.

Who is using XLMiner Analysis ToolPak?

Statistical analysis is performed by research scientists, analysists, government organizations, non-profits, and private companies. Being able to correctly understand data allows us to make better decisions in finance, investment, production, distribution, scheduling, and more. Users are using this add-on to predict flight delays and even optimize professional football scheduling. How will you use XLMiner Analysis ToolPak to change the world around you?

How to access XLMiner Analysis ToolPak

It is really simple to get add-ons for Sheets! The fastest way to get XLMiner Analysis Toolpak is to click this link that will take you directly to the Google Workspace Marketplace. Simply click the Install button and the add-on will be added to your Google Sheets account.

XLMiner Analysis Toolpak installation
Alternatively, you can get the add-on by following these steps:

  1. Open a Google Sheets file
  2. Click the Add-ons button in the navigation menu
  3. Click the Get add-ons button
  4. A window will pop up for you to browse
  5. Type “XLMiner Analysis ToolPak” in the search box.
  6. Click the Install button to get the add-on

You are now ready to start using XLMiner Analysis ToolPak!

How to use the Add-on

Starting the XLMiner Analysis Toolpak is easy. Simple click Add-ons → XLMiner Analysis ToolPak → Start.

XLMiner Analysis ToolPak 2
Google Sheets will open the add-on, and a toolbar will appear on the right side of your spreadsheet. From this sidebar you can choose from twenty different options to perform statistical analyses on your data:

  • Anova: Single Factor, Two-Factor With Replication, and Two-Factor Without Replication
  • Correlation
  • Covariance
  • Descriptive Statistics
  • Exponential Smoothing
  • F-Test Two-Sample for Variances
  • Fourier Analysis
  • Histogram
  • Linear Regression
  • Logistic Regression
  • Moving Average
  • Random Number Generation
  • Rank and Percentile
  • Sampling
  • t-Test: Paired Two Sample for Means, Two-Sample Assuming Equal Variances, and Two-Sample Assuming Unequal Variances
XLMiner Analysis ToolPak 3
XLMiner Analysis Toolpak is intuitive to use and generates functions just like its Excel counterpart. Let’s go over a simple analysis so you can see the features of the add-on.
Correlation analysis

For this tutorial, we will run a correlation analysis. We are using a simple set of sample data comprised of 3 columns containing various variables. Plug in the input data ranges into the Input Range box (A1:C12).

For this demo, we will group our data by columns, and select the Labels in First Row option. Choose an empty cell range for the data to output and enter it into the Output Range box (here: F1).

It’s that simple! Finally, click the OK button.

XLMiner Analysis ToolPak 4

The add-on will run the statistical analysis for you, and populate output in your desired range, like in the image below.

XLMiner Analysis ToolPak 5
Descriptive analysis

We can repeat the same process to run a descriptive statistical analysis, and have the data output to the same sheet.

XLMiner Analysis ToolPak 6
If you were worried about switching to Google Sheets for fear of losing some of the advanced mathematical capabilities that Excel offers, Solver has created a great solution for you! Not only does it have nearly the same user experience and features that Excel offers, but it also features an extra tool to sweeten the pie: Logistic Regression!

As a result, it will be an easy transition for Excel users to get started using XLMiner Analysis ToolPak. If you are new to statistical analysis, however, check out some of the great community resources available for free.

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

finance-spreadsheet Enhance your productivity

Automate your finance management

You may also like…

🎓MASTERCLASS: Learn how to create a Purchase Order system from scratch