What the Spreadsheet Column Profiler does
This profiler reads an Excel sheet or CSV file and describes every column: the type its values actually have, how many are blank, how many are distinct, the most common values, minimum and maximum, mean, median, standard deviation and quartiles for numbers, the range of dates, and warnings for the things that break imports and analyses - mixed types, stray spaces, leading-zero codes and outliers.
It is the check to run before loading a file into a database, a BI tool or a pivot table, when you need to know what is in the data rather than what the header says. Everything is computed in your browser; the file is never uploaded.
How to use it
- Drop an .xlsx or .csv file, paste CSV, or load an example. For a workbook, choose the sheet.
- If the header is not on the first row (reports often have a title block), set the header row number.
- Choose Profile columns. The summary table lists every column; open a column below it for full statistics and a chart of its most common values.
- Fix what the warnings point to in your spreadsheet, or download the profile as CSV or JSON to attach to a data-quality ticket.
Reading the results
Type is decided from the values, not the header: a column is Whole number only if every filled cell is an integer, Date only if every filled cell is an ISO date (YYYY-MM-DD), and Text if any value does not fit. Mixed types warnings show the breakdown, which usually points to one bad value such as "unknown" in an age column.
Distinct counts values after trimming spaces. A column where every filled value is distinct is flagged as a candidate key - useful for spotting an ID column, and a warning sign if a supposed ID has repeats.
Outliers use the 1.5 x IQR rule (Tukey's fences): anything below the first quartile minus 1.5 times the interquartile range, or above the third quartile plus it. It is a screening rule, not proof of an error; quartiles use linear interpolation, the same method as Excel's QUARTILE.INC.
Worked example: a survey export with typical problems
The CSV example has 10 respondents. The score column holds 7.5, 8, 6.5, 9, 7, 8.5, 95, 7.5, 8 and 6. Sorted, the middle two values are 7.5 and 8, so the median is 7.75; the sum is 163 and the mean 16.3 - pulled far up by one value. The quartiles are 7.125 and 8.375, the IQR is 1.25, and the upper fence is 8.375 + 1.875 = 10.25, so 95 is flagged as an outlier (probably a 9.5 typed without its point).
age is Text with a mixed-types warning: 8 whole numbers, 1 text value ("unknown") and 1 blank. country has a value with a trailing space ("uk "), and UK, uk and United Kingdom appear as different values. signup_date is mostly ISO dates with one 05/01/2025, and email has one address without a domain ending - each shows as a mixed-types warning on its column.
Limitations: what the result does not prove
- Only ISO dates (YYYY-MM-DD) count as dates in CSV, because 05/01/2025 cannot be read without knowing the locale. Excel date cells are converted to ISO when the workbook is read, so they profile as dates.
- Statistics describe the file as it is; they do not say whether values are correct. A plausible wrong number is invisible to a profiler.
- Numbers with thousands separators such as 1,234.5 are recognised; numbers written with a decimal comma are treated as text.
- The limit here is 40 MB per file; very wide or long sheets are limited by browser memory.
Privacy: where your data goes
Everything you paste, type or drop is processed in this browser tab. It is not uploaded, logged, stored or sent to analytics. Session recording and tag-manager scripts are switched off on this page.
Standards and sources
- NIST/SEMATECH e-Handbook - Measures of skewness and outliers (box plots)
- Microsoft - QUARTILE.INC function
- Microsoft - STDEV.S function
- RFC 4180 - Common Format and MIME Type for CSV Files
Frequently asked questions
What is data profiling and why do it before an import?
Profiling summarises what each column really contains - types, blanks, distinct values and ranges - before you rely on it. Imports fail or silently coerce values when a column has one text value among numbers or dates in two formats, and a profile finds those in seconds.
How does it decide a column's type?
Every filled value is classified as integer, decimal, ISO date, yes/no, email or text. The column takes a type only when all its filled values agree (integers and decimals together make Decimal); otherwise it is Text with a warning listing how many values of each kind were found.
What does candidate key mean?
Every filled value in the column is different from every other, so the column could identify rows uniquely - like an order number or customer ID. It is only a candidate: a column can be unique in this file by chance, and blanks are not counted.
How are outliers calculated?
With Tukey's fences: values below Q1 - 1.5 x IQR or above Q3 + 1.5 x IQR, where Q1 and Q3 are the 25th and 75th percentiles and IQR is their difference. The fences are shown so you can judge whether the flagged values are errors or just the real spread of the data.
Is the standard deviation the sample or population figure?
The sample standard deviation, dividing by n - 1, which matches Excel's STDEV.S. For the population figure (STDEV.P) multiply by the square root of (n - 1) / n; with more than a few dozen rows the difference is small.
Can I profile a sheet whose header is not on row 1?
Yes. Set the header row to the row that holds the column names, and the rows above it are ignored. This handles reports that start with a title, a date line or a logo row.
Last reviewed by the A2Z.Tools team against the sources listed above.
