Google Sheets List Without Duplicates Using UNIQUE

How-to basics · Updated November 20, 2026

To make a list without duplicates in Google Sheets, enter =UNIQUE(A2:A) in an empty cell, replacing A2:A with the range containing your values. Sheets returns one copy of each distinct entry and updates the result when the source data changes.

How the UNIQUE function works

UNIQUE is a built-in Google Sheets function for returning distinct rows from a range. With a single column, it keeps one instance of each value and removes later repetitions from the result. By default, the output follows the order in which values first appear in the source, so the formula does not automatically alphabetize the list.

For example, suppose A2:A7 contains Maya, Luis, Maya, Priya, Luis, and Chen. With those names in A2:A7, =UNIQUE(A2:A7) returns Maya, Luis, Priya, and Chen, in that order. The original cells are unchanged: the formula creates a separate result rather than deleting or editing source data.

Enter the formula in a cell with enough room below it for the results. UNIQUE produces an expanding, or “spilled,” array. If a value or another formula is already in the output area, Sheets may show an array expansion error instead of displaying the full list. Clear the obstructing cells or place the formula somewhere with open space.

Make a clean list and leave out blank cells

If the source range includes empty cells, UNIQUE may include a blank in its output. This can be useful when a blank is a meaningful category, but it is often unwanted in a list of names, products, or locations. Combine UNIQUE with FILTER to exclude empty cells.

With the source values in A2:A, enter =UNIQUE(FILTER(A2:A,A2:A<>"")) in an empty cell. FILTER first keeps cells that are not blank, and UNIQUE then removes repeated entries from that filtered set. Starting at row 2 also avoids including a header such as “Name.”

This formula can use an open-ended range such as A2:A, which covers entries added further down the column. If you prefer a fixed range, use a range such as A2:A100 instead. A fixed range can be easier to inspect, but values entered below its last row will not appear until you expand the reference.

A cell that looks empty may contain a space or a formula that returns an empty string. A test for an empty string excludes ordinary blank cells and formula results that return an empty string, but a cell containing a space is not truly empty. If those values appear in the output, remove the extra spaces in the source or clean the data before making the list.

Sort the results alphabetically

UNIQUE keeps the order of first appearance. To display a deduplicated list in ascending order, wrap the result in SORT. With names in A2:A and no header in that range, enter =SORT(UNIQUE(FILTER(A2:A,A2:A<>""))) in an empty cell. The result excludes blanks, removes duplicates, and sorts the remaining values.

Sorting is a presentation choice, not a requirement for deduplication. Keeping the original order can be helpful when the sequence reflects priority, arrival time, or how a list was entered. SORT is useful when readers need to scan a directory or when you want the output to be consistent regardless of the order in which new items are added.

Google Sheets sorts text according to its own sorting behavior and the spreadsheet’s locale settings. If the ordering of accented characters, numbers stored as text, or mixed types matters, check the displayed output against your needs. Sorting a list does not change or reorder the source range.

Use the right method for rows or columns

If your data has multiple columns, UNIQUE evaluates complete rows by default. With names in A2:A and departments in B2:B, enter =UNIQUE(A2:B) to return each distinct name-and-department combination. Two rows with the same name but different departments remain separate, because the entire row differs.

That behavior matters when a reader says they want “unique names.” If the same person appears in more than one department, a unique-row formula can show the name more than once. To create a list based only on one field, apply UNIQUE to that field—for example, use =UNIQUE(A2:A) for names—rather than passing both columns to the function.

UNIQUE also accepts an optional argument that tells it to compare columns rather than rows. For instance, if values are arranged horizontally across A1:F1, enter =UNIQUE(A1:F1,TRUE) in an empty cell to return distinct columns from that horizontal range. Most everyday lists run down a column, so the default row-based behavior is usually appropriate.

Distinct values are not values that occur once

A common source of confusion is the difference between “return one copy of each value” and “return only values that appeared exactly once.” The normal formula =UNIQUE(A2:A) keeps one copy even when an item occurs several times. For example, if “Maya” appears three times, the result still contains Maya once.

To return only values that occur exactly once, set UNIQUE’s third argument to TRUE. With entries in A2:A, use =UNIQUE(A2:A,FALSE,TRUE). If A2:A contains Maya, Luis, Maya, and Priya, the result is Luis and Priya: Maya is left out because it appeared more than once. The second argument, FALSE, specifies that the function should compare rows rather than columns.

