Google Sheets: Find Unique Values in a Range Easily

To find unique values in Google Sheets, enter =UNIQUE(A2:A) in an empty cell, replacing A2:A with your range. Sheets returns one copy of each distinct value and updates the result when the source data changes.

How the UNIQUE function works

UNIQUE is a built-in Google Sheets function that extracts distinct rows from a range. With a single-column range, the result is a list of the values that occur in that column, with duplicates reduced to one copy. It keeps the order in which values first appear; it does not automatically sort the results alphabetically or numerically.

For example, if A2:A7 contains Pear, Apple, Pear, Plum, Apple, and Grape, the result is Pear, Apple, Plum, Grape. The output is an array: Sheets fills cells below the formula with the results automatically. Make sure those cells are empty, or the array cannot expand and Sheets may show an expansion error.

With names in A2:A7 and an empty output cell such as C2, enter =UNIQUE(A2:A7). The result is Pear, Apple, Plum, Grape, in first-seen order. A bounded range is useful for a small, fixed list; an open-ended range such as A2:A is convenient when you expect to add more entries over time.

Find values that occur exactly once

There is an important distinction between values that are unique in the everyday sense and values that appear only one time. By default, UNIQUE keeps one representative of every value, even if that value appeared many times. To return only values whose original frequency is exactly one, use the function’s third argument.

With entries in A2:A7 and the output formula in C2, use =UNIQUE(A2:A7,FALSE,TRUE). The second argument, FALSE, tells Sheets to compare rows rather than columns. The third argument, TRUE, requests values that occur exactly once. For the sample list Pear, Apple, Pear, Plum, Apple, Grape, the result is Plum and Grape.

This option is handy for identifying one-off product codes, names, or transactions. It is not a way to remove duplicates while keeping one copy: leave the third argument out, or set it to FALSE, when you want one copy of every distinct value.

Exclude blank cells and headers

If the source range contains empty cells, a unique-values result may include a blank entry. A header can also appear as one of the results if the selected range starts on the header row. Start below the header and filter out blanks when you want a clean list.

With data in A2:A, a header in A1, and the result beginning in C2, use =UNIQUE(FILTER(A2:A,A2:A<>"")). FILTER first keeps only nonblank cells; UNIQUE then reduces the remaining entries to distinct values. The formula uses an open-ended range, so new entries added below the current data are included.

If every cell in the source range is blank, FILTER has no matching rows and can return an error. With data in A2:A and the output in C2, use =IFNA(UNIQUE(FILTER(A2:A,A2:A<>"")),"") to display a blank instead when there are no nonblank entries. This is useful in a report that should remain visually empty until someone enters data.

For a one-time list, the simplest fix may be to select only the data rows rather than include the header. For a reusable formula, filtering blanks is usually clearer and avoids an empty item appearing in a dropdown source or summary.

Return unique rows from multiple columns

UNIQUE can work with more than one column. In that case, it compares whole rows: a row is treated as a duplicate only when its values across the selected columns match a previous row. This is useful for building a list of distinct combinations, such as a customer-and-region pair.

With customer names in A2:A100, regions in B2:B100, and the output starting in D2, enter =UNIQUE(A2:B100). If the same customer appears in the same region several times, that pair appears once. A customer in a different region is a different row and remains in the results. Both columns are returned together, preserving the relationship between each name and its region.

Do not select two columns if you intend to make one combined list of every value regardless of its original column: a two-column UNIQUE formula returns distinct rows, not a single flattened column. Decide whether your goal is distinct records or distinct individual entries before choosing the range.

Sort the unique results

UNIQUE preserves first-seen order, which is often helpful because it reflects the source list. If you need an alphabetized or numerically ordered result, wrap the unique list in SORT. Sorting is a display choice; it does not change the source data.

With values in A2:A and an empty output area starting at C2, use =SORT(UNIQUE(FILTER(A2:A,A2:A<>"")),1,TRUE). The formula excludes blanks, extracts distinct values, then sorts the resulting one-column list in ascending order. Change TRUE to FALSE to sort in descending order. For a multi-column result, SORT can order by a selected column, so choose the sort-column number deliberately.

