How to Find Unique Values in Google Sheets

To list distinct values without changing your source data, enter =UNIQUE(A2:A10) in an empty cell, replacing the range with your data. Google Sheets returns each different value once, in the order it first appears. If you want to remove duplicate entries from the original range instead, use Data > Data cleanup > Remove duplicates.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
A range of cells containing the values you want to check, such as names in A2:A10. Include the data, not its column heading, unless you plan to identify the header in the cleanup dialog.
An empty area for formula results. The UNIQUE formula fills cells below its starting cell, so leave room for the list to expand.

You do not need to install an add-on. The UNIQUE function is built into Google Sheets. The steps below focus on finding distinct values in a column, with options for finding values that occur exactly once and for handling multiple columns.

Step-by-Step

Choose whether to make a separate list or edit the source. A formula is usually the safer choice: it leaves the original data intact and updates the result when the source changes. Use Remove duplicates when you intentionally want to delete repeated rows from a selected range. If the original data matters, make a copy of the sheet before using that command.
Identify the data range. Find the first and last cells containing the values you want to compare. For example, if a heading is in A1 and the entries run from A2 through A10, use A2:A10. Excluding the heading prevents it from appearing as one of the results. If the list will grow, you can select a larger range, but be aware that unused blank cells may affect the output.
Select a blank starting cell for the results. Click a cell outside the source range, such as C2. Make sure the cells below it are empty for at least as many rows as you expect unique results. A formula result needs room to expand; it cannot replace existing content in its output area.
Enter the UNIQUE formula. With source values in A2:A10 and the result starting in C2, enter =UNIQUE(A2:A10) and press Enter. Sheets displays each distinct entry once. For example, if A2:A8 contains Cedar, Maple, Cedar, Birch, Maple, Ash, Birch, the output is Cedar, Maple, Birch, Ash. The output follows the order of first appearance in the source; it is not automatically alphabetical.
Check the result and the source range. Confirm that the first result appears in the cell where you entered the formula and that the remaining values fill the cells below. If the list looks incomplete, inspect the source range for entries outside its boundaries. If the result contains an unexpected heading or blank, adjust the range or use the blank-handling option below.
Exclude blank cells when needed. A source range that includes empty cells can produce an empty result in the list. With values in A2:A10 and the output beginning in C2, enter =UNIQUE(FILTER(A2:A10,A2:A10<>"")) to filter out empty cells before listing distinct values. The comparison to an empty string excludes cells that are blank. This is useful if you deliberately selected a longer range to accommodate future entries.
Return only values that occur once. Distinct means each value appears once in the result, even if it appeared several times in the source. If instead you want to find entries that occur exactly one time in the source, use the third UNIQUE argument. With source values in A2:A10 and output in C2, enter =UNIQUE(A2:A10,FALSE,TRUE). For the Cedar/Maple/Birch/Ash example above, this returns Ash because it is the only value that appears once. FALSE tells the function to compare down the column; TRUE requests values that occur only once.
Sort the distinct list if that is more useful. UNIQUE preserves the source order, which is helpful when that order has meaning. To display distinct names alphabetically instead, with values in A2:A10 and output in C2, enter =SORT(UNIQUE(A2:A10)). Sorting changes the displayed order, not the source data. If you also need to exclude blank cells, use the filtered UNIQUE approach and sort its result.
Find unique combinations across columns, if required. UNIQUE can compare complete rows, not just individual cells. With a table of paired values in A2:B10 and the output starting in D2, enter =UNIQUE(A2:B10). Sheets returns each distinct row combination once. For instance, a repeated pair of a city and a department is removed from the result, but the same city paired with a different department remains. Select enough empty columns and rows for the result.
Use Remove duplicates to clean the selected data itself. First select the range to clean. In the browser menu, choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selected data has a header row, then choose which columns should be checked. Click Remove duplicates and review the confirmation. Sheets keeps one occurrence of matching data and removes the other matching rows from the selected range. This is a direct change to the data, unlike a UNIQUE formula.
Choose the comparison columns carefully in the cleanup dialog. If you select every column in a table, Sheets looks for repeated rows across all those columns. Two rows with the same name but different order dates will not be identical if the date column is included. To remove repeated names regardless of other details, select or configure the name column as the comparison column, and think carefully about what information should remain in each row. Check the result before using the cleaned data elsewhere.

For a quick, non-destructive list, the formula is generally the best starting point. For permanent cleanup, use the menu command only after deciding what counts as a duplicate and which row information should be retained.

Common Mistakes

