Descriptive Statistics in Excel (Tutorial)

Descriptive statistics help make sense of large datasets, and Excel makes it easy. With built-in tools for calculating metrics like mean, mode, and standard deviation, as well as creating histograms and percentile rankings, Excel simplifies data analysis. Best of all, it’s user-friendly — just input your data, and Excel does the rest.

How to do descriptive statistics in Excel

Consider a worksheet summarizing sales data for a clothing retailer. In column A, the worksheet lists the suggested retail price, while column B shows the number of units sold for each clothing item through a popular retail outlet. You can use the Descriptive Statistics tool to summarize this dataset effectively.

To calculate descriptive statistics for the your dataset, follow these steps:

Step #1

Install the Data Analysis ToolPak:

1. Go to File > Options.
2. In the Excel Options dialog, select Add-Ins from the menu on the left.
3. Look for the Analysis ToolPak in the list of available add-ins.
4. Select it and click Go to open the Add-Ins dialog.
5. Check Analysis ToolPak and click OK to install it.

In the Excel Subscription version, follow the same steps by navigating to File > Excel Options > Add-ins.

Step #2

Click on the Data tab and select the Data Analysis option to initiate the descriptive statistics calculation.

Excel will display the Data Analysis dialog box, as shown in Figure 1.

Step #3

In the Data Analysis dialog box, select Descriptive Statistics from the list of Analysis Tools and click OK.

Excel will then open the Descriptive Statistics dialog box, as shown in Figure 2.

Step #4

In the input section of the Descriptive Statistics dialog box, specify the dataset you want to analyze.

To select the data for statistical analysis, click the Input Range text box and enter the range reference for the dataset. For example, in the sample worksheet, the input ran ge is $A$1:$C$35. Ensure the range uses absolute references, indicated by the dollar signs.

To simplify selecting the range, click the worksheet icon next to the Input Range text box. Excel temporarily hides the Descriptive Statistics dialog box, allowing you to drag the mouse to select the range. Once selected, click the worksheet icon again to return to the dialog box.

Specify how the data is organized by selecting either the Columns or Rows radio button. 

To indicate if the first row contains labels describing the data, check the Labels in First Row box. For instance, in the sample worksheet, the data is organized in columns, and the first row contains labels, so you select the Columns option and check the Labels in the First Row box.

Step #5

In the output options section of the Descriptive Statistics dialog box, specify where and how Excel should display the results.

You can choose from three options: Output Range, New Worksheet Ply, or New Workbook. Typically, the statistics are placed on a new worksheet within the current workbook. To do this, select the New Worksheet Ply radio button.

To specify the statistical measures you want, use the checkboxes in the Output Options section. Check Summary Statistics to calculate mean, mode, and standard deviation metrics. Select Confidence Level for Mean to calculate a confidence level for the sample mean and enter the desired percentage in the provided text box. Use the Kth Largest and Kth Smallest options to find the largest or smallest values in the dataset.

Once you've defined the data location and selected the desired calculations, click OK. Excel will generate the descriptive statistics on a new sheet, as shown in Figure 3. Table 1 explains the statistical measures calculated by Excel.

Accessing descriptive statistics in various Excel versions

On Mac:

1. Open Excel and go to Tools in the menu bar.
2. Select Add-ins.
3. Check Analysis ToolPak and click OK.
4. Once installed, go to the Data tab and find the Data Analysis option. Use it to access Descriptive Statistics.

Excel for Windows

1. Go to File > Options > Add-ins.
2. In the Manage box, select Excel Add-ins and click Go.
3. Check Analysis ToolPak and click OK.
4. Open the Data tab, and you'll find Data Analysis. Select it and choose Descriptive Statistics.

Excel Online

The Analysis ToolPak is not available in Excel Online. However, you can perform descriptive statistics manually using formulas like AVERAGE, STDEV, and COUNT.

Excel for Office 365 (Subscription Version)

1. Go to File > Excel Options > Add-ins.
2. Follow the same steps as in the Windows version to enable the Analysis ToolPak.
3. Access the Data Analysis button in the Data tab.

These steps allow you to effectively access and use Descriptive Statistics across different Excel platforms.

Conclusion

The Analysis ToolPak enhances Excel’s capabilities, allowing users to perform advanced statistical analysis through simple steps. This guide outlines the step-by-step process of enabling and using the Analysis ToolPak across various Excel platforms, empowering users to analyze datasets effectively.

For further insights and a detailed walkthrough of using the Analysis ToolPak in Excel for data analysis, check out the Excel Data Analysis ToolPak Guide.