Descriptive Statistics Tools

Excel provides a data analysis tool called Descriptive Statistics which produces a summary of the key statistics for a data set.

Example 1: Provide a table of the most common descriptive statistics for the scores in column A of Figure 1.

Descriptive Statistics data analysis tool output

Figure 1 – Output from Descriptive Statistics data analysis tool

The output from the tool is shown in the right side of Figure 1. To use the tool, select Data > Analysis|Data Analysis and choose the Descriptive Statistics option. A dialog box appears as in Figure 2.

Dialog box for Excel's Descriptive Statistics data analysis tool

Figure 2 – Dialog box for Excel’s data analysis tool

Now click on Input Range and highlight the scores in column A (i.e. cells A3:A14). If you include the heading, as is done here, check Labels in first row. Since we want the output to start in cell C3, click the Output Range radio button and insert C3 (or click on cell C3). Finally click the Summary statistics checkbox and press the OK button.

Note that if we had also checked the Kth Largest checkbox, the output would also contain the value for LARGE(A4:A14, k) where k is the number we insert in the box to the right of the label Kth Largest. Similarly, checking the Kth Smallest checkbox outputs SMALL(A4:A14, k). The option Confidence Interval for Mean option generates a confidence interval using the t distribution as explained in One Sample t Test.

Real Statistics Data Analysis Tool: The Real Statistics Resource Pack provides the supplemental Descriptive Statistics and Normality data analysis tool which outputs the above statistics plus GEOMEAN, HARMEAN, MAD, AAD and IQR. But instead of just generating the numerical value of each statistic, as is in Excel’s Descriptive Statistics data analysis tool, the Real Statistics tool outputs the appropriate Excel formula for computing each statistic (see Figure 4 below). Thus whenever the input data values change, the output values will change automatically as well.

Both Excel’s Descriptive Statistics and the Real Statistics Descriptive Statistics and Normality data analysis tools allow you to report on multiple sets of data at the same time, as shown in the following example.

Example 2 – Use Excel’s Descriptive Statistics data analysis tool as well as the Real Statistics Descriptive Statistics and Normality data analysis tool to show the descriptive statistics for the two samples on the left side of Figure 3.

Descriptive statistics tool multiple

Figure 3 – Output from Excel’s Descriptive Statistics data analysis tool

The output from the Excel Descriptive Statistics data analysis tool is given on the right side of Figure 3.

To use the Real Statistics data analysis tool, enter Ctrl-m and select the Descriptive Statistics and Normality option. A dialog box will now appear as shown in Figure 4. Insert A5:B18 into the Input Range, make sure Column headings included with data is checked and select the Descriptive Statistics option.

Descriptive statistics dialog box

Figure 4 – Real Statistics Descriptive Statistics and Normality dialog box

After clicking on the OK button the following output will be displayed:

Improved Descriptive Statistics data analysis tool

Figure 5 – Real Statistics Descriptive Statistics data analysis tool

As described above, the tool actually generates formulas instead of the numerical values.

9 Responses to Descriptive Statistics Tools

  1. Fateh Khan says:

    I am using this tool at the moment to analyse a set of data from 10 M&As but the problem is, it is hard for me to explain/interpret the results in my analysis.

  2. Jess says:

    I am attempting to use Realstats add-in for an assignment. When trying to use the Descriptive Statistics, I cannot get it to work. I don’t know where I’m going wrong. I type in my Input Range (D11:D75), I make sure the ‘Column headings’ is ticked and the Descriptive Statistics option is ticked. I then enter my output range. I have tried numerous output ranges. Every time I click ‘OK’ it comes up with ‘Compile error in hidden module: Analysis’. The only time it has given an indication that it may work is when I experimented and made the output range the same as the input, and it then tells me “output will overwrite existing data” which I obviously don’t want. Please help.

    • Charles says:


      I am not sure what is going wrong. If you send me an Excel file with your data I can see whether it works properly on my computer. You can get my email address at Contact Us.

      Also please answer the following questions:
      1. Which version of Excel and Windows or MAC OC are you using?
      2. What answer do you get when you enter the formula =VER() in any cell in a spreadsheet?


  3. Shane Devenshire says:

    Question regarding the MAD:

    MAD can be Mean Absolute Deviation or Median Absolute Deviation? With the sample data set this function returns 12. Using =SUM(ABS(K2:K14-AVERAGE(K2:K14)))/COUNT(K2:K14) Excel returns 13.7988165680473 and using =SUM(ABS(K2:K14-MEDIAN(K2:K14)))/COUNT(K2:K14) it returns 13.4615384615385 (both array entered.)

    Obviously I am not following something, but what?

  4. Shane Devenshire says:

    Suggestions: (I am in no way being critical of you wonderful add-in, only making, hopefully constructive, suggestions)

    1. No need for a drop-down for the Real Statistics command since there is only one choice.
    2. In the Descriptive Statistics module Med-Q1 uses QUARTILE for the box plot output while all the other use QUARTILE.EXC. Probably would be best to go with QUARTILE.EXC for consistency and we never know what Microsoft will do with these semi-depreciated functions.

    • Charles says:


      1. I agree, but I don’t know how to write the program which avoids the drop-down.

      2. I agree, but since a lot of people still use Excel 2007, which does not support QUARTILE.EXC, I have decided, at least for now, to use QUARTILE as the default.


  5. malach m mogaka says:

    Very good teaching but i have not exactly understood statistical tools used to analyse both quantitative and qualitative data

Leave a Reply

Your email address will not be published. Required fields are marked *