How To Add The Analysis ToolPak in Excel
[Step–by–Step Guide]

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

The Analysis ToolPak is one of Excel’s most beloved ‘Application Add-ins’.

And for a good reason! This add-in, with its financial, statistical, and engineering data analysis tools, can save you a great deal of time.

All you have to do is simple: provide the data and parameters and let the appropriate tools do the work. You’ll then have your results in an output table (and sometimes, in charts).

Kasper Langmann, Co-founder of Spreadsheeto

But before you can use its tools, you’ll have to load it first. (Don’t worry, it’s super easy! 😊)

Let’s look first at the different tools the Analysis ToolPak provides and we’ll then jump into how to load the add-in.

Table of Content

Data Analysis Tools

There are 15 tools you can use:

1. Anova

ANOVA (Analysis of Variance) is a variance tool to develop and confirm an explanation for the observed data.

There are 3 types of Anova:

  • Single Factor
  • Two-Factor With Replication
  • Two-Factor Without Replication

2. Correlation

The correlation analysis tool provides an output table and a matrix to know whether 2 variables tend to move together.

3. Covariance

Covariance is usually used in tandem with the correlation analysis tool especially when you have lots of different measurement variables on a set.

4. Descriptive Statistics

This tool provides information and data about the central tendency and variability of your data.

5. Exponential Smoothing

The Exponential Smoothing tool smooths time-series data through an exponential window function.

6. F-Test Two-Sample for Variances

This tool tests the null hypothesis that the samples from two distributions are equal variances.

7. Fourier Analysis

The Fourier Analysis tool breaks down a harmonic series into its most basic components using sinusoidal functions and analyzes the periodic data.

8. Histogram

A Histogram represents the distribution of numerical data.

Starting with Excel 2016, you can create a Histogram easily without loading the Analysis ToolPak.
Kasper Langmann, Co-founder of Spreadsheeto

9. Moving Average

This is a technical analysis tool usually used in sales which constantly updates the average price. It works by unmasking the noise brought about by random short-term fluctuations.

10. Random Number Generation

The Random Number Generation analysis tool lets you generate random numbers with respect to a number of variables and other options.

In addition, there are two different functions in Excel you can use to create a random number generator in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

11. Rank and Percentile

This tool outputs a table with the ordinal and percentage rank the values in a data set.

12. Regression

This is a linear regression analysis tool that analyzes the effect of different variables on a single dependent variable.

13. Sampling 

The Sampling tool is useful in performing an analysis of a smaller sample of your data and look for variations. It treats the input range (the sample) as a population.

14. t-Test

The t-Test tool tests the equality of the population means in a sample.

There are three types of t-Test:

  • Paired Two Sample For Means
  • Two-Sample Assuming Equal Variances
  • Two-Sample Assuming Unequal Variances

15. z-Test: Two Sample for Means

This statistical tool is useful in testing the null hypothesis that the population means of two populations are different considering the variances are known the sample size is big.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to Load the ToolPak

Loading the ToolPak is as easy as 1 2 3.

You literally only need 5 clicks to load the Analysis ToolPak.

Kasper Langmann, Co-founder of Spreadsheeto
loading the analysis toolpak in 5 clicks

To start, click ‘File’ from the tab list.

file from the tab list

Open ‘Excel Options’ by clicking ‘Options’ on the left-hand sidebar.

select options from the file tab

Then, click ‘Add-ins’ (second to the last) on the left sidebar of the window.

click add-ins on excel option window

In here, you’ll see the different application add-ins you currently have.

listed application add-ins in excel

What you need to do next is to make sure that ‘Excel Add-ins’ is selected to be managed. Then, click ‘Go’.

manage excel add-ins and go

The next window shows you the Excel Add-ins available. Check the ‘Analysis ToolPak’ and click ‘OK’.

select the add-in available and press ok

Congratulations! The Analysis ToolPak is now loaded. You can now use the different tools the add-in offers.

Using the ToolPak

To use a tool, click ‘Data’ from the tab list.

data from the tab list

After loading the ToolPak, a new category can be seen on the Ribbon — Analysis.

Select ‘Data Analysis’ in this category.

data analysis button on analysis category

If you can’t find this category, the Analysis ToolPak isn’t loaded yet. Follow the instructions written above before you attempt to use the tools in the ToolPak.

Kasper Langmann, Co-founder of Spreadsheeto

This opens up a new window.

Select your tool of choice by clicking on it. Then, click ‘OK’ to use the tool.

select the analysis tool to load and press ok

Conclusion

The Data Analysis ToolPak is a great addition to Excel’s default powerful tools. However, you have to learn how to load it first since the ToolPak is not automatically loaded.

Also, the functions in the ToolPak can only be used on one worksheet at a time. That’s why you’ll have to learn how to load this every time you need it.

Kasper Langmann, Co-founder of Spreadsheeto