Sort Unique Values in Google Sheets: Formulas and Tips

To sort unique values in Google Sheets, combine the UNIQUE and SORT functions. If your values are in A2:A20, enter =SORT(UNIQUE(A2:A20),1,TRUE) in an empty cell to return each distinct value once, in ascending order. The result updates when the source range changes.

What “sort unique” means

Sorting and removing duplicates are two different jobs. UNIQUE returns distinct entries from a range, while SORT arranges the results in a chosen order. Combining them is useful for creating a clean, alphabetized list of customers, products, locations, or categories without changing the original data.

The formula’s order matters for understanding its behavior: UNIQUE first removes repeated entries, then SORT orders what remains. The source cells are left untouched. The formula produces a separate output list, so it is a good choice when you want a reusable summary beside or on another sheet rather than a one-time change to your data.

For example, if A2:A7 contains “Pear,” “Apple,” “Pear,” “Plum,” “Apple,” and “Fig,” the result is Apple, Fig, Pear, Plum. Each value appears once and the order is alphabetical.

Basic formula and where to put it

Put the formula in a cell outside the source range, with enough blank space below it for the results to expand. With values in A2:A20, use:

=SORT(UNIQUE(A2:A20),1,TRUE)

In this example, the range passed to UNIQUE is one column wide. The 1 tells SORT to sort by the first column of its input, and TRUE means ascending order. For text, ascending order is generally alphabetical; numbers are arranged from low to high. If your data is in a different location, replace A2:A20 with the appropriate range.

Google Sheets uses an expanding, or “spill,” result: one formula fills the cells below it. Keep those cells empty. Existing content in the output area can prevent the result from expanding and trigger an error. You do not need to drag the formula down; doing so can create repeated or conflicting output.

Sort descending or sort by more than one column

To reverse the order, change the final argument to FALSE. With values in A2:A20, this formula returns the distinct values in descending order:

=SORT(UNIQUE(A2:A20),1,FALSE)

For a table, UNIQUE treats each row as a record. It keeps a row when that combination of column values has not appeared before. Suppose names are in A2:A100 and departments are in B2:B100. With those two columns as the source, this formula returns distinct name-and-department combinations, ordered by department and then name:

=SORT(UNIQUE(A2:B100),2,TRUE,1,TRUE)

The first sort key is column 2 of the supplied range, and the second is column 1. In this case that means department first, then name. The sort-column numbers refer to the range inside the formula, not necessarily to the worksheet’s column letters. This distinction matters if your range starts in column C or includes only selected columns.

A multi-column unique list is not the same as a list of unique names. If one person appears in two departments, both rows remain because the full rows differ. If you need just one name per person, choose which department or other associated detail should be kept; simply removing duplicate full rows cannot make that decision for you.

Exclude blank cells

An open-ended source range can make a convenient list that grows as you add records, but it may also include a blank entry in the output. To omit empty cells, filter them out before finding unique values. With source values in A2:A, use:

=SORT(UNIQUE(FILTER(A2:A,A2:A<>"")),1,TRUE)

The FILTER portion keeps only cells that are not empty; UNIQUE removes repeated values, and SORT orders the result. The formula uses an open-ended range, so new entries lower in column A can be included automatically. If your sheet is large, a bounded range such as A2:A500 can be more efficient and easier to inspect.

There is a small but useful distinction between a truly empty cell and a formula that returns an empty string. The condition shown above generally excludes both from the displayed list because both evaluate as empty text for this comparison. Cells containing a space are not empty, however, and can still appear as a seemingly blank item.

Headers, source data, and dynamic results

Usually, start the formula at row 2 when row 1 contains a heading. For example, if A1 is “Product” and the values begin in A2, use A2:A rather than A1:A. SORT does not automatically recognize or protect a header; if you include the heading in the input range, it can be treated like any other value and sorted into the results.

The formula output is a live view, not a replacement for the source data. When a source value changes, the unique list recalculates. If you need a fixed snapshot for a report or export, copy the result and use Edit > Paste special > Values only in Google Sheets. That replaces the formulas in the pasted area with their current displayed values. Keep the original formula or source data if you may need the list to update later.

Also check that the cells under and beside the formula are clear enough for its output to expand. A note, old value, or another formula in the way can block the result. Moving the formula to an unused part of the sheet is often simpler than trying to make a dynamic array fit into occupied cells.

Formula results versus sorting the original data

