Google Sheets Distinct Values: Find, Filter, and Count Them

To list distinct values in Google Sheets, enter =UNIQUE(A2:A100) in an empty cell, replacing the range with your data. Sheets returns each different value or row once, in the order it first appears; use filtering or the optional arguments when you need to omit blanks or find values that occur exactly once.

What “distinct values” means

In spreadsheet work, “distinct” usually means one copy of every different value. If a column contains North, South, North, East, the distinct list is North, South, East. It is not necessarily a list of values that occur only once: North still belongs in the distinct list even though it appears twice.

That distinction matters because people also use “unique” to mean “appears exactly once.” Google Sheets supports both interpretations, but they require different options. Before choosing a formula, decide whether you want a tidy list of categories, a list of one-off entries, or a cleaned copy of the original data. Each answers a different question.

Distinct lists are useful for checking which products were sold, preparing a dropdown, summarizing survey answers, or finding the set of regions in a transaction table. They can also help reveal unexpected entries, such as a misspelled category that would otherwise be hard to spot among repeated rows.

Use UNIQUE for a distinct list

With values in A2:A100, place this formula in an empty cell, such as C2:

=UNIQUE(A2:A100)

The result spills into the cells below C2. Leave those cells clear; existing content in the output area can prevent the formula from expanding and trigger a #REF! error. The original values are not changed. If a value appears more than once in the source range, it appears once in the result.

UNIQUE can also work on records made up of multiple columns. With a customer name in column A and a city in column B, the following returns each distinct name-and-city pair:

With paired records in A2:B100, enter this formula in an empty area:

=UNIQUE(A2:B100)

Here, a row is considered as a whole. Two rows with the same name but different cities remain separate, while repeated copies of the same pair collapse to one row. This is useful for combinations such as department and job title, or product and size. It does not mean “unique customer names”; for that, pass only the name column to UNIQUE.

Exclude blank cells and sort the results

A source range may include empty cells, especially when it is sized generously for future entries. If you want the output to contain only populated values, combine UNIQUE with FILTER. With the source values in A2:A100, use:

=UNIQUE(FILTER(A2:A100,A2:A100<>""))

FILTER keeps the cells that are not empty, and UNIQUE then removes repeats. This formula also updates when values in the source range change. The bounded range avoids including an arbitrary number of empty cells, while still giving room for later additions within the selected rows.

UNIQUE preserves the order of first appearance; it does not alphabetize the output. To sort the filtered distinct list, wrap it in SORT:

With values in A2:A100, enter this formula in an empty cell:

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

Sorting is helpful for a reference list or report, but it changes the order from the source’s sequence. If that sequence has meaning, use UNIQUE without SORT. If FILTER finds no matching cells, it can return an error because there is nothing to pass along; that is different from a formula failing to remove duplicates.

Find values that occur exactly once

For entries that appear only one time—not merely one copy of every distinct entry—use UNIQUE’s third argument. With the data in A2:A100, enter:

=UNIQUE(A2:A100,FALSE,TRUE)

The second argument, FALSE, says to compare rows rather than columns. The third argument, TRUE, requests items that occur exactly once. For example, if the source is Red, Blue, Red, Green, the result is Blue and Green; Red is excluded because it appears twice. This is useful for spotting one-time codes or names, but it is not the right formula for building a complete category list.

For a multi-column range, “exactly once” applies to whole rows. A repeated name paired with different cities makes two distinct rows, and each row may qualify if neither full pair occurs again. Think about the unit you are counting—individual cells, records, or combinations—before interpreting the results.

Count distinct entries

If you need a number rather than a visible list, count the filtered distinct results. With values in A2:A100 and blanks excluded, enter:

=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

For example, if the nonblank source values are North, South, North, and East, the formula returns 3. COUNTA counts the items in the resulting list, not the rows in the original range. This method is useful when the list itself is not needed in the report.

