Google Sheets UNIQUE Function: How to Find Distinct Values

To list distinct values in Google Sheets, enter =UNIQUE(A2:A) in an empty cell, replacing A2:A with your data range. The formula returns each different value once, in the order it first appears. Before you start, make sure the cells where results will appear are clear; the list expands automatically beneath the formula.

What You’ll Need

A Google Sheets spreadsheet open in a web browser, with the data you want to check.
A blank cell for the formula and enough empty cells below or beside it for the results to expand.
A clear idea of what counts as a duplicate: a repeated value in one column, or a repeated combination of values across several columns.

The instructions below use Google Sheets, not Excel. Excel has a similarly named UNIQUE function, but this guide uses Google Sheets’ formula syntax and behavior.

Step-by-Step

Check the data you want to summarize. Find the column or table that contains the values. For a single-column list, include the first data cell and the last row you want to inspect. If the first row is a heading, do not include it in the data range. You can use an open-ended range such as A2:A to include future entries in column A, or a bounded range such as A2:A100 to keep the formula focused on a specific block. Check for inconsistent typing, such as “North” and “north”; those may look similar to a person but can be treated as distinct text values.
Choose an output cell. Click an empty cell outside the source data, such as C2 if the source is in column A. Leave the cells below it available. UNIQUE returns a result array, so Sheets fills the neighboring cells in the needed direction. You generally enter the formula once; do not copy it down one row at a time. Keeping the output away from the source also makes it easier to read and reduces the chance that the results will overwrite data.
Enter the basic UNIQUE formula. With product names in A2:A8, where the values are North, South, North, East, a blank, South, and West, enter this in an empty cell: =UNIQUE(A2:A8). Sheets returns North, South, East, a blank result, and West, in that first-seen order. Repeated North and South entries appear only once. If you prefer to include future values from the same column, with product names starting in A2 and later rows reserved for new entries, use =UNIQUE(A2:A). The open-ended version is convenient, though a bounded range can be more efficient in a very large sheet.
Choose whether blanks should appear. A source range containing empty cells can produce an empty-looking item in the unique list. If you want to omit blanks, with the same product names in A2:A8, enter =UNIQUE(FILTER(A2:A8,A2:A8<>"")). FILTER first keeps only nonblank cells, then UNIQUE removes repetitions. The resulting list is North, South, East, and West. Use this version when a clean list is needed for a dropdown or report. If there are no nonblank values to keep, FILTER may return an error rather than a list; see Troubleshooting below.
Return values that occur exactly once, if that is what you mean. This is different from ordinary distinct values. With North, South, North, East, South, and West in A2:A7, enter =UNIQUE(A2:A7,FALSE,TRUE). The second argument, FALSE, tells Sheets to compare rows vertically; the third, TRUE, requests only entries that occur exactly once. The result is East and West. The basic formula would instead return North, South, East, and West. Use exactly_once only when repeated items should be excluded entirely, not when you merely want one copy of each.
Make UNIQUE compare complete rows. When a record has multiple fields, you may want to remove only rows that match across every included column. With customer-and-city records in A2:B6, such as Lee–Dover, Kim–Salem, Lee–Dover, Lee–Reno, and Kim–Salem, enter =UNIQUE(A2:B6) in a blank area. The output contains Lee–Dover, Kim–Salem, and Lee–Reno. A row is considered a duplicate when the values across the included columns match; two people with the same name but different cities remain separate records. Include every column that should define whether a record is the same.
Use the horizontal option for data arranged across columns. UNIQUE normally compares rows. If the values run left to right in a row, change its second argument to TRUE. With department labels in B1:F1, enter =UNIQUE(B1:F1,TRUE) in an empty cell. The distinct labels return across the row, rather than down a column. This argument changes the direction of comparison; it does not mean “keep values that occur exactly once.” For that separate behavior, the third argument is used.
Sort the results only if needed. UNIQUE preserves the order in which each value first appears, which is useful when the source order matters. If you want an alphabetical list instead, with product names in A2:A8, enter =SORT(UNIQUE(A2:A8)). The result is sorted after duplicates are removed. Because SORT can change the order whenever the source changes, use the unsorted version when you need the first-seen order.

Google Sheets recalculates the output as source values change. If you add a new value within the referenced range, it can appear in the list; if you used a fixed range and add data below it, expand the range or switch to an open-ended reference. Menu labels and formula arguments may vary with your spreadsheet’s locale, but the function itself is available in Google Sheets.

Common Mistakes