This option is useful for finding one-off entries, but it is not the usual way to create a list without duplicates. For a conventional deduplicated list, leave the third argument out or set it to FALSE. Remember the distinction when matching a report’s wording: “unique list” often means one copy of every value, while “values occurring once” means discard every value with a repeat.

Use the menu to remove duplicates from the original data

A formula is best when you want a separate list that updates with the source. If you instead want to delete repeated records from the data itself, use Google Sheets’ cleanup tool. In a web browser, select the range, then choose Data > Data cleanup > Remove duplicates. Review the selected columns and the header-row setting in the dialog before confirming.

Removing duplicates changes the selected data, unlike UNIQUE. Make a copy of the sheet or range first if you may need the original records. For a table with several columns, consider whether rows should count as duplicates only when every selected column matches, or whether duplication should be decided using just one column. Selecting the wrong columns can remove records that are different in a meaningful way.

The menu is convenient for a one-time cleanup, such as preparing a static list for sharing. It does not keep a deduplicated result linked to future additions. If new source values arrive later, run the cleanup again or use a formula-based output instead. Google Sheets is being described here; Excel has its own Remove Duplicates command and formula behavior, so its menu path is not the same.

Common problems and how to fix them

  • The formula returns an error about expanding. Clear cells below or beside the formula where its results need to appear. Also check for merged cells or other content in the output area.
  • A duplicate-looking value appears twice. The cells may differ because of leading or trailing spaces, punctuation, hidden characters, or different underlying values. Inspect the source and clean inconsistent entries; a visual match does not always mean the cell contents match.
  • A blank appears in the list. Use the FILTER version to omit empty cells, and check whether apparently blank cells contain spaces or formulas.
  • The output does not include a new entry. Confirm that the source range covers the row where the entry was added. An open-ended reference such as A2:A includes later rows in that column.
  • The list is not alphabetized. UNIQUE preserves first-seen order. Wrap it in SORT if alphabetical output is wanted.
  • The source data changed but the formula result did not update as expected. Check that the formula points to the intended tab and range, and that the changed value is within that range. Also check for a blocking cell in the output area.

For reliable results, keep the source data consistent. For example, choose one spelling for each product name, avoid adding spaces by accident, and use a clear header row outside the formula’s input range. These habits reduce apparent duplicates and make the result easier to maintain.

Choosing between a formula and cleanup

Choose UNIQUE when you want a live list beside your original data, need the source records intact, or expect more entries to arrive. It is also a good fit for dropdown-source lists, summary work, and quick checks, provided you place the formula output in a suitable range.

Choose Remove duplicates when you intentionally want to edit the original range and need a one-time result. Before using it, decide what counts as a duplicate and select only the appropriate columns. If each row represents a complete record, comparing all relevant fields is often safer than removing rows based on a single repeated label.

In either case, “duplicate” depends on the question being asked. A repeated customer name may be a duplicate in a contact list, but not in a transaction table where each row records a separate purchase. Keep the full source data when repeated entries have meaning; generate a distinct view rather than deleting useful records.

Frequently Asked Questions

How do I get a unique list in Google Sheets?
With values in A2:A, enter =UNIQUE(A2:A) in an empty cell. Replace the range with your own source range. The result shows one copy of each distinct value in first-appearance order.

How do I remove duplicates but keep the original data?
Use a UNIQUE formula in a separate, empty area of the sheet. It creates a separate result and leaves the source cells unchanged. Use Data > Data cleanup > Remove duplicates only when you want to modify the selected data.

How do I make a unique list without blank cells?
With values in A2:A, enter =UNIQUE(FILTER(A2:A,A2:A<>"")). This filters out empty cells before returning distinct values.

How do I sort a unique list alphabetically?
With values in A2:A, use =SORT(UNIQUE(FILTER(A2:A,A2:A<>""))). It excludes blank cells, removes repeats, and sorts the results.

How do I show only values that occur once?
With values in A2:A, use =UNIQUE(A2:A,FALSE,TRUE). Unlike the standard UNIQUE formula, this omits any value that appears more than once.

Related

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.