Excel Data Analysis ToolPak: how to enable it, the 19 tools, and one worked run

The Excel Data Analysis ToolPak is a free add-in that puts a Data Analysis button on the Data tab. On Windows, enable it with File > Options > Add-ins, Manage: Excel Add-ins, Go, then check Analysis ToolPak and click OK; on a Mac, use Tools > Excel Add-ins. It then runs 19 statistical tools, from Descriptive Statistics to Regression, and writes each result as a block of values.

How to enable the ToolPak on Windows and Mac

The click paths, from Microsoft's page on loading the Analysis ToolPak:

Windows: File > Options > Add-ins
         Manage: Excel Add-ins > Go
         check Analysis ToolPak > OK

Mac:     Tools > Excel Add-ins
         check Analysis ToolPak > OK

Data Analysis then appears at the right end of the Data tab. If Analysis ToolPak is not in the list, click Browse to locate it; if Excel says it is not installed, click Yes to install it, then restart Excel.

Two things trip people up. Analysis ToolPak - VBA is a separate entry that exposes the functions to macros; checking only that one does not add the button. And Microsoft documents the ToolPak for Excel on Windows and Mac only, so for Excel for the web plan on the desktop app, or use worksheet functions in the browser.

What is in it: the 19 tools

The Microsoft Support guide to the ToolPak lists these tools:

Regression has its own page: regression analysis in Excel runs the tool on a worked table and reads every line of its output. A full guide to reading descriptive statistics will follow as its own page; the run below is the short version.

A worked run: Descriptive Statistics on ten invoices

Ten invoice values from one month's sales ledger, in A1:A11 with the header "Invoice (USD)" in A1:

Data > Data Analysis > Descriptive Statistics. Input Range A1:A11, Grouped By Columns, check Labels in first row, pick an output cell, check Summary statistics, OK. The output:

Standard Error is the standard deviation divided by the square root of the count. Mode is the most frequent value, here the two invoices of 8,900. Kurtosis and Skewness describe shape: positive skewness means a long tail to the right.

Reading the output: which numbers matter

Mean against median first. The mean is $16,100 and the median $11,800: the mean sits $4,300 higher, and a gap that size says one or two large values are pulling it. Here it is a single invoice of $47,200, which also drives the skewness of 2.55; SKEW is positive when the tail extends toward larger values.

Take that one invoice out and the other nine have a mean of $12,644.44, a median of $11,200 and a standard deviation of $4,279.93, against $11,648.65 with it. One order nearly tripled the spread. Whether to keep it is a business question, covered in outliers in sales data; mark it with an outlier flag rather than deleting it.

For a "typical invoice" in a report, use the median. Use the mean only when the total matters, because mean × count is the sum: 16,100 × 10 = 161,000.

The check that proves it

Every line of the output has a worksheet function. Rebuild them next to the ToolPak table and they must agree:

=AVERAGE(A2:A11)                    16,100
=STDEV.S(A2:A11)/SQRT(COUNT(A2:A11)) 3,683.63
=MEDIAN(A2:A11)                     11,800
=MODE.SNGL(A2:A11)                  8,900
=STDEV.S(A2:A11)                    11,648.65
=VAR.S(A2:A11)                      135,691,111.1
=KURT(A2:A11)                       6.87
=SKEW(A2:A11)                       2.55
=MAX(A2:A11)-MIN(A2:A11)            38,300
=SUM(A2:A11)                        161,000
=COUNT(A2:A11)                      10

If a line disagrees, the input range is wrong: usually the Labels box was set the wrong way, so a header was read as data or the first invoice was read as a header. Before trusting any summary, run the five checks before you trust a spreadsheet total.

ToolPak or worksheet functions?

The ToolPak writes values, not formulas. Change an invoice and the output stays as it was until you rerun the tool. Worksheet functions recalculate as soon as the data changes.

Use the ToolPak for a one-time look: exploring a new export, a quick regression, a t-test for a single question. Use functions for anything refreshed monthly, because a report built from pasted ToolPak output is only correct on the day it was run.

Where it goes wrong

• Results are pasted values. Change the data and the output stays wrong until the tool is rerun.
• Labels set the wrong way. Checking Labels when the range has no header drops the first value; leaving it unchecked when there is a header makes most tools stop on the text.
• Filters and hidden rows are ignored. The tool reads the whole input range, including rows a filter hides, so a filtered view is not what gets analyzed.
• Loading the VBA add-in only. Analysis ToolPak - VBA is for macros; it does not add the Data Analysis button.
• Blanks and text in the input range. Most tools refuse to run or mis-count; clean the column first.

When the data outgrows the add-in

The ToolPak gives one-time statistics that go stale as soon as the next ledger export arrives. Covirage's tools recompute the measures from each new export and check the totals first; the external AI model explains the result and never does the arithmetic. See how finance teams ask the question instead of rebuilding the workbook, and Excel or an analytics tool for where the line falls. For the measures themselves, see descriptive statistics in Excel, moving average in Excel and how to make a pivot table in Excel.