Google Sheets UNIQUE Function: Remove Duplicate Values

To list distinct values in Google Sheets, put your source data in a range and enter the UNIQUE function in an empty cell. For example, with Red, Blue, Red, Green, Blue, Yellow, and Green in A2:A8, enter =UNIQUE(A2:A8) in an empty cell; the result is Red, Blue, Green, and Yellow, in the order each value first appears. The results update when the source range changes.

What You’ll Need

A Google Sheets spreadsheet open in a web browser, with the data you want to check.
An empty cell with enough clear space below it—or to the right if you are returning unique columns—for the results to expand.
A clear idea of what counts as a duplicate. UNIQUE can compare a single column, full rows across several columns, or columns across a range.

Step-by-Step

Identify the source range. Decide which cells contain the values you want to deduplicate. A range such as A2:A100 includes one column; A2:C100 includes three columns and compares complete rows. Avoid including a heading unless you want the heading treated as one of the values. If new rows will be added regularly, you can use an open-ended range such as A2:A, which starts below the header and continues down the column.
Choose a clear place for the output. Click an empty cell where the first result should appear, such as D2. UNIQUE returns an array of results, so the cells below the formula cell need to be available. For a horizontal result, make sure the cells to its right are clear. The source range and output range can be on the same sheet or on different sheets.
Enter the basic formula for one column. With the example values Red, Blue, Red, Green, Blue, Yellow, and Green in A2:A8, type =UNIQUE(A2:A8) into D2 and press Enter. The results are Red, Blue, Green, and Yellow. UNIQUE keeps the first occurrence of each value and leaves later repeats out of the returned list; it does not sort the results alphabetically.
Use the range that fits your real data. Replace A2:A8 with the range containing your list. For example, if product names are in B2:B50, use that range in the formula. With names in B2:B50, =UNIQUE(B2:B50) returns each distinct name once, in first-seen order. An open-ended range can save you from updating the formula when you append rows, though it also includes any future entries added anywhere in that column below the starting row.
Return distinct records from multiple columns. UNIQUE compares rows as complete combinations when you supply a multi-column range. For example, with Mia and 12 in A2:B2, Noah and 8 in A3:B3, Mia and 12 in A4:B4, Ava and 15 in A5:B5, Noah and 8 in A6:B6, and Mia and 14 in A7:B7, enter =UNIQUE(A2:B7) in an empty cell. The output rows are Mia–12, Noah–8, Ava–15, and Mia–14. The repeated Mia–12 and Noah–8 pairs appear only once. Mia–14 remains because the complete row differs.
Choose whether to compare rows or columns. UNIQUE has optional arguments for comparison direction and for keeping only items that occur exactly once. With labels in A1:D2 arranged as North/North/South/South in row 1 and 10/10/20/30 in row 2, enter =UNIQUE(A1:D2,TRUE) in an empty cell. The function compares columns and returns three distinct columns: North–10, South–20, and South–30. The second argument TRUE means “compare by column”; it does not mean “keep only one-off values.”
Keep only values that appear once in the source. The third argument, exactly_once, changes the meaning: it excludes any value or row that occurs more than once. With Red, Blue, Red, Green, Blue, Yellow, and Green in A2:A8, enter =UNIQUE(A2:A8,FALSE,TRUE) in an empty cell. The result is Yellow, because it is the only value in that example that occurs exactly once. The second argument FALSE keeps the normal row-by-row comparison, while the third argument TRUE requests only one-off entries.
Check the output and keep the source intact. Confirm that the returned values match your intended comparison, especially when using multiple columns. UNIQUE creates a formula-driven result; it does not delete or edit the original records. If you need a fixed list rather than a live result, copy the output and use Edit > Paste special > Values only in the destination cells.

The function’s full pattern is =UNIQUE(range,[by_column],[exactly_once]). The square brackets indicate optional arguments; do not type the brackets into the formula. You can leave optional arguments out when the default behavior—compare rows and include values that occur more than once in the distinct list—is what you want.

Common Mistakes