If you need a distinct list for a chart, dropdown, or summary, keeping the source order may be preferable to sorting. For example, a list of status labels may have a meaningful sequence such as New, In progress, and Complete that does not match alphabetical order.

Formula results versus removing duplicates

A UNIQUE formula creates a separate, automatically updating result. It does not delete or alter repeated entries in the source range. This makes it safer for dashboards and working lists: the original records remain intact, and changes to them flow through to the formula output.

To remove duplicates from the source data itself, select the relevant cells and use Google Sheets in a browser: Data > Data cleanup > Remove duplicates. Review the range and whether it includes a header before confirming. This operation changes the selected data, so make a copy first if the original records must be preserved. When selecting multiple columns, consider which columns define a duplicate; identical names with different order dates, for instance, may be separate records rather than duplicates.

Use a formula when you need a live view, a reusable list, or a result that should leave the source unchanged. Use Remove duplicates when the goal is to clean the actual dataset and you have decided which repeated rows can safely be removed.

Common problems and how to fix them

The formula will not expand: Clear cells below or beside the formula where its array output needs to appear. Merged cells or existing content in the spill area can also prevent the result from filling.
Values that look identical both appear: Check for leading or trailing spaces, line breaks, nonprinting characters, or differences in the underlying values. For example, a name with a trailing space may not match the same name without one. Clean the source consistently before extracting distinct entries.
A blank appears in the output: Exclude empty cells with FILTER, or select a range that does not include unused rows. Be aware that cells containing a formula that returns an empty string can look blank too.
The result includes the column label: Start the range below the header row. A header is ordinary cell content to UNIQUE, so it will be returned if included.
The formula shows a parse error: Check the range reference and parentheses. Depending on spreadsheet locale settings, a sheet may require semicolons instead of commas between function arguments; use the separator expected by that file.
The output does not look sorted: UNIQUE returns first-seen order. Add SORT only when you want a sorted result.

When text cleanup is needed, inspect a few of the suspicious source cells rather than assuming the function has missed a duplicate. A normal-looking space at the end of a label is easy to overlook, yet it can make two entries behave differently in later analysis.

Choosing a range and keeping the list useful

Use a bounded range, such as A2:A500, when the dataset size is known and you want the formula to consider only those rows. Use an open-ended range such as A2:A when new entries are regularly added below the existing list. Open-ended references are convenient, but they can make a workbook harder to understand if the column contains unrelated notes or data farther down.

Place the output in a separate column or on a separate sheet rather than over the source data. Label the output clearly, and leave room for it to grow. If another formula, chart, or dropdown depends on the list, remember that the number and order of results can change when someone edits the source. A sorted unique list can help keep a dropdown predictable, while a first-seen list can preserve the sequence used in the original records.

Google Sheets recalculates the formula as its referenced cells change, so you generally do not need to rerun a manual extraction. For a fixed snapshot to share or archive, copy the result and use Edit > Paste special > Values only. That replaces formulas with their current displayed values; it will no longer update from the source.

Frequently Asked Questions

Does UNIQUE remove duplicates from my original range?
No. It returns a separate result containing distinct entries. To alter the source itself, use Data > Data cleanup > Remove duplicates after confirming the selected range and duplicate criteria.

How do I get one copy of each value, not only values seen once?
Use UNIQUE with the range alone, such as =UNIQUE(A2:A) when the entries are in A2:A. The default behavior keeps one copy of each distinct value, even if it occurs repeatedly.

How do I list values that appear only once?
With the entries in A2:A, use =UNIQUE(A2:A,FALSE,TRUE). The TRUE third argument requests entries that occur exactly once, rather than one representative of every repeated value.

Can I use UNIQUE on more than one column?
Yes. Select the columns together, such as A2:B100, to return distinct rows. Both values in a row are considered, so matching one column alone does not make two different rows duplicates.

Why is a blank or unexpected value in my result?
The selected range may include empty cells, a header, or text with extra spaces or other differences. Start below the header and use FILTER to exclude blanks; inspect source entries that look the same but remain separate.

Is Google Sheets UNIQUE the same as Excel UNIQUE?
The core purpose is similar: both can return distinct values from a range. This page uses Google Sheets syntax and its browser interface; Excel has its own function and interface details, so do not assume every menu step or optional argument behaves identically.

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.