Home · Free tools · Count unique values
Count unique values
Drop a .xlsx or .csv, pick a column, and get two answers at once: how many distinct values it holds,
and how often each one occurs. No UNIQUE(), no SUMPRODUCT, and it works the same
on Excel 2019, LibreOffice and a Google Sheets export. The file is read in this tab and never uploaded.
Your spreadsheet
Drop .xlsx, .csv or .tsv here, or click to choose
Nothing is uploaded. The file is read by JavaScript in this tab and never leaves your computer — no server sees it, and closing the tab throws it away.
How to count unique values in a spreadsheet column
- Drop the file. A .xlsx with several sheets shows a sheet picker; a CSV is read with its delimiter and encoding detected.
- Pick the column. The picker lists the header names, or the spreadsheet letters when there is no header row.
- Read the unique count. The first figure is how many distinct values the column holds; blank cells are counted separately and excluded.
- Read the frequency table. Each value with its count and its share, most frequent first — the answer behind the question, most of the time.
The formulas, and when each one fails
The usual answers to this question are all formulas, and each is fine until it is not:
=COUNTA(UNIQUE(A2:A1000))is the modern one and it needsUNIQUE()— Microsoft 365 and Excel 2021 onward. On Excel 2019 or earlier it is#NAME?.=SUMPRODUCT(1/COUNTIF(A2:A1000,A2:A1000))works everywhere and divides by zero the moment the range contains one blank cell, which it usually does.- A pivot table gives the frequency table but counts distinct values only through the Data Model, and that is several dialogs away.
All three also need you to fix the range when the data grows. Dropping the file here has none of those edges, and gives you the frequency table in the same pass.
What counts as the same value
Values are compared after trimming surrounding whitespace and stripping invisible characters that
arrive with copied data — the non-breaking space (U+00A0) and the zero-width space (U+200B). Those are
why a column that looks like it holds 12 distinct values reports 15. Case is significant unless you
turn that off: EMEA and emea are two values by default, one when
Ignore upper/lower case is checked.
Blanks are reported, not counted
Empty cells are excluded from both the unique count and the frequency table, and their number is shown separately. Silently counting blanks as a value is the mistake that makes two tools disagree about the same column.
What to do with the frequency table
Download it as CSV or .xlsx, or copy it as Markdown for a ticket. If what you actually want is the rows behind a repeated value rather than the counts, that is a different question — find duplicates lists them, and highlight duplicates colors them in place.
Questions people ask
How do I count unique values in Excel without a formula?
Drop the file on this page and pick the column. The distinct count appears above the table, and the table lists every value with how often it occurs. Nothing is installed and nothing is uploaded.
Why does my count differ from COUNTA(UNIQUE(…))?
Two usual reasons. Blank cells: they are excluded here and reported separately, while a formula over a range that includes blanks counts the blank as a value. And invisible characters: a trailing space or a non-breaking space makes two visually identical values distinct to Excel, while they are trimmed here before comparing.
Does it work on Excel 2019, which has no UNIQUE()?
Yes — nothing here runs inside Excel. The file is parsed by this page, so the Excel version that wrote it does not matter. .xls from Excel 97 is the one exception.
Can it count unique values across two columns?
Not as a combined key — one column at a time. For distinct rows across every column, use the row cleaner with Remove duplicate rows, which reports how many rows were repeats.
Are my files uploaded anywhere?
No. The file is read by JavaScript running in this tab, using the browser's own
DecompressionStream to unzip .xlsx and a parser that runs on your machine. There is no
server call in the page — you can watch the network tab while you use it. Close the tab and the data is gone.
Does it work with old .xls files?
Not directly. The .xls format is a different, binary format from Excel 97. Open it in Excel, LibreOffice or Numbers and save as .xlsx or .csv, then come back. The tool tells you this rather than failing silently.
Related
Doing this every month?
TableDI 2 keeps it as a job — the files, the key columns, the tolerance and the fixes you made. Next month you drop in the new files and run it again.
macOS, Apple silicon and Intel; Windows is in progress. Free is not a trial — no account, no card.
Last reviewed 2026-09-15 by the TableDI team. Something wrong on this page? Tell us — it is one inbox, read by the people who build TableDI.