Use the formula method when you want a separate, automatically updated list. Use the built-in sorting tools when you want to rearrange the original rows. In Google Sheets, select the data and choose Data > Sort range > Advanced range sorting options to select a sort column and, where relevant, indicate that the range has a header row. Sorting the source range changes its row order; it does not by itself remove duplicates.

For a one-time cleanup, Google Sheets also has Data > Data cleanup > Remove duplicates. This works on selected source data rather than generating a live formula result. Read the prompts carefully and make a copy first if preserving every original record matters. Removing duplicates from a table can delete rows, while a UNIQUE formula leaves the source intact.

As a practical choice:

Use SORT(UNIQUE(...)) for a separate list that should refresh as the source changes.
Use the sort-range menu to rearrange existing records without discarding them.
Use Remove duplicates for a deliberate, one-time cleanup after checking which columns define a duplicate.

Common problems and how to fix them

If the formula reports that its result cannot expand, inspect the cells where the output should appear and clear any content in the way. If you see an error saying there are no matches from FILTER, check whether the source range contains any nonblank values. The blank-filtering formula is intended for data with at least one value to return.

Values that look identical may not actually match. A trailing space, an accidental leading space, or a nonbreaking space copied from a website can make two text entries distinct. If a result contains near-duplicates, inspect the source cells and clean the data rather than assuming the sort function is broken. You can test suspicious cells by editing them or comparing their contents in a helper column.

Another common misunderstanding is expecting UNIQUE(A2:B100) to remove repeated names when other columns differ. It compares rows across the supplied range, so a person paired with different departments remains as separate records. Narrow the input to the field you want to deduplicate, or decide on a rule for selecting one full record per person.

Finally, a formula that seems not to include newly added data may use a fixed range that stops too early. Expand the range or use an open-ended column reference such as A2:A. Keep headers out of an open-ended range by starting below the header, and consider a bounded range when the sheet contains a very large number of rows.

Finding values that occur only once

“Unique” can mean either distinct values with duplicates reduced to one copy, or values that appear exactly one time in the source. These are different results. The basic formula keeps one representative of every value, including values repeated many times. If you want only values that occur once, use the third argument of UNIQUE.

With entries in A2:A20, this formula returns values that occur exactly once and sorts them alphabetically:

=SORT(UNIQUE(A2:A20,FALSE,TRUE),1,TRUE)

Here, the second argument leaves the comparison in rows rather than columns, and the third argument requests entries found exactly once. This option is useful for finding one-off codes or names, but it is not a substitute for the usual distinct list. If a value appears twice, it is excluded rather than shown once.

Frequently Asked Questions

How do I sort unique values alphabetically in Google Sheets?
With values in A2:A20, enter =SORT(UNIQUE(A2:A20),1,TRUE) in an empty cell. The formula removes repeated entries and sorts the distinct results in ascending order.

How do I sort unique values from Z to A?
Change the ascending argument to FALSE. For values in A2:A20, use =SORT(UNIQUE(A2:A20),1,FALSE).

Can Google Sheets sort unique rows across multiple columns?
Yes. Give UNIQUE a multi-column range to compare complete rows, then give SORT the column number or numbers to use as sort keys. For A2:B100, =SORT(UNIQUE(A2:B100),1,TRUE,2,TRUE) sorts distinct rows by the first column and then the second.

Why does my unique list contain a blank?
The input range may include empty cells. Filter them out before applying UNIQUE, as in =SORT(UNIQUE(FILTER(A2:A,A2:A<>"")),1,TRUE). A cell containing a space is not truly blank, so check for stray whitespace too.

Does sorting unique values change my original data?
No. A formula creates a separate output list and leaves its source range unchanged. Menu-based sorting changes the order of the selected source rows, while Remove duplicates can delete repeated records.

Google Sheets compared with Excel

These instructions use Google Sheets in a web browser and its SORT, UNIQUE, and FILTER functions. Current Microsoft Excel versions also provide dynamic-array sorting and unique-value functions, but menu names and some formula details differ between Excel and Sheets. Do not assume a Google Sheets menu path will match Excel. If you move a workbook between the products, check the formulas and resulting ranges in the destination app.

For most Google Sheets lists, the simplest reliable pattern is still SORT(UNIQUE(range)), with a blank filter or extra sort keys added only when the data calls for them. Keeping the header out of the range, choosing the right columns to define a duplicate, and leaving room for the results prevents most surprises.

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.