Expecting the output to be sorted. UNIQUE keeps the order of first occurrence. If you want an alphabetized result, sort the returned list separately or use a formula that combines UNIQUE with SORT. Sorting is a different operation and can change the order people expect from the source.
Including the header by accident. A range starting at A1 includes the column label, so that label appears in the output as a value. Start at A2 when row 1 contains headings, or deliberately include the heading if you want it included.
Comparing a whole row when you meant to compare one field. If you pass A2:C100, a record is considered a duplicate only when all cells in the row match. Two rows with the same name but different dates or amounts are distinct. Select only the column you want to deduplicate if those other fields should not affect the comparison.
Confusing the optional arguments. The second argument controls whether the function compares columns instead of rows. The third argument controls whether to return only entries appearing exactly once. For a typical vertical list, omit both arguments or use FALSE for the second one.
Putting notes or other values in the output area. The formula needs room to expand. A value in a cell where a result should appear can prevent the array from expanding and trigger an error. Clear the relevant cells rather than deleting or changing the source data.
Assuming visually similar text is identical. Extra spaces or inconsistent typing can make entries behave differently than expected. For instance, a name with a trailing space may look like the same name but be stored differently. Clean or standardize the source if you want those entries treated consistently.

Tips

Put the formula outside the source range. If the formula sits inside the cells it is trying to read, you can create a circular reference or disrupt the source data.
Use an open-ended column range such as A2:A when new records are added often. Use a bounded range such as A2:A500 when you want to restrict how much of the sheet is considered.
To create a distinct list of combinations, include every field that defines a unique record. For example, a product and its size may form the combination you need, even when the product name alone repeats.
UNIQUE can return a blank as one of its distinct results if the selected range contains blank cells. If blanks are meaningful in your list, leave that behavior as it is; if not, clean the source or use a filtering approach before returning distinct values.
Use the formula when you want the result to keep updating. For a one-time cleanup of source data, Google Sheets also provides Data > Data cleanup > Remove duplicates. That command removes repeated rows from the selected data, so consider making a copy first; it is not the same as producing a separate formula result.
If your spreadsheet uses a locale that expects semicolons between function arguments, Sheets may require semicolons instead of commas in formulas with multiple arguments. The examples here use commas.

Troubleshooting

The formula reports that the array result was not expanded. Look below or to the right of the formula cell for existing values, spaces, or merged cells in the intended output area. Clear the obstructing cells or move the formula to a larger empty area. The output grows or shrinks as the source changes, so leave room for future results too.

The result includes the heading or an unexpected blank. Check the start and end of the selected range. Exclude the heading row if it is not data, and inspect the source for genuinely empty cells. If the output includes a blank, that usually means the selected range contains at least one blank entry; remove it from the source or use a filter to exclude empty cells before applying UNIQUE.

Values you expected to merge still appear separately. Compare the underlying cells, not just how they look. Check for leading or trailing spaces, inconsistent punctuation, and different spellings. Standardize those entries in the source first, or create a cleaned helper column and run UNIQUE on that column.

The list is not in the order you wanted. The default order is first appearance, not alphabetical or numerical order. Sort the resulting list if that is the desired presentation. If order carries meaning—such as the sequence in which categories were first recorded—leave the result unsorted.

The formula is rejected or shows a parse error. Verify that the function name is UNIQUE, the parentheses are balanced, and the selected range uses a colon between its endpoints. In a locale that uses semicolons as argument separators, change commas between arguments to semicolons. Also check that the optional arguments are entered in the correct order: range, by_column, then exactly_once.

Frequently Asked Questions

Does UNIQUE remove duplicates from my original data?
No. It returns a separate, formula-driven list and leaves the source cells unchanged. Use Data > Data cleanup > Remove duplicates if you intend to remove repeated records from selected source data, and make a copy first if you may need the original.

Does UNIQUE keep the first occurrence?
Yes. In its standard mode, it returns each distinct value or row once, in the order of its first appearance. It does not choose the last occurrence or sort the results.

Can UNIQUE return distinct rows using more than one column?
Yes. Give it a multi-column range. It treats each row as a combination of the values across those columns, so a row is repeated only when the complete combination is repeated.

What does the third argument TRUE do?
It asks UNIQUE to return only values or rows that occur exactly once in the source range. It is useful for finding one-off entries, but it is not the usual setting for building a list of all distinct values.

Can I use UNIQUE in Excel too?
Excel also has a UNIQUE function in Microsoft 365 and Excel 2021 or later, but menu details and compatibility can differ by version. This guide’s examples and steps are for Google Sheets in a web browser.

Why does my formula output change after I edit the source?
That is normal: UNIQUE recalculates from the selected range. If you need to preserve a snapshot, copy the results and paste them with Edit > Paste special > Values only.

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.