Descriptive Statistics in Excel for Any Column of Numbers

Descriptive statistics in Excel summarise a column of numbers in a few key figures: the average, the middle value, the most common value, how spread out the numbers are, and the smallest and largest. You can build them with formulas or get them all at once with the Analysis ToolPak. This guide is for students, analysts and anyone preparing for a data test, and it shows both methods on one small dataset and explains what each number means.

What descriptive statistics tell you#

Descriptive statistics describe the data you have; they don't predict anything. They answer three questions:

• Where is the centre? The mean (average), median (middle value) and mode (most common value).
• How spread out is it? The range, standard deviation and variance.
• What shape is it? Skewness (is it lopsided?) and kurtosis (are the tails heavy or light?).

The examples use one week of daily orders in A1:B8:

Descriptive statistics with formulas#

Each statistic has its own function, so you can build a summary that updates whenever the data changes. Put labels in one column and these formulas next to them:

The results are rounded to two decimals here. The AVERAGE function guide, the COUNT function guide and the MAX function guide explain how each one treats text and blank cells. The basics are covered in more depth in AVERAGE, MIN and MAX and MEDIAN and MODE.

How to run the Descriptive Statistics tool#

The Analysis ToolPak builds the whole summary in one step. First, turn it on (you only do this once):

• Windows: File > Options > Add-ins. In the Manage box choose Excel Add-ins, click Go, tick Analysis ToolPak and click OK.
• Mac: Tools > Excel Add-ins, tick Analysis ToolPak and click OK.

Then run the tool:

1. Go to Data > Data Analysis (at the right end of the Data tab).
2. Choose Descriptive Statistics and click OK.
3. Set Input Range to B1:B8 and leave Grouped By on Columns.
4. Tick Labels in first row, because B1 holds the header.
5. Under Output options, pick an Output Range such as D1, or a new worksheet.
6. Tick Summary statistics and click OK.

Excel writes a two-column table with every statistic from the formula table above: Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum and Count. You can also tick Kth Largest, Kth Smallest and Confidence Level for Mean for extra rows.

Select several columns as the input range and the tool summarises each one side by side.

How to read the output#

Each line answers a simple question about the orders:

• Mean 49.71: on an average day, about 50 orders.
• Median 50: half the days had 50 or fewer orders. Mean and median are close, so no extreme day is pulling the average. Read average vs median for when they differ.
• Mode 55: the only value that appears twice.
• Standard deviation 8.04: a typical day is about 8 orders away from the mean. The standard deviation guide explains this in detail.
• Range 23: the gap between the slowest day (38) and the busiest (61).
• Standard error 3.04: how much the mean might change with a different week's sample. Smaller is more precise.
• Skewness -0.16: close to 0, so the data is fairly balanced around the mean. A large positive value means a long tail of high values.
• Kurtosis -0.90: below 0, so the tails are lighter than a normal bell curve. With only 7 values, skewness and kurtosis are rough guides at best.

Sample or population statistics?#

The ToolPak treats your data as a sample: its standard deviation matches STDEV.S and its variance is labelled Sample Variance. Use these when your data is part of a larger group, such as one week out of a year.

If your data is the whole population, such as every employee in a small team, use STDEV.P and VAR.P instead. On the orders, =STDEV.P(B2:B8) returns about 7.44, a little smaller than 8.04. The difference shrinks as the count grows. For the full explanation, see STDEV.S vs STDEV.P.

Add quartiles and percentiles#

The Descriptive Statistics tool doesn't list quartiles, but formulas do. =QUARTILE.INC(B2:B8,1) returns the first quartile (the value below which a quarter of the data falls) and =PERCENTILE.INC(B2:B8,0.9) returns the 90th percentile. Together with the minimum, median and maximum, quartiles give the classic five-number summary. The percentile and quartile guide shows how they work.

Common mistakes#

These slips give misleading summaries:

1. Including the header without ticking Labels. The ToolPak returns an error message about non-numeric data. Tick Labels in first row or start the range below the header.
2. Including a total row. A SUM at the bottom of the column doubles the sum and drags the mean up. Select the data rows only.
3. Numbers stored as text. COUNT, AVERAGE and the other functions skip them, so the count is too low. Compare COUNT with COUNTA: if they differ, some cells are text. The guide to Excel count functions explains the difference.
4. Blanks typed as 0. A missing day entered as 0 pulls the mean and minimum down. Leave it blank instead.
5. Forgetting the output is fixed. The ToolPak table won't change when the data does. Run it again, or use formulas.

Practise it#

Descriptive statistics are mostly a handful of functions you already know. Practise AVERAGE, MIN and MAX in a live spreadsheet, then work with MEDIAN and MODE and learn when to use COUNT and COUNTA. Each lesson checks your formulas instantly. Then try the exercise on this page to calculate the range of the week's orders.