How to Get a Unique List With the UNIQUE Function

=UNIQUE(A2:A9) returns each value in the range once, in a new spot, and leaves your original data exactly as it was. It works in Excel and Google Sheets, and it spills the results into the cells below the formula. That is the main reason to choose it over deleting duplicates: nothing is removed, so you can check the list against the source.

1. The arguments

Excel's syntax is =UNIQUE(array, [by_col], [exactly_once]). Google Sheets uses UNIQUE(range, by_column, exactly_once). The two optional arguments mean the same in both apps:

• by_col / by_column: FALSE or omitted compares rows against each other. TRUE compares columns.
• exactly_once: FALSE or omitted returns every distinct entry. TRUE returns only entries that occur exactly once.

That second option is easy to misread. Microsoft calls it "the database concept of unique." Distinct means "one copy of each." Exactly once means "values that were never repeated."

2. A worked example

Suppose A2:A9 holds these eight customer names from a service log:

=UNIQUE(A2:A9) returns five names: Ana, Ben, Cho, Dee, Eli. Ana appears three times and Ben twice, but each is listed once.

=UNIQUE(A2:A9, FALSE, TRUE) returns three names: Cho, Dee, Eli. Ana and Ben are gone because they occur more than once. Microsoft's own example for this setting returns customers who had service one time, which is a handy way to find people who never came back.

The counts check out: 8 entries minus Ana's 3 and Ben's 2 leaves 3 entries that occur only once, and those three names plus Ana and Ben make 5 distinct values.

3. Order of the results

Google states that rows come back in the order they first appear in the source range. In the example that is Ana, Ben, Cho, Dee, Eli. For an alphabetical list in Excel, Microsoft shows nesting it inside SORT: =SORT(UNIQUE(A2:A9)).

4. Comparing across columns

UNIQUE compares whole rows when given several columns. Google's help page uses a grid of colors in B2:D5:

=UNIQUE(B2:D5) returns three rows, because rows 1 and 3 are identical. =UNIQUE(B2:D5, FALSE, TRUE) returns two rows (Blue, Magenta, Blue and Green, White, Green), since the Red-Yellow-Red row occurs twice and is excluded. Setting by_column to TRUE flips the comparison and checks columns against each other instead.

This means a row counts as a duplicate only when every cell in the selected columns matches. If you pick only the Customer column, two different sales to the same customer collapse into one. Select the columns that together define a duplicate.

5. Why "duplicates" sometimes show up anyway

Google's notes cover the usual cause. If rows come back that look identical, check for hidden differences: text cells with trailing spaces, or numbers formatted differently (percentages in one cell, plain decimals in another). "Ana" and "Ana " are different values to the formula even though they look the same on screen.

The fix is to clean the source first, for example with TRIM in a helper column, then run UNIQUE on the cleaned column.

6. Building a list that updates itself

Because UNIQUE is a formula, new rows flow through. Microsoft notes that if your data is an Excel table and you reference it with structured references, the result resizes automatically when rows are added or removed. A plain range will not grow on its own, so extend the reference when you add data below it.

Excel adds a limit: dynamic arrays between workbooks only work when both files are open. If you close the source workbook, linked formulas return #REF! when refreshed.

7. Where it fits

Use UNIQUE when you want a clean list of categories, clients, or SKUs for a dropdown, a summary tab, or a count. Delete duplicates in place only when you are sure you want the extra rows gone for good.

Details above follow the Microsoft and Google help pages as read in October 2026.

8. Key takeaways

• UNIQUE returns a new list and does not touch the source data.
• Omitted exactly_once gives one copy of each value; TRUE gives only values that appear a single time.
• With several columns, a duplicate means the whole row matches.
• In the example, 8 names give 5 distinct values and 3 that appear exactly once.
• Hidden trailing spaces and mixed number formats are the usual reason apparent duplicates survive.