Confusing distinct values with values that appear once. By default, UNIQUE returns one copy of every value, including values repeated many times. The third argument TRUE changes the result to entries that occur exactly once. Check which outcome you need before using it.
Including a heading in the range. If the range starts at A1 and A1 contains “Product,” that heading is treated as a value and appears in the output. Start at the first data row, or deliberately include the heading only if you want it listed.
Leaving cells in the output area occupied. A formula may fail to expand if another value, formula, or merged cell blocks the results. Put the formula in a clear area and remove obstructions from the cells where the output needs to go.
Assuming similar-looking text is identical. Extra spaces, punctuation, different spellings, or different capitalization can affect whether entries are considered duplicates. Clean the source data when entries that should match are appearing separately.
Using a range that stops too soon. A formula using A2:A50 ignores a new entry in A51. Expand the range or use A2:A when new records are added regularly. Avoid including unrelated columns or large areas without a reason.

Tips

Use a bounded range for a small, stable table. It clearly shows what data is included and avoids referring to more cells than necessary. An open-ended column range is often more convenient for a list that grows over time.
Keep the source and output separate. A dedicated results column makes the formula easier to audit and helps avoid accidental edits to the original data.
Use UNIQUE as the starting point for a dropdown. Put the distinct list in a helper area, then select the cells that should have the dropdown and choose Data > Data validation. Set the criteria to a dropdown from a range and select the output range. If that list is meant to exclude blank entries, use the FILTER version above.
Decide whether to preserve source order. The standard function keeps the order of first occurrence. Add SORT only if a sorted list is more useful than the original sequence.
Test your definition of a duplicate with a small example. For multi-column records, ask whether a matching name alone is enough, or whether the name and another field must both match. Adjust the included range to represent that definition.
Check your spreadsheet locale if a formula will not parse. Some locales use semicolons instead of commas between arguments. If Sheets flags a formula with a parse error, follow the separator style used by other formulas in that spreadsheet.

Troubleshooting

The formula shows a parse error. Confirm that the function name is spelled UNIQUE, parentheses are balanced, and the range uses a colon, as in A2:A8. Check whether your locale expects semicolons between arguments instead of commas. For example, the three-argument version may need locale-appropriate separators. Do not add quotation marks around a cell range.

The formula is entered, but the results do not appear. Look for text, another formula, merged cells, or other content in the cells where the output should expand. Clear the blocked area or move the formula to a larger empty area. Also check whether the result is simply lower in the column or farther across the row than expected.

Duplicates still appear in the list. They may not be exact matches. Compare the source entries for extra spaces, spelling differences, punctuation, or other inconsistencies. If the data has unwanted leading or trailing spaces, clean those values in the source or in a separate helper column before applying UNIQUE. Also confirm that the range includes the columns you intend to compare.

Expected values are missing. Check the range boundaries and make sure the formula points to the correct sheet and cells. A fixed range will not include additions below its last row. If you used FILTER to remove blanks, confirm that the condition is not excluding legitimate values and that nonblank entries actually exist in the selected range.

You see an empty-looking entry. The source may contain blank cells, or cells with spaces that look blank but are not empty. Use the FILTER-and-UNIQUE approach to omit truly blank cells. If a cell contains spaces, remove or clean the spaces first; a test for an empty string may not treat a space as blank.

The output order changes. The standard UNIQUE formula follows first appearance in the source, so changing the source order can change the output. If you added SORT, the results are deliberately ordered by value instead. Remove SORT if preserving the source sequence is important.

Frequently Asked Questions

Does UNIQUE remove duplicates from my original data?
No. It returns a separate list and leaves the source cells unchanged. To replace or edit the original data, make those changes separately and keep a copy if the original order or repeated entries matter.

Does UNIQUE keep the first occurrence?
It returns each distinct item once in the order that item first appears. It does not delete later occurrences in the source. Add SORT around the result if you want alphabetical or numerical order instead.

How do I list only items that appear one time?
Use the third argument TRUE. With the values in A2:A7, for example, =UNIQUE(A2:A7,FALSE,TRUE) returns only entries whose frequency in that range is one. The standard two-argument-or-shorter version instead includes one copy of repeated items.

Can UNIQUE work with more than one column?
Yes. Give it a multi-column range, such as A2:B20, to return distinct rows based on the combination of values in those columns. It removes a repeated row, not every row that happens to share just one field.

Why is there a blank in my result?
The source range likely includes empty cells. Use FILTER to exclude empty-string cells before UNIQUE, and check for cells containing spaces, which are not the same as truly empty cells.

Can I use UNIQUE in Excel too?
Excel also has a UNIQUE function in Microsoft 365 and newer supported versions, but its availability and behavior depend on the Excel version. This guide’s steps and Google Sheets menus apply to Sheets in a web browser.

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.