Free tools Windows power users keep installed
One-click scans. No signup required.
To count distinct values in a range in a supported version of Excel, enter =ROWS(UNIQUE(A2:A100)). To extract them as a list instead, use =UNIQUE(A2:A100). These treat repeated entries as one distinct value. If you mean values that appear exactly once, use =ROWS(UNIQUE(A2:A100,,TRUE)) to count them or =UNIQUE(A2:A100,,TRUE) to list them.
First, decide what “unique” means
Excel’s “unique” can mean either distinct values—one copy of each value, even if it repeats—or values that occur exactly once. For example, if the range contains Red, Blue, Red, the distinct list is Red, Blue, while the exactly-once list is Blue. In the UNIQUE function, the third argument selects the exactly-once interpretation.
Choose a method for the result you need
| Method | Best for | Result | Version or behavior notes |
|---|---|---|---|
UNIQUE |
A live list that updates with source data | Spilled formula result | Microsoft lists Microsoft 365, Excel 2024, and Excel 2021, among other clients; check the product and update state. |
ROWS(UNIQUE(...)) |
A live count of distinct values | Single count | Uses the dynamic-array UNIQUE function. |
| Advanced Filter | A copied list of unique records or a temporary view | Copied output or filtered source range | Built-in command; copying to another location preserves the original list. |
| Legacy array formula | Counting unique values in older Excel without UNIQUE |
Formula count | More complex; formula entry and data-type handling matter. |
| PivotTable | An interactive count summary | Explorable summary | Useful for rearranging fields and inspecting details rather than producing a simple standalone list. |
1. Extract unique values with UNIQUE
In a blank cell with enough empty space below it, enter:
=UNIQUE(A2:A100)
Excel returns one instance of each distinct value and spills the results into the cells below. Microsoft documents UNIQUE for Microsoft 365, Excel 2024, and Excel 2021, among other products. If Excel does not recognize the function, confirm your edition and update status; this formula is not the legacy-version route.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchReturn values that occur exactly once
Use the third argument, exactly_once:
=UNIQUE(A2:A100,,TRUE)
This excludes any value that appears more than once, rather than returning one representative of each repeated value. The optional second argument is by_col; leaving it empty keeps the default row-wise comparison for a vertical list.
Sort the extracted list
To sort the distinct values, combine the functions:
=SORT(UNIQUE(A2:A100))
For a growing dataset stored as an Excel Table, use a structured reference instead of a fixed range so the formula follows added or removed table rows. For example, replace A2:A100 with a reference to the relevant Table column.
2. Count unique values with ROWS and UNIQUE
To count distinct values in a one-column range, enter:
=ROWS(UNIQUE(A2:A100))
UNIQUE creates the distinct-value array and ROWS counts its entries. For values occurring exactly once, use:
=ROWS(UNIQUE(A2:A100,,TRUE))
Check how blank cells should be treated in your dataset before relying on a count; the formula’s result should match the definition of “unique” you intend to report.
3. Extract with Advanced Filter, then count with ROWS
Advanced Filter is a useful built-in option when you need a copied list or do not have the dynamic-array function. Include a heading with the source range. To preserve the source, choose Copy to another location rather than filtering in place.
- Select the source range, including its heading.
- Open Data > Advanced.
- Choose Copy to another location, specify the destination, and check Unique records only.
- Count the copied values below the heading with
=ROWS(destination_range), selecting only the copied data cells.
Filtering in place temporarily hides duplicate records; it does not remove them. Copying the unique records to another location creates a separate result and leaves the original data untouched. Microsoft’s Advanced Filter instructions and counting guidance are available in its filter and duplicate-value guidance and unique-value counting guidance.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errors4. Count unique values with a legacy array formula
For older Excel versions without UNIQUE, Microsoft documents a legacy approach combining IF, SUM, FREQUENCY, MATCH, and LEN. Use Microsoft’s text-aware formula pattern if the range includes text; a simplified formula based on FREQUENCY alone is not suitable for all data because FREQUENCY ignores text and zero values.
In older Excel versions, the documented array formula may require selecting the output range and pressing Ctrl+Shift+Enter. Microsoft 365 can confirm its dynamic-array formula with Enter. Since the formula is more intricate and sensitive to data types, use the official pattern for your version rather than substituting a shortened numeric-only variant. See Microsoft’s instructions for counting unique values among duplicates.
5. Use a PivotTable for an interactive count summary
Choose a PivotTable when you want to explore counts rather than create a simple extracted list or one-cell result. A PivotTable can summarize counts and let you expand, collapse, rearrange fields, and drill into details. Microsoft includes PivotTables among its approaches to counting values; see its counting overview and PivotTable instructions.
Do not confuse filtering with removing duplicates
Advanced Filter can hide duplicates or copy unique records, but Remove Duplicates deletes duplicate rows from the selected range. Microsoft recommends copying the original data first if you use that deletion command. Which rows count as duplicates depends on the columns selected for comparison and the displayed cell values; rows that differ in other columns may still be treated as duplicates when those columns are not included. Review the selection before confirming deletion. Microsoft explains this behavior in its guidance on filtering or removing duplicate values.
Recommended Free Tools
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
