Use Excel's Data Analysis Toolpak for Descriptive Statistics

This tutorial demonstrates how to use the Data Analysis Toolpak to generate tables of descriptive statistics in Excel. One of the first steps in any analytics project will be to get a summarized look at the dataset and variables you aare working with. Excel offers a quick and easy way to generate tables of summary statistics as a starting point for describing the data you are working with.

What the Tutorial Covers

Descriptive statistics summarize where the center of your data lies and how spread out it is. Together with a quick chart they are the first step in almost any data analysis, and Excel's Data Analysis Toolpak produces a full table of them in a few clicks.

  • Turning on the Analysis Toolpak (press Alt, then T, then I to open the Add-ins dialog)
  • Choosing Descriptive Statistics from the Data Analysis menu
  • Selecting the input range and ticking Labels in First Row when your data has a header
  • Sending the output to a new worksheet and ticking Summary statistics
  • Optional extras: a confidence level for the mean, and the Kth largest and smallest values
  • Formatting the output, for example as percentages for weekly returns
  • Running several columns at once to compare them side by side

The Confidence Level row is the margin of error: add it to and subtract it from the mean to get a confidence interval for the mean. For more on that, see confidence intervals in Excel.

Advertisement