The Tedium of “Remove Duplicates”
A frequent task in data analysis is extracting a clean list of distinct items from a massive, messy dataset. For example, you have a spreadsheet with 5,000 sales records, and you need a simple, deduplicated list of exactly which individual products were sold that day.
Historically, the only way to accomplish this in Excel was to highlight the column, copy it, paste it to a new location, navigate to the Data tab, and click the “Remove Duplicates” button. This is a destructive, static process. If a new product is added to the raw data ten minutes later, your extracted list is immediately outdated. You have to delete the list and perform the entire copy-paste-remove process again.
With the introduction of Dynamic Arrays in Microsoft 365, Excel completely modernized this workflow with the UNIQUE function. It analyzes an array of data and instantly “spills” a perfectly deduplicated list into adjacent cells. Most importantly, it is alive: as the source data changes, the deduplicated list updates in real-time.
The Syntax of UNIQUE
The function is incredibly simple to implement. It has one required argument and two optional ones:
=UNIQUE(array, [by_col], [exactly_once])array: The range of data you want to deduplicate (e.g., A2:A5000).[by_col]: (Optional) By default (FALSE), it compares rows against each other. If set to TRUE, it compares columns against each other.[exactly_once]: (Optional) By default (FALSE), it returns a list of every distinct item. If set to TRUE, it will only return items that appeared exactly one single time in the source data.
Step-by-Step Examples
Example 1: The Basic Deduplication
Assume your 5,000 raw sales records are in Column A (A2:A5000). You want a clean list of products starting in cell D2.
- Click in cell D2.
- Type the formula:
=UNIQUE(A2:A5000) - Press Enter.
Excel will instantly scan all 5,000 rows and spill a concise, deduplicated list of products down Column D.
Example 2: Combining UNIQUE with SORT
The output of the UNIQUE function appears in the exact chronological order that the items first appeared in the raw data. Usually, you want your extracted list to be alphabetized.
Because Dynamic Arrays are designed to nest seamlessly, you simply wrap the UNIQUE function inside a SORT function:
=SORT(UNIQUE(A2:A5000))Now, your list is both perfectly deduplicated and perfectly alphabetized (A to Z).
Example 3: Multi-Column Deduplication
The UNIQUE function isn’t limited to a single column. It can analyze entire rows across multiple columns to find unique combinations.
Suppose Column A contains the “Region” (East, West) and Column B contains the “Salesperson” (Smith, Doe). You want to see the unique pairings of Region and Salesperson, stripping out all the redundant daily sales logs.
You highlight both columns in the array argument:
=UNIQUE(A2:B5000)Excel will spill a clean, two-column table. If “East” and “Smith” appears 50 times in the raw data, it will only appear exactly once in the dynamic output.
The Power of Excel Tables
If you hard-code the range A2:A5000, your formula will break if you paste 6,000 records into the sheet tomorrow. The UNIQUE function works best when paired with an official Excel Table.
If you highlight your raw data and press Ctrl+T to format it as a Table (named “SalesTable”), you can write your formula like this:
=UNIQUE(SalesTable[Product])Now, as new rows are endlessly added to the bottom of the “SalesTable,” the UNIQUE function automatically sees the new data and updates your dashboard instantly, without you ever touching the formula again.
Conclusion
The UNIQUE function replaces the tedious, static “Remove Duplicates” tool with an elegant, mathematically robust array formula. By pairing it with functions like SORT and official Excel Tables, you can build self-maintaining dashboards that extract and organize distinct data in real-time.