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 SpreadsheetoBut 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.
*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.
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 Spreadsheeto9. 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 Spreadsheeto11. 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
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
To start, click ‘File’ from the tab list.
Open ‘Excel Options’ by clicking ‘Options’ on the left-hand sidebar.
Then, click ‘Add-ins’ (second to the last) on the left sidebar of the window.
In here, you’ll see the different application add-ins you currently have.
What you need to do next is to make sure that ‘Excel Add-ins’ is selected to be managed. Then, click ‘Go’.
The next window shows you the Excel Add-ins available. Check the ‘Analysis ToolPak’ and click ‘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.
After loading the ToolPak, a new category can be seen on the Ribbon — Analysis.
Select ‘Data Analysis’ in this 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 SpreadsheetoThis opens up a new window.
Select your tool of choice by clicking on it. Then, click ‘OK’ to use the tool.
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