In everyday language, unique and distinct can mean similar things. In spreadsheet analysis, however, people often use distinct values for every different item and unique-only values for items appearing exactly once. This guide explains the difference with an example, a memory trick, and Excel formulas. Note: terminology varies between tools: Excel’s UNIQUE function returns distinct values by default.
Difference between Unique and Distinct
Distinct: All different values in a dataset, regardless of how many times they appear. Each value is counted only once, even if it appears multiple times.
Unique: Values that appear exactly once in the dataset.
Understand the Difference with an Example
Suppose your dataset contains {1, 2, 2, 3, 4, 4, 4, 5}.
There are five distinct values but only three values that occur exactly once:
Distinct values: {1, 2, 3, 4, 5}. Repeated values appear only once in the result.
Unique-only values: {1, 3, 5}. Each of these numbers occurs exactly once in the source.
How to Remember Unique vs Distinct
Remember unique-only as one of a kind: a value must occur exactly once in the original list to qualify.
Remember distinct as different—both words start with D. A distinct list shows every different value once, even when the original list contains duplicates.
Excel Formulas: Distinct vs Unique-Only Values
In Microsoft 365 and Excel 2021 or later, enter the numbers in cells A2:A9 and use the following formulas in an empty area:
- All distinct values:
=UNIQUE(A2:A9)returns 1, 2, 3, 4, and 5. - Values occurring exactly once:
=UNIQUE(A2:A9,,TRUE)returns 1, 3, and 5. - Count distinct values:
=ROWS(UNIQUE(A2:A9))returns 5.
Why the double comma? In UNIQUE(array,[by_col],[exactly_once]), the second argument controls whether Excel compares columns rather than rows. Leaving it blank and setting the third argument to TRUE requests values appearing exactly once.
Does UNIQUE Mean the Same Thing in SQL?
Not necessarily. In SQL, SELECT DISTINCT column_name returns different values from a query result, while a UNIQUE constraint controls duplicate values stored in a table. In Excel, the UNIQUE function produces a distinct list by default. Always check the terminology used by the particular application.
For a detailed walkthrough, see three ways to extract distinct values in Excel.
Practical Excel Notes: Blank Cells, Spills and Older Versions
Blank cells: A blank in the source range can appear as a zero in a spilled UNIQUE result. If you want to exclude empty source cells, use =UNIQUE(FILTER(A2:A9,A2:A9<>"")). This formula filters out blank cells and formulas returning an empty string before extracting distinct values. If no nonblank values exist, FILTER can return a calculation error unless you provide an appropriate empty-result fallback.
Spill range: Enter the formula in a cell with enough empty cells below it. If Excel displays #SPILL!, clear the obstructing cells or move the formula outside an Excel Table. Dynamic-array formulas can reference Table data, but the spilled results must be placed in the worksheet grid outside the Table.
Older Excel versions: The UNIQUE function is available in supported dynamic-array versions, including Microsoft 365 and Excel 2021 and later. In older Excel versions without UNIQUE, use Data → Remove Duplicates on a copy of the data, or Data → Advanced with Unique records only. Remove Duplicates changes the selected data, so make a backup first.
Related Posts
C P Gupta is a YouTuber and Blogger. He is expert in Microsoft Word, Excel and PowerPoint. His YouTube channel @pickupbrain is very popular and has crossed 9.9 Million Views.
Pingback: How to Get Distinct Values in Excel 2013 and Above version