How to Calculate Column Statistics in Excel

The Core Statistics to Check

Count: How many non-empty values exist?
Blank count: Are required fields missing?
Sum: Does the total match expectations?
Average: Is the typical value realistic?
Minimum and maximum: Are there outliers or impossible values?
Unique count: How many distinct customers, regions, or categories exist?

Example: What Statistics Reveal

Suppose a monthly sales file has 12,000 rows. The revenue column has 11,982 numeric values, 18 blanks, a minimum of -450, and a maximum of 9800000. That tells you four things immediately: some rows are missing revenue, negative values may represent returns or errors, the maximum may be an extra-zero mistake, and the file should not be used blindly in a dashboard.

This is why column statistics are a good first step before cleaning or importing data. They do not fix the file, but they point you to the columns that deserve attention.

Useful Excel Formulas

=COUNTA(A2:A1000)     // non-empty values
=COUNTBLANK(A2:A1000) // blanks
=SUM(A2:A1000)        // total
=AVERAGE(A2:A1000)    // average
=MIN(A2:A1000)        // smallest value
=MAX(A2:A1000)        // largest value
=COUNTA(UNIQUE(A2:A1000)) // distinct values in newer Excel

For older Excel versions without UNIQUE, use a PivotTable or Advanced Filter to count distinct values.

Why This Helps Auditing

Column statistics catch issues that a visual scan misses. A salary column with a maximum of 9000000 may have an extra zero. A quantity column with negative values may contain returns, or it may contain an error. A customer ID column with blanks may break imports into a CRM or database.

Statistics to Use by Data Type

Numeric columns: sum, average, min, max, blank count, negative count.
Text columns: unique count, blank count, longest value, most common values.
Date columns: earliest date, latest date, blank count, impossible future dates.
ID columns: blank count, duplicate count, unique count, text/number consistency.

Red Flags Worth Investigating

A high blank count in a required field, a minimum date before your business existed, a maximum value far above the normal range, or a unique count that is much higher than expected can all indicate hidden data-quality problems. These issues are especially important before uploading spreadsheets into accounting software, CRMs, databases, or BI tools.

Use the Free Column Statistics Tool

The Column Statistics tool calculates summaries for every column in a sheet automatically. It is helpful when a workbook has many columns and you want a quick quality check before reporting, sharing, or importing the data.

The tool runs client-side and does not upload the workbook. It is designed for fast review, not storage.