How do I calculate statistics for a column in Excel?

Select a blank cell and enter formulas: =COUNT(A:A) for the count of numbers, =COUNTA(A:A) for all non-blank cells, =SUM(A:A) for the total, =AVERAGE(A:A) for the mean, =MIN(A:A) for the minimum, =MAX(A:A) for the maximum, =MEDIAN(A:A) for the middle value, and =STDEV(A:A) for the standard deviation. Subtract 1 from COUNTA to exclude the header row.

How do I get summary statistics for all columns at once in Excel?

Use the Excel Column Statistics tool at ExcelErrorFinder.com — upload your workbook and it calculates count, blank count, unique count, sum, average, min, max, and median for every column at once. Alternatively, use Excel's Data Analysis ToolPak (Data > Data Analysis > Descriptive Statistics) and select the entire data range.

Why does AVERAGE give a different result than expected in Excel?

The most common reasons: blank cells are excluded from AVERAGE, and text-formatted numbers are also excluded. If a column has numbers stored as text, AVERAGE ignores them — making the average appear to work while actually calculating from fewer values than you expect. Use COUNT vs COUNTA to detect how many values AVERAGE is including.

How to Calculate Column Statistics in Excel - Complete Guide

Why Column Statistics Matter for Data Quality

Statistics on a column reveal four categories of problems:

Completeness: How many values are blank? A column with 12,000 rows and 3,000 blanks may represent data that was never collected, a failed import, or a structural problem in the source system.
Validity: Are the values in a reasonable range? A salary column with a minimum of -50,000 or a revenue column with a maximum of 50,000,000,000 warrants investigation before any aggregation.
Uniqueness: How many distinct values are there? A "product category" column with 847 unique values in a dataset that should have 12 categories likely has inconsistent entries — capitalisation differences, synonyms, trailing spaces.
Distribution: Is the average much higher than the median? A large gap between mean and median indicates outliers that may skew the analysis.

Core Column Statistics Formulas

Replace A2:A1000 with your actual column range, or use A:A to reference the entire column (excluding the header row in your calculation by subtracting 1 from COUNTA where needed).

Count and Completeness

=COUNT(A2:A1000)         — count of numeric values only
=COUNTA(A2:A1000)        — count of all non-blank values (any type)
=COUNTBLANK(A2:A1000)    — count of blank cells
=COUNTA(A2:A1000)-COUNT(A2:A1000)   — count of text values in a number column

If COUNTA is higher than COUNT in a column that should contain only numbers, some values are stored as text. This commonly happens after CSV imports. Text-formatted numbers are excluded from SUM, AVERAGE, and other numeric functions — results will undercount silently.

Numeric Statistics

=SUM(A2:A1000)           — total
=AVERAGE(A2:A1000)       — arithmetic mean (excludes blanks)
=MEDIAN(A2:A1000)        — middle value (less sensitive to outliers)
=MIN(A2:A1000)           — smallest value
=MAX(A2:A1000)           — largest value
=STDEV(A2:A1000)         — sample standard deviation
=LARGE(A2:A1000,2)       — second-largest value (useful for checking outliers)
=SMALL(A2:A1000,2)       — second-smallest value

Unique Values Count

=COUNTA(UNIQUE(A2:A1000))   — unique value count (Excel 365)
=SUMPRODUCT((A2:A1000<>"")/COUNTIF(A2:A1000,A2:A1000&""))  — all Excel versions

The UNIQUE approach is cleaner in Excel 365. The SUMPRODUCT formula works in all versions and handles blanks safely with the &"" trick.

Percentile and Distribution

=PERCENTILE(A2:A1000,0.25)  — 25th percentile (Q1)
=PERCENTILE(A2:A1000,0.75)  — 75th percentile (Q3)
=PERCENTILE(A2:A1000,0.75)-PERCENTILE(A2:A1000,0.25)  — interquartile range (IQR)

The IQR is a robust measure of spread not inflated by outliers. Values more than 1.5× the IQR above Q3 or below Q1 are commonly flagged as outliers for further review.

Statistics by Column Type

For Numeric Columns (Amounts, Quantities, Scores)

Run: COUNT, COUNTBLANK, SUM, AVERAGE, MEDIAN, MIN, MAX, STDEV. Compare AVERAGE to MEDIAN — a large difference suggests outliers. Check MIN for negatives if negatives are not expected. Check MAX for implausibly large values.

For Text / Category Columns (Status, Region, Name)

Run: COUNTA, COUNTBLANK, and unique value count. Then generate a frequency table: select the column and create a PivotTable with the column in both Rows and Values (set to Count). This shows exactly how many times each value appears — revealing typos, duplicate category labels, and inconsistent formatting.

For Date Columns

