Skip to content
Tutorials Center
My learning

Topics

Lesson 65 of 93 ยท PivotTables and Data Analysis

The Analysis ToolPak

Install and use the Excel Analysis ToolPak: descriptive statistics, histogram, regression, t-test, ANOVA, correlation matrix, moving average and exponential smoothing, with formula equivalents.

The Analysis ToolPak is an Excel add-in that adds a Data Analysis button with about twenty statistical tools: descriptive statistics, histograms, regression, t-tests, ANOVA, correlation, moving averages and more. Each tool is a dialog: you choose input and output ranges, and Excel writes a report on the sheet. It is a fast way to get a full regression table or a complete set of summary numbers in one step.

There is one important catch. The ToolPak writes static values: the report does not update when your data changes. Everything it can do can also be done with ordinary worksheet functions (which stay live), so this lesson shows both, and uses a JavaScript stand-in to verify the numbers.

Excel cannot run on this site, so the menus and formulas are real Excel but read-only. The TryIts are JavaScript stand-ins that calculate what the tools report (the same formulas, written out) so you can check your own results. They are not the ToolPak itself.

Installing it

Windows: File > Options > Add-ins. At the bottom, set Manage: Excel Add-ins and click Go. Tick Analysis ToolPak (and Analysis ToolPak - VBA if you want to call it from macros) and click OK. A Data Analysis button appears at the right end of the Data tab.

Mac (Excel 2016 and later, Microsoft 365): Tools > Excel Add-ins, tick Analysis ToolPak, OK. In some versions the entry is under the Data tab as Data Analysis once enabled. If the add-in is missing, reinstall or update Office.

Excel for the web does not support the ToolPak. Use the worksheet functions instead.

If the button does not appear, check Add-ins > Disabled Items; Excel sometimes disables add-ins after a crash.

Using any tool

  1. Put your data in columns with a header row.
  2. Click Data > Data Analysis, pick the tool and click OK.
  3. Set the Input Range (tick Labels in first row if you include the header), choose an Output Range or a new worksheet, and tick the optional outputs (confidence level, residuals).
  4. Read the report. Because values are pasted, rerun the tool after the data changes.

Descriptive statistics

Tool: Descriptive Statistics, with Summary statistics ticked. The report lists Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, Count and optionally the Confidence Level. The functions are AVERAGE, STDEV.S, VAR.S, KURT, SKEW, MEDIAN, MODE.SNGL and others (Statistics for Analysts). The Standard Error is STDEV.S/SQRT(COUNT), and the Confidence Level(95%) row is CONFIDENCE.T(0.05, STDEV.S, COUNT), the half-width of the interval around the mean.

Histogram

Tool: Histogram. Provide the data in the Input Range and, optionally, a Bin Range listing upper limits (10, 20, 30...). Each bin counts values greater than the previous limit and up to and including its own, with an extra More row for values above the last bin. Tick Chart Output and Cumulative Percentage to get a chart and an ogive. In a worksheet you reproduce the counts with =FREQUENCY(data, bins) or =COUNTIFS(...) (Frequency Distributions), or with Excel's built-in Histogram chart type.

Moving average and exponential smoothing

Moving Average takes an Interval (for example 3) and writes the average of the last 3 values next to each point, with #N/A for the first two (not enough history). Equivalent formula: =AVERAGE(B2:B4) filled down. Exponential Smoothing takes a Damping factor (1 minus alpha), and each forecast is F(t) = alpha * actual(t-1) + (1 - alpha) * F(t-1), with the first forecast set equal to the first actual. See Correlation and Forecasting for trends and FORECAST.ETS.

JS Descriptive statistics, histogram bins, moving average, smoothing

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Regression

Tool: Regression. Choose the Input Y Range (the outcome) and Input X Range (one or more predictors, in adjacent columns), tick Labels, Residuals and Line Fit Plots if wanted. The report has three blocks:

  • Regression Statistics: Multiple R (correlation), R Square (share of the variation explained), Adjusted R Square, Standard Error (typical size of a prediction error) and Observations.
  • ANOVA: the F statistic and Significance F, the p-value for "the model explains nothing". A small Significance F (below 0.05, say) means at least one predictor helps.
  • Coefficients table: the Intercept and one row per X variable with the Coefficient, its Standard Error, t Stat, P-value and a 95% confidence interval. A small p-value means that coefficient is distinguishable from zero.

The live equivalents are LINEST for coefficients and statistics, plus SLOPE, INTERCEPT, RSQ, STEYX (Correlation and Forecasting). The ToolPak's Regression also handles several predictors at once and gives p-values, which the simple functions do not.

t-Test

Tools: t-Test: Paired Two Sample for Means, Two-Sample Assuming Equal Variances, Two-Sample Assuming Unequal Variances (Welch). Choose the two ranges, a Hypothesized Mean Difference (normally 0) and Alpha (normally 0.05). The report shows each group's mean and variance, the t Stat, and P-values and critical values for one-tail and two-tail tests. If the two-tail P-value is below alpha, the difference between means is statistically significant. The worksheet function is T.TEST(range1, range2, tails, type) (type 1 paired, 2 equal variance, 3 unequal), which returns just the p-value and stays live.

ANOVA, correlation and covariance

  • Anova: Single Factor compares the means of three or more groups. It lists each group's count, sum, average and variance, then an ANOVA table with SS, df, MS, F, P-value and F crit. A p-value below alpha says at least one group mean differs; it does not say which.
  • Correlation produces a matrix with the correlation of each pair of columns; Covariance does the same for covariances. Use =CORREL(a, b) or =COVARIANCE.S(a, b) for one pair. Correlation is not causation (Correlation and Forecasting).
JS Regression and a two-sample t-test, by formula

Press Run (or Ctrl + Enter) to see the console output here.

No output.

ToolPak versus formulas

Analysis ToolPak Worksheet functions
Updates when data changes No (static report) Yes
Full regression table with p-values Yes, one click Needs LINEST plus extra formulas
Works in Excel for the web No Yes
Repeatable and auditable Rerun by hand Formulas show the method

Use the ToolPak to explore or to produce a one-off table; use formulas for anything that will be refreshed or audited. Always plot the data first, because a fitted line can hide an outlier or a curve. If you prefer code, Office Scripts and Python in Excel offer repeatable alternatives.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main drawback of Analysis ToolPak reports?

    The tools paste results as values. If the data changes, rerun the tool, or use live worksheet functions such as LINEST and T.TEST.
  2. 2. In the Regression output, what does Significance F test?

    Significance F is the p-value for the F test that all slope coefficients are zero. Below your alpha (commonly 0.05), at least one predictor helps.
  3. 3. Which worksheet function returns the p-value of a two-sample test assuming equal variances?

    The third argument is the number of tails (2) and the fourth is the type (2 means two-sample equal variance; 1 paired; 3 unequal variance).
  4. 4. Where does the Data Analysis button appear after you enable the add-in?

    Enabling Analysis ToolPak adds a Data Analysis command in the Analysis group at the far right of the Data tab.

Summary

  • Enable it via File > Options > Add-ins > Excel Add-ins > Analysis ToolPak; it is not available in Excel for the web.
  • Descriptive Statistics, Histogram, Moving Average and Exponential Smoothing summarize and smooth data.
  • Regression reports R Square, Significance F and a coefficients table with p-values; t-tests and single-factor ANOVA compare means.
  • Output is static, so rerun the tool after data changes or use live functions such as LINEST, T.TEST, FREQUENCY, CORREL.

Next: What-If Analysis: Goal Seek, Scenarios and Data Tables.