Return Unique Values in Google Sheets: Formulas & Tips

To return each distinct value from a column in Google Sheets, enter =UNIQUE(A2:A) in an empty cell, replacing A2:A with your source range. Sheets displays the results in the order values first appear; use =SORT(UNIQUE(A2:A)) if you want the unique results alphabetically instead.

What UNIQUE returns

UNIQUE is a Google Sheets function that creates a list of distinct entries from a range. It does not change or delete the source data: it returns a formula result in the cell where you enter it, then fills cells below or beside that cell as needed. This makes it useful for building a clean list of customers, product names, categories, locations, or other repeated values while keeping the original records intact.

For example, if A2:A7 contains Tea, Coffee, Tea, Juice, Coffee, and Water, a formula entered in C2 returns Tea, Coffee, Juice, and Water in C2:C5. The first occurrence determines each item’s position. If the source range changes, the results update automatically.

UNIQUE works with a range or an array and can compare rows or columns. Its general syntax is =UNIQUE(range, by_column, exactly_once). The first argument is required. The other two are optional: by_column determines whether Sheets compares columns instead of rows, and exactly_once asks for values that occur only once rather than values that merely have duplicates removed.

Return unique values from one column

For a list in column A with a heading in A1, use the source range beginning at A2 so the heading is not included. Put the formula in a separate, unused column, such as C2. The output expands automatically, so leave the cells below C2 clear.

With names in A2:A20, enter =UNIQUE(A2:A20) in C2. The result is one copy of each name, preserving its first-seen order. If new records will be added over time, an open-ended reference can avoid changing the formula each time. With names starting in A2, use =UNIQUE(A2:A) in C2. Open-ended ranges are convenient, though filtering out blank cells is often preferable for a polished list.

To omit blank entries, use =UNIQUE(FILTER(A2:A,A2:A<>"")) in C2, assuming the source values begin in A2. FILTER first keeps cells that are not empty; UNIQUE then removes repeated values. If the source range contains no nonblank values, FILTER has no matching rows and can return an error. To show an empty result in that situation, use =IFNA(UNIQUE(FILTER(A2:A,A2:A<>"")),""). This IFNA wrapper is useful for a dashboard or template that may temporarily have no data.

Sort the results or show only one-time values

UNIQUE preserves the original order rather than sorting alphabetically. To make an alphabetical list, combine it with SORT. With values in A2:A20, enter =SORT(UNIQUE(A2:A20)) in C2. For numeric values, SORT orders numbers numerically; for text, it sorts alphabetically according to Sheets’ sorting behavior. Sorting changes the displayed order, not the source column.

There is an important difference between “distinct values” and “values that appear exactly once.” In the example Tea, Coffee, Tea, Juice, Coffee, Water, the ordinary UNIQUE result includes Tea and Coffee once each. To return only items whose original frequency is one, use the third UNIQUE argument. With the same source range A2:A7, enter =UNIQUE(A2:A7,FALSE,TRUE) in C2. It returns Juice and Water, because Tea and Coffee each occur more than once. The second argument is FALSE so Sheets compares rows, which is the usual choice for a single-column list.

Find unique rows across multiple columns

UNIQUE can compare complete rows, which is useful when each record is defined by several fields. Suppose A2:B6 contains a city in column A and a department in column B. Enter =UNIQUE(A2:B6) in D2 to return one copy of each distinct city-and-department combination. Two records with the same city but different departments remain separate because the entire row is compared.

For example, “Seattle, Sales” and “Seattle, Support” are distinct rows, while a second “Seattle, Sales” is a duplicate. This is different from extracting unique cities alone: for that, reference only the city column, such as =UNIQUE(A2:A6). Choose the range based on what makes a record meaningfully distinct.

The optional by_column setting is useful when each column, rather than each row, is a list to compare. With values spread across A1:F1, enter =UNIQUE(A1:F1,TRUE) in A3. TRUE tells Sheets to compare columns. Most everyday lists are vertical, so the default FALSE behavior is generally appropriate.

Count distinct values without creating a list

If the goal is a number rather than a visible list, use COUNTUNIQUE. With entries in A2:A20, enter =COUNTUNIQUE(A2:A20) in C2 to count the distinct values in that range. This is handy for a quick total of unique customers or products without displaying all the names elsewhere.

