Google Sheets: Get a List of Unique Values

With source values in A2:A, enter =UNIQUE(A2:A) in an empty cell to return each distinct value once. This guide covers ten ways to create, sort, clean, or use a unique-value list in Google Sheets, from quick formulas to built-in tools. It is for anyone organizing categories, names, IDs, or other repeated entries in a browser-based spreadsheet.

1. Use UNIQUE for a straightforward list

With the values you want to check in A2:A, put =UNIQUE(A2:A) in an empty cell; Sheets spills the results into the cells below, keeping the first-seen order. This is the simplest choice when you need a live list rather than a one-time cleanup, because changes to the source range can update the result automatically. Leave enough empty space beneath the formula, since anything blocking the spill range can produce an expansion error.

2. Sort the unique results alphabetically

With source values in A2:A, use =SORT(UNIQUE(A2:A)) to remove repeats and sort the remaining values in ascending order. Sorting is useful for a report, a category index, or a list that people need to scan, especially when the source data is entered in an inconsistent sequence. The result is still formula-driven, so it refreshes when the source changes; for descending order, set SORT’s sort-order argument to FALSE, as in =SORT(UNIQUE(A2:A),1,FALSE).

3. Exclude blank cells from the result

With entries in A2:A, enter =SORT(UNIQUE(FILTER(A2:A,A2:A<>""))) when blank cells should not appear in the finished list. FILTER first keeps only cells that are not empty, UNIQUE removes repeated entries, and SORT puts the remaining values in order, so this is handy for a column where people leave unused rows between records. If the source currently contains no nonblank values, FILTER has nothing to return and can show a no-matches error; that is different from a valid list containing an empty item.

4. Combine values from multiple columns

With two lists in A2:A100 and B2:B100, use =SORT(UNIQUE(FILTER({A2:A100;B2:B100},{A2:A100;B2:B100}<>""))) to stack them, remove blanks and duplicates, then sort the combined list. In a Google Sheets array literal, the semicolon stacks the two ranges vertically, which is useful when departments, months, or separate forms keep similar entries in different columns. Use matching range heights as shown and adjust the row limit to your data; limiting the ranges can also avoid calculating across thousands of unused cells.

5. Return unique complete rows

With records occupying A2:C100, enter =UNIQUE(A2:C100) to return distinct combinations across all three columns, not just distinct values from column A. For example, a row with “North,” “Widget,” and “Online” is considered different from “North,” “Widget,” and “Store,” even though two fields match. This approach suits de-duplicating a record table by the entire row, but it will not identify duplicates based on just one key column; for that job, select the key column or define a separate rule.

6. Keep values that occur exactly once

With values in A2:A, enter =UNIQUE(A2:A,FALSE,TRUE) to return only entries that occur once in the source, rather than one copy of every repeated entry. The second argument keeps the comparison row-based, and the third asks for values occurring exactly one time, making this useful for spotting one-off codes or names that may need review. It is not interchangeable with ordinary UNIQUE: if “Blue” appears four times, the ordinary formula returns “Blue” once, while this version leaves it out entirely.

7. Use QUERY when you want a grouped list

With one-column data in A2:A and no header included in that range, use =QUERY(A2:A,"select A where A is not null group by A",0) to group matching values and omit empty cells. QUERY is especially useful if the same output needs to grow into a small summary later, because its query can also select or group fields from a wider table. Its query text is less familiar than UNIQUE, and the final argument matters: use 0 here because the selected range begins with data rather than a header row.

8. Mark first occurrences with a helper formula

With source values in A2:A20, enter =AND(A2<>"",COUNTIF($A$2:A2,A2)=1) in B2, then fill it down through B20; TRUE marks the first nonblank occurrence of each value. You can then turn on a filter with Data > Create a filter and show only TRUE in column B, or copy the marked source values elsewhere. This method is useful when you want to see which original rows supplied the unique entries, but the helper results are not a compact spill list until you filter or copy them.

9. Remove duplicates from a range with the menu

To permanently clean a selected data range, choose Data > Data cleanup > Remove duplicates, confirm whether the range has a header row, select the columns Sheets should compare, and click Remove duplicates. This tool changes the selected data rather than creating a live list, so make a copy of the tab or range first if you may need the repeated rows later. Choosing all columns compares complete rows, while choosing only a key column can remove records that share that key even if other fields differ.

10. Summarize categories with a pivot table

Select a table that includes the category column, then choose Insert > Pivot table and create the pivot table in a new sheet; in the pivot table editor, add the category field under Rows. Pivot tables group repeated labels into one row, so they provide a quick distinct-category view alongside counts or other summaries, without writing a formula. They are a good fit for exploring a dataset, but refresh and layout needs differ from a simple UNIQUE spill list, and a pivot table may be more structure than needed for a short list.

How to Choose

Start by deciding whether the result should update when the source changes. For a reusable list, choose UNIQUE; add SORT when readers expect alphabetical order, and add FILTER when blanks must be excluded. If you need to compare multiple source columns, stack the ranges before applying UNIQUE. If the task is to retain only values seen once, use the exactly-once option instead of assuming ordinary UNIQUE does that.

Next, decide what counts as a duplicate. UNIQUE applied to a single column compares the entries in that column, while UNIQUE applied to several columns returns distinct row combinations. A helper formula is a better fit when you need to identify the original first-occurrence row, and Remove duplicates is appropriate when you intend to edit the source data itself. A pivot table makes more sense when you also want a count or other summary for each category.

Consider maintenance as well as the first result. Formula-based lists are convenient when source records keep changing, but keep their spill area clear and use bounded ranges in large sheets when practical. Menu cleanup is direct but alters the selected range, so preserve a backup. If a value that looks repeated still appears twice, inspect the source for extra spaces, inconsistent punctuation, or other text differences before choosing a more complex formula.

These instructions and menu paths are for Google Sheets in a web browser. Excel for Microsoft 365 also has a UNIQUE function, but its ribbon and data-cleanup commands are not the same as Sheets menus; use the Excel interface rather than following the Google Sheets menu path there.

Frequently Asked Questions

Does UNIQUE update when I add more data?

Yes, if the formula references a range that includes the new entries, such as A2:A. The output updates as cells in that range change, provided the spill area below the formula is not blocked.

How do I get unique values in alphabetical order?

With the source values in A2:A, use =SORT(UNIQUE(A2:A)). Add FILTER inside UNIQUE first if you also need to remove blank cells.

How can I use the list for a dropdown?

Create the unique list in an unused range, then select the cells that need the dropdown and choose Data > Data validation. Set the rule to a dropdown from a range and point it to the generated list; keep that list in a place users will not accidentally overwrite.

Why does my result show values that look like duplicates?

The source entries may not be identical even if they look similar, for example because one includes a trailing space or different punctuation. Check or clean the source text, and apply the same cleaning approach consistently before generating the list.

Can UNIQUE return distinct rows instead of distinct cells?

Yes. Reference all the columns that define a record, such as A2:C100, and UNIQUE returns each distinct row combination once; repeated values in only one column do not make two otherwise different rows duplicates.

What is the difference between UNIQUE and Remove duplicates?

UNIQUE creates a formula result and leaves the source intact, while Remove duplicates edits the selected range. Use the formula for a live output and the menu command for a deliberate cleanup after making a backup if needed.

Final Thoughts

For most changing lists, start with UNIQUE, then add SORT or FILTER to match the output you need. Use row-based comparisons, exactly-once logic, helper formulas, pivot tables, or the cleanup menu when the task calls for something more specific. Choosing based on whether you need a live list, a summary, or an edited source helps keep the result both useful and safe to maintain.

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.