When compiling mailing lists, survey responses, or master inventory sheets, you will inevitably encounter duplicate data. In traditional spreadsheet software, removing duplicates often required complex highlighting rules, manual sorting, and physically deleting rows—a destructive process that ruined the original data source.
Google Sheets solved this elegantly with the UNIQUE function. This dynamic array formula scans a messy column of data and instantly generates a brand new, perfectly clean list containing only one instance of every item, without altering the original raw data.
In this guide, you will learn how to use the UNIQUE function to instantly clean up your spreadsheets.
The Basic UNIQUE Syntax
The UNIQUE function is incredibly straightforward. It only requires one mandatory argument: the range of cells you want to scan.
=UNIQUE(range)Use Case 1: Finding Unique Email Addresses
Imagine you have a master list of 5,000 email addresses in Column A (from cell A2 down to A5000), but you know some customers submitted the form multiple times. You need a clean list for your marketing software.
Click into an empty cell (e.g., cell C2) and type:
=UNIQUE(A2:A5000)Press Enter. Google Sheets will instantly scan all 5,000 rows, discard every duplicate, and spill a perfectly clean list of unique emails straight down Column C. If someone adds a new duplicate to Column A tomorrow, Column C will ignore it. If someone adds a brand new, unique email to Column A, it will automatically appear at the bottom of Column C.
Advanced Usage: Comparing Multiple Columns
The UNIQUE function does not just work on single columns; it can evaluate entire rows of data simultaneously to determine if the entire row is a duplicate.
Suppose you have an event registration sheet where Column A is “First Name” and Column B is “Last Name”. You might have three people named “John” (John Smith, John Doe, John Adams), and one person who registered twice (Jane Doe, Jane Doe).
If you highlight the entire block:
=UNIQUE(A2:B100)The formula looks at the combination of both columns. It sees the three “Johns” as three separate, unique entries because their last names are different. It sees the two “Jane Doe” entries as an exact match across both columns and drops the duplicate, outputting a clean 2D grid.
Combining UNIQUE with SORT
While UNIQUE is excellent for cleaning data, it outputs the unique items in the exact order it first encounters them in the raw list. This usually results in a clean, but completely disorganized, list.
To fix this, you should almost always nest the UNIQUE function inside a SORT function. This forces the clean output into perfect alphabetical order.
=SORT(UNIQUE(A2:A5000))By nesting these formulas, you create a powerful, self-cleaning, alphabetized dashboard that automatically maintains itself as your raw data grows.