Counting and listing answer different questions. UNIQUE returns the actual entries so they can be reviewed, sorted, or used in another formula; COUNTUNIQUE returns a count. If the count should exclude blank cells explicitly, filter the source first. With values in A2:A20, enter =COUNTUNIQUE(FILTER(A2:A20,A2:A20<>"")) in C2. As with the filtered UNIQUE example, an all-blank range has no FILTER matches, so wrap the formula in IFNA if a no-data state is possible.

Formula results versus removing duplicates

A formula is usually the safer choice when you need a live list and want to preserve the original rows. It updates as source values change and does not remove data. Its output is a calculated array, however, so cells in the output area must be empty; otherwise Sheets cannot expand the result and reports that the array could not be expanded.

If you instead need to edit the source data permanently, Google Sheets has a built-in cleanup command. Select the data range, then choose Data > Data cleanup > Remove duplicates. Review the selected range, indicate whether the data has a header row, and choose which columns should be checked before confirming. Removing duplicates alters the selected data, so make a copy first if the original records need to be retained. This command is not the same as UNIQUE: it removes duplicate rows from the selected range instead of generating a separate live list.

Common problems and clean results

The result will not expand: Clear any values, spaces, or other content in the cells where the output needs to appear. Move the formula to a larger empty area if necessary.
Values that look identical both appear: Check for leading or trailing spaces, inconsistent punctuation, or different spellings. A cell containing “North” plus a trailing space may not behave like a clean “North” entry. You can clean a helper column with TRIM before applying UNIQUE, which is safer than altering source records unexpectedly.
The heading appears in the list: Start the range below the header, for example A2:A rather than A1:A.
A blank appears in the output: Use FILTER with a not-empty condition before UNIQUE, as shown above. This is especially helpful with open-ended ranges that include unused rows.
The formula gives a parse error: Check that the parentheses and commas are in place. Google Sheets’ argument separator can depend on the spreadsheet’s locale; some locales use semicolons instead of commas. If the comma version is rejected, check the locale settings and use the separator expected by that sheet.

For a clean list from inconsistent text, a helper column can normalize spacing first. With raw entries in A2:A20, enter =ARRAYFORMULA(TRIM(A2:A20)) in B2, then run UNIQUE on B2:B20. This removes extra spaces at the beginnings and ends of text and reduces accidental duplicates. TRIM does not correct different spellings or every kind of invisible character, so inspect the source if apparently identical values remain separate.

Google Sheets and Excel differences

In Google Sheets, UNIQUE is entered directly in a cell and its results expand into neighboring cells. Microsoft Excel for Microsoft 365 also has a UNIQUE function with similar use, but this page’s menu instructions refer only to Google Sheets in a browser. Excel’s duplicate-removal command is found on its Data ribbon rather than under the Google Sheets menu path. If a workbook is moved between spreadsheet programs, check formulas and resulting arrays in the destination instead of assuming every feature behaves identically.

In Sheets, you can edit the formula in the formula bar or directly in the cell. The function is not case-sensitive in the same way as a specialized case-sensitive text comparison, so do not rely on UNIQUE to preserve entries that differ only by letter case as separate categories. If case itself matters to your data, verify the output with a small sample or use an explicit case-sensitive method rather than treating a standard unique list as a case-sensitive audit.

Frequently Asked Questions

How do I get unique values in Google Sheets?
Enter =UNIQUE(A2:A) in an empty cell, changing A2:A to match the source range. The results spill into the cells below in first-occurrence order.

How do I get unique values in alphabetical order?
Wrap UNIQUE in SORT. For a list in A2:A20, use =SORT(UNIQUE(A2:A20)). The formula returns the distinct values sorted rather than in their original order.

How do I return values that appear only once?
Set UNIQUE’s third argument to TRUE. For values in A2:A20, use =UNIQUE(A2:A20,FALSE,TRUE). This excludes entries that occur two or more times.

Can UNIQUE remove duplicates from my original data?
No. UNIQUE creates a formula result and leaves the source untouched. To remove duplicate records from the selected data itself, use Data > Data cleanup > Remove duplicates, and review the range before confirming.

How do I exclude blank cells from a unique list?
Filter out empty cells before applying UNIQUE. For a source list starting in A2, use =UNIQUE(FILTER(A2:A,A2:A<>"")). If the range might be entirely blank, wrap the formula in IFNA to avoid showing a no-matches error.

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.