Run: COUNTA, COUNTBLANK, MIN (earliest date), MAX (latest date). Check whether the date range is sensible for your dataset. Also verify COUNT equals COUNTA — if not, some dates are stored as text rather than Excel serial dates, so they will not sort or filter correctly.

For ID / Code Columns

Run: COUNTA, COUNTBLANK, unique count. Use =COUNTIF(A:A,A2)>1 as a helper column to flag duplicate IDs. For fixed-length IDs, also check: =MIN(LEN(A2:A1000)) and =MAX(LEN(A2:A1000)) — if both are not equal to the expected length, some IDs are truncated or padded incorrectly. See the guide on keeping leading zeros in CSV imports if IDs have lost their leading zeros.

A Real-World Example: Profiling a Sales Dataset

Suppose you receive a 12,000-row sales export. Before building any reports, run statistics on each column:

OrderID: COUNTA = 11,982, Unique = 11,900. There are 82 blank order IDs and 82 duplicate IDs — likely a data export problem worth escalating before the data is used.
Revenue: COUNT = 11,050, COUNTA = 11,982. This means 932 revenue values are stored as text — they will be excluded from SUM and AVERAGE, understating the total by an unknown amount.
Revenue MIN = -15,000: Negative revenue may represent returns. Before summing the column, understand whether negatives are intentional and whether they should be included or excluded from totals.
Region: Unique values = 18, but the data dictionary defines 6 regions. The extra 12 are likely variations: "North West" vs "NorthWest" vs "N.West" — each counted as a separate category in every SUMIF and PivotTable.

None of these problems would be obvious from scrolling through the data. Statistics surface them in seconds.

Using the Data Analysis ToolPak

For a built-in descriptive statistics report without individual formulas:

Go to File > Options > Add-ins. Select Excel Add-ins at the bottom and click Go.
Check Analysis ToolPak and click OK.
Go to Data > Data Analysis.
Choose Descriptive Statistics and click OK.
Set the Input Range to your column, check Labels in First Row if you have a header, check Summary Statistics, and click OK.

Excel outputs count, mean, standard error, median, mode, standard deviation, variance, kurtosis, skewness, range, min, max, and sum in a new table. This is the fastest way to get a full statistical profile of a single column without writing formulas one by one.

Column Statistics Tool for All Columns At Once

The Column Statistics tool profiles every column simultaneously without formula setup:

Upload your Excel workbook.
Choose the sheet to analyze.
The tool returns: count, blank count, unique count, sum, average, min, max, and median for each column.
Review the profiles to spot data quality issues before starting analysis.

Particularly useful for wide datasets with many columns, or as a first-pass quality check before handing data to stakeholders or loading it into another tool.

Red Flags to Look For in Column Statistics

COUNT < COUNTA on a number column: Some numbers are stored as text. Verify with =ISNUMBER(A2). Fix with Text to Columns or VALUE formula.
Unexpected blank count: Zero blanks when you expected some, or far more blanks than expected — both suggest an import or schema problem.
Unique count higher than expected for a category column: Inconsistent naming is present. Run a PivotTable frequency table and standardize the values. The Extract Unique Values tool can help identify all distinct entries.
Unique count equals COUNTA for an ID column: Good — every ID is distinct. If Unique < COUNTA, there are duplicate IDs that need investigation.
Average much larger than median: One or more extreme outliers are inflating the mean. Investigate the second-largest value with =LARGE(A:A,2) to determine whether it is an error or a real edge case.
MIN is negative when negatives are not expected: Investigate whether these represent legitimate credits, adjustments, or data entry errors before including them in totals.

Frequently Asked Questions

Can I calculate column statistics for multiple columns at the same time with a single formula?

In Excel 365, you can use array formulas and BYROW/BYCOL to compute statistics across all columns at once. For most workflows, a statistics summary table with one row per column is more readable and auditable. The Column Statistics tool handles all columns simultaneously with one upload.

Why is my AVERAGE different from what I expected?

AVERAGE excludes blank cells. If your range has 100 cells but 20 are blank, AVERAGE divides by 80, not 100. Use =SUM(A2:A100)/100 to force division by total rows including blanks. Also check for text-formatted numbers — they are invisible to AVERAGE even if they look like numbers on screen.

How do I calculate statistics excluding outliers?

Use TRIMMEAN to compute the mean after excluding a specified percentage from the top and bottom: =TRIMMEAN(A2:A100,0.1) excludes 5% from each end. Alternatively, use AVERAGEIFS with min/max bounds to exclude extreme values: =AVERAGEIFS(A2:A100,A2:A100,">"&PERCENTILE(A2:A100,0.05),A2:A100,"<"&PERCENTILE(A2:A100,0.95)).