If every cell is blank, FILTER has no values to return and may produce an error rather than a count of zero. If that situation is possible, handle it separately or arrange the source so that at least one valid value exists. Also check that your selected range does not omit rows: a formula counting A2:A100 will not include a new value entered in A101.

Remove duplicates from the source data

A formula creates a separate result and leaves the source untouched. If you instead want to delete repeated rows from a dataset, use Google Sheets’ built-in cleanup command. Select the data range, then choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selected data has a header row and confirm which columns should be checked before removing duplicates.

This operation changes the selected data, so make a copy first if the original rows may be needed later. When you choose multiple columns, Sheets compares the values in those columns together; two records with the same email but different dates, for example, may both remain if the date column is included in the comparison. Select only the columns that define a duplicate for your task.

Use the formula approach when you want a live list that updates as the source changes or when you need to keep every original row. Use Remove duplicates when you intend to clean the selected data itself. A pivot table is another non-destructive option for summarizing categories: choose Insert > Pivot table, then add the relevant field under Rows in the pivot table editor. It can show categories for analysis, but it is a report rather than a replacement for the source data.

Common problems and how to avoid them

The result will not expand: Clear cells below or beside the formula where its output needs to spill. UNIQUE does not overwrite existing content.
Unexpected near-duplicates appear: Values with leading or trailing spaces can be different from the same text without spaces. Standardize the source, for example by cleaning a copy of it, before comparing.
A number and a number stored as text behave differently: Check how imported data is formatted and whether values were brought in as text. Standardizing the source type can prevent confusing lists and counts.
The list is in the wrong order: UNIQUE keeps first-appearance order. Add SORT only when alphabetical or numerical order is preferable.
The formula gives an error after a locale change: Google Sheets may use different argument separators depending on spreadsheet locale. If commas are rejected, check the locale and formula conventions for that sheet.
A result seems incomplete: Verify the range endpoints and make sure the formula points to the intended column or columns. For multiple columns, UNIQUE compares complete rows, not each column independently.

To highlight repeats rather than create a separate list, conditional formatting can help. With the values in A2:A100, select that range, choose Format > Conditional formatting, and use a custom formula rule:

=COUNTIF($A$2:$A$100,A2)>1

Set the formatting style and select Done. This marks values whose count in the specified range exceeds one; it does not remove them. The reference A2 adjusts for each row, while the dollar signs keep the counted range fixed.

Google Sheets and Excel differences

In Google Sheets, UNIQUE is available in the browser, and the menu command for deleting duplicates is Data > Data cleanup > Remove duplicates. Excel for Microsoft 365 and Excel 2021 also support the UNIQUE function and spill results into neighboring cells, but its ribbon command for deleting repeats is Data > Remove Duplicates. Menu names and dialog details are not identical, so follow the instructions for the application you are using.

In either program, a formula-generated distinct list is generally safer when you want the source data preserved. A cleanup command is more direct when you deliberately want to change the data. If a file moves between Sheets and Excel, check formulas and formatting after conversion, particularly if the workbook relies on features beyond basic dynamic arrays.

Frequently Asked Questions

Does UNIQUE remove duplicates from my original data?
No. It returns a separate, dynamic result and leaves the source range in place. To delete repeated records, use Data > Data cleanup > Remove duplicates after checking the selected columns.

How do I get distinct values in alphabetical order?
Combine SORT and UNIQUE. If blanks should be omitted as well, use the SORT(UNIQUE(FILTER(…))) formula shown above and adjust its source range.

What is the difference between distinct and unique?
Distinct commonly means one result for each different value, including values that repeat in the source. “Appears exactly once” excludes any value that occurs more than once. In Sheets, UNIQUE’s third argument can request that stricter interpretation.

Can I find distinct combinations across two columns?
Yes. Pass a two-column range, such as A2:B100, to UNIQUE. Sheets returns each different complete row once, so a change in either column creates a different combination.

Why does my formula show #REF!?
A common cause is that one or more cells in the output area are occupied. Clear the cells where the results need to expand, then check that the formula’s source range is valid.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.