How to Calculate Statistics for a Column in Excel

The "I need descriptive stats for this column" problem

You have a column of numbers. Test scores, transaction amounts, response times, anything. You want the basic descriptive statistics: mean, median, mode, standard deviation, min, max. Excel has all of them as separate functions but typing 8 of them in 8 cells is tedious.

TL;DR — Key takeaways

• Each statistic is a separate Excel function: AVERAGE, MEDIAN, MODE.SNGL, STDEV.S, MIN, MAX, etc.
• Analysis ToolPak > Descriptive Statistics gives a full table but requires the add-in.
• A free VBA macro writes a clean stats summary table next to your data with one click.

Method 1: Type each function

The basic approach:

Mean:    =AVERAGE(B2:B100)
Median:  =MEDIAN(B2:B100)
Mode:    =MODE.SNGL(B2:B100)
Std Dev: =STDEV.S(B2:B100)
Min:     =MIN(B2:B100)
Max:     =MAX(B2:B100)
Range:   =MAX(B2:B100) - MIN(B2:B100)
Count:   =COUNT(B2:B100)

Drop each into a cell. About 90 seconds of typing.

The catch: typing the range 8 times. And remembering whether to use STDEV.S (sample) or STDEV.P (population), MODE.SNGL or MODE.MULT, etc.

Method 2: Analysis ToolPak Descriptive Statistics

Excel ships with the Analysis ToolPak add-in (available but disabled by default).

1. File > Options > Add-ins > Analysis ToolPak > Go > check it > OK.
2. Data > Data Analysis > Descriptive Statistics > OK.
3. Pick input range. Check Summary statistics. Click OK.
4. Excel writes a table with about 15 statistics.

Pros: Full descriptive stats output.

Cons: Add-in dependency. Output includes statistics most people don't need (skewness, kurtosis). Doesn't update on data change (one-shot).

Method 3: The free VBA macro

Download Quick Statistics Summary. Free .xlsm with one macro.

1. Select a single column of numbers.
2. Alt + F8, pick the macro, click Run.
3. The macro writes a 9-row stats summary in the columns to the right: Count, Mean, Median, Mode, Std Dev (sample), Min, Max, Range.

In place. No add-in. Static values, so no recalc dance.

What "mode" means when there's no clear mode

If no value repeats in the column, MODE.SNGL returns #N/A. The macro catches this and writes (no mode) instead.

If multiple values tie for the most-frequent, MODE.SNGL returns the first one found (lower in row order). For all modes, use MODE.MULT which returns an array.

What "standard deviation" means

STDEV.S (sample) divides by n-1 and is what statistics courses teach as "the standard deviation".

STDEV.P (population) divides by n and is appropriate when your data is the entire population, not a sample.

For most real-world data analysis, you want STDEV.S. The macro uses STDEV.S.

A common scenario: pre-grade analysis

Teacher wants descriptive stats on a quiz before assigning grades. The stats inform whether to scale the assignment.

1. Select the score column.
2. Run Quick Statistics Summary.
3. See mean, median, mode, std dev. Decide whether to scale.
4. Run Letter Grade From Numeric Scores to assign grades.

Versus the typing approach: 5 seconds versus 90.

Frequently asked questions

What if the column has text mixed with numbers?

Text cells are skipped. The macro uses IsNumeric() per cell. The popup tells you the count of numeric cells used.

Does it handle empty cells?

Empty cells are skipped, not treated as 0. So a column with 100 cells where 20 are blank gets stats based on 80 values.

Can I get more statistics (skewness, kurtosis, variance)?

Edit the .bas source. The pattern is the same: add a row label and a value calculation. About 3 lines per additional statistic.

Can I run it on multiple columns at once?

No. Run once per column. The macro requires a single-column selection.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the writes.

What to do next

After looking at stats, common follow-ups: assign grades with Letter Grade From Numeric Scores, highlight outliers with Highlight Top and Bottom Values, add a totals row with Add Totals Row to Numeric Columns.