Confusing distinct with exactly once. =UNIQUE(A2:A10) returns every different value one time. It does not omit values that repeat in the source. Use =UNIQUE(A2:A10,FALSE,TRUE) when the goal is to show only values whose source count is one.
Including the heading in the range. If the range starts at A1, the heading may appear as a result. Start at the first data row, or deliberately account for a header when selecting data for Remove duplicates.
Putting something in the formula’s output area. Existing values, spaces, or other formulas can prevent the list from expanding. Choose an empty area and clear any blocking cells if Sheets reports that it cannot expand the result.
Deleting duplicates from the wrong selection. Remove duplicates acts on the selected data. Selecting only one column in a table can leave related information in other columns misaligned with the cleaned list. Select and review the full intended range, and confirm the comparison columns in the dialog.
Assuming visually similar entries are identical. Extra spaces, punctuation, or different spelling can make two entries distinct to Sheets. For example, a name with a trailing space may not match the same name without one.

Tips

Keep the original data and place formula results on another sheet or in a separate column. That makes it easier to compare the distinct list with the source and reduces the risk of overwriting data.
If your list changes often, use a range with room for new entries and exclude blanks. For example, with data starting in A2, =UNIQUE(FILTER(A2:A,A2:A<>"")) checks down the column while leaving empty cells out of the results. Avoid placing the output in column A, because the formula would then overlap its own source.
For a recurring report, keep the formula rather than copying and pasting its current results. The formula recalculates when its source values change, while pasted values do not update automatically.
Before removing duplicates, consider whether capitalization, spaces, and punctuation should count as differences for your task. If entries need standardizing first, clean the source consistently and then create the distinct list or run the cleanup command.
Google Sheets normally uses commas between formula arguments, as shown here. Depending on your spreadsheet’s locale, it may expect semicolons instead. If a correctly written formula is rejected, check the spreadsheet’s locale settings and use the separator it requires.
In Excel, UNIQUE is also available in Microsoft 365 and Excel 2021 or later, but menu names and dialogs can differ. The formulas in this guide are for Google Sheets in a web browser; use the Google Sheets menu paths shown here rather than Excel ribbon instructions.

Troubleshooting

The formula shows an error instead of a list. Check that the range is spelled correctly and that parentheses are balanced. If the error indicates a formula parse problem, confirm that your spreadsheet locale expects commas or semicolons between arguments. Also verify that UNIQUE and FILTER are entered as function names, without misspellings.
Only one result appears, or the output will not expand. Look at the cells below and beside the formula for existing content, including cells that appear blank but contain spaces or formulas. Clear the blocked cells or move the formula to an empty area. For a multi-column source, leave enough empty columns for the returned rows.
Duplicates still seem to appear. Compare the entries closely. Leading or trailing spaces, inconsistent punctuation, and spelling variations may mean the text is not actually identical. Standardize the source values before applying UNIQUE if those differences should be ignored. Also check that the formula covers all relevant source rows.
Blank results appear. If you included empty cells in the source range, use the FILTER version to remove them from the output. Make sure the source and filter ranges have the same size; in the examples, both refer to column A starting at row 2.
Remove duplicates removed more or fewer rows than expected. Undo immediately if the result is not right, then review the selected range and the columns checked in the dialog. If you meant to identify repeated names only, but checked all table columns, rows with different details may have been kept. If you checked only one column in a multi-column table, related row data may not have been handled as intended. Restore a backup if undo is no longer available.
The formula result does not change as expected. Confirm that the edited cells are inside the range referenced by the formula. A formula using A2:A10 will not include a new entry typed in A11. Expand the range or use an open-ended range, and make sure the formula output is not being mistaken for part of the source.

Frequently Asked Questions

Does UNIQUE delete duplicate values from my source data?
No. It creates a separate result list and leaves the source range unchanged. Use Data > Data cleanup > Remove duplicates if you intend to remove repeated data from the selected range.

How do I find values that appear only one time?
Use the third argument set to TRUE. With entries in A2:A10, =UNIQUE(A2:A10,FALSE,TRUE) returns only entries whose value occurs once in that source range.

Can I find unique values from more than one column?
Yes. Use a multi-column range such as A2:B10. UNIQUE compares whole rows, so it returns each distinct combination of values in those columns once.

Can I make the results alphabetical?
Yes. Wrap UNIQUE in SORT, for example =SORT(UNIQUE(A2:A10)) when the source is A2:A10. This sorts the output list while leaving the original range unchanged.

Why does my unique list contain a blank?
The range may include empty cells. Filter blanks out before applying UNIQUE, using =UNIQUE(FILTER(A2:A10,A2:A10<>"")) for data in A2:A10.

Will the unique list update when I add new data?
It updates when changes fall within the range referenced by the formula. If the formula refers only to A2:A10, a new value in A11 is outside that range. Expand the reference or use an open-ended column range, while keeping the formula’s output in a different column.

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.