Google Sheets unique values formula maker

Type the cells to read and pick what you need: the list of unique values, the same list sorted A to Z, how many different values there are, each value with its count, only the values that repeat, or only the values that appear once. You get one formula for an empty cell; it updates by itself when the data changes. It is built in your browser and nothing you type here is sent anywhere. Each formula was run in a real Google Sheet on 2 November 2026, including the case, blank-cell and several-column cases below.

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

How to use it

Put the formula in an empty cell, ideally on a new tab. It fills the cells below (and to the right for several columns), so those must be empty: if something is in the way, Sheets shows #REF! with "Array result was not expanded". Do not put it inside the cells it reads. Type the tab name with the range (Data!A2:A) when the formula lives on another tab; names with spaces are quoted for you.

What each formula does

• Unique values (=UNIQUE(range)): one copy of every different value, first one wins. With SORT around it the list comes out in order. Select several columns and a row counts as a repeat only when every column matches.
• How many unique values (=COUNTUNIQUE(range)): a single number. Blank cells are not counted.
• Each value with its count: a QUERY with group by and count(), two columns (Value, Count) with a header row.
• Only values that appear more than once: a QUERY on top of the counts keeps the rows with a count above 1, so each repeated value is listed once. Sheets has no HAVING keyword in QUERY, which is why it takes two steps.
• Only values that appear exactly once (=UNIQUE(range, FALSE, TRUE)): the third argument drops every value that occurs more than once, including its first copy.

The catches (found by testing in a real Sheet)

• UNIQUE treats Apple and apple as different values. So does COUNTUNIQUE and the group by in QUERY. Tick "Ignore upper and lower case" to merge them; the price is that the list shows lower case. SORT itself ignores case when ordering, so Apple and apple sit next to each other.
• Blank cells. Without "Leave out blank cells" the list starts with an empty row. COUNTUNIQUE skips blanks either way.
• Extra spaces and look-alikes. "Apple" and "Apple " (trailing space) are different values. Clean them first, for example with =TRIM(A2) in a helper column.
• The result cannot be edited and has no formatting. It is a live copy of values: edit the original cells, and format the result yourself.
• Separators. If your sheet's locale writes decimals with a comma (File > Settings), Sheets wants ; between arguments: switch the separator above.

When the unique list should also stay sorted in the original

These formulas make a separate list. To keep the rows of the original table sorted by themselves, use the sort formula generator for a sorted copy or the auto sort script generator for the table itself.

No code: KeepSorted

The add-on sorts the original table in place without code: no second copy, so formatting, checkboxes and notes move with their rows and people keep editing the same table. Select the range, pick the sort columns, Save. Free: one area per tab, up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): up to 5 sort columns, custom orders like High > Medium > Low, ticked checkbox rows to the bottom, stacked tables sorted separately, and sorting after form responses, other editors' changes and an hourly check.

More KeepSorted tools and guides

• FILTER formula builder (text, number, date conditions)
• RANK formula builder (ties, groups)
• Sort formula generator (SORT, SORTN, QUERY order by)
• Auto sort script generator (Apps Script that sorts the original)
• Sort by last name
• Auto-sort alphabetically (A to Z)
• StaySorted alternative: an honest comparison

Sources

• UNIQUE function | COUNTUNIQUE function | QUERY function | Query language (group by, count, label) | SORT function | FILTER function