“`html
When working with large data tables in Excel, duplicates often sneak in unnoticed. Imagine a client file where some rows repeat: addresses, product references, names… To get a clear view, sometimes it’s enough to extract the unique list of elements. With the UNIQUE function, introduced in recent versions of Excel, this operation becomes child’s play. In this article, we will break down its syntax, illustrate its use through concrete cases, and offer some tips to combine it with other Excel functions.
1. Presentation of the UNIQUE function
1.1 What is UNIQUE?
UNIQUE is a dynamic function that returns an array of distinct values from a given range. Rather than a simple sort or conditional formatting, UNIQUE automates the detection and extraction of duplicates, without VBA macros or tedious manipulations.
1.2 Compatible versions
UNIQUE is part of the “Dynamic Array Functions” available in Office 365 (Excel 2019 and later). If you use Excel 2016 or earlier, you will need to resort to alternative methods (see the “Workarounds” section below).
2. Syntax and parameters
The syntax of UNIQUE is very readable:
| Formula | Description |
|---|---|
=UNIQUE(plage) |
Returns all distinct values from range. |
=UNIQUE(plage; par_colonne) |
If by_column is TRUE, extraction is done column by column. By default, it processes each row. |
=UNIQUE(plage; par_colonne; filtrer_les_nulles) |
If ignore_blanks is TRUE, empty cells are excluded. |
2.1 Basic examples
Suppose the list in A2:A12 contains city names. By entering =UNIQUE(A2:A12) in C2, Excel will “spill” the different cities without duplicates into column C.
2.2 Advanced options
• Extract distinctly by column: =UNIQUE(B2:D10; TRUE) returns a unique array for each column B, C, and D.
• Exclude empty cells: =UNIQUE(A2:A12;; TRUE).
3. Practical cases and examples
3.1 Product list without repetitions
In an order tracking file, the Product column often contains multiple identical occurrences. By placing the UNIQUE formula in a separate sheet, you instantly generate a unique catalog:
- Get a consolidated view before creating a pivot table.
- Send this list to a marketing department for label printing.
- Combine with the SUMIFS function to get total sales per product.
3.2 Group unique emails before sending
When collecting email addresses for a campaign, you want to avoid sending the same message multiple times. UNIQUE + FILTER allows you to clean your database before importing into Outlook or Mailchimp.
3.3 Mixing with other functions (LEFT, SEARCH…)
Sometimes you want to extract multiple fields from a text string, then remove duplicates. You then combine UNIQUE with:
- LEFT or RIGHT to cut codes.
- MID to extract a specific segment.
- SEARCH or FIND to locate a separator.
Practical example: dissect an item code “ABC-12345” into family code “ABC” then list the unique families.
4. Tips and best practices
4.1 Structured ranges
Use Excel table ranges (Ctrl+T) so that your formulas automatically adapt to added rows or columns.
4.2 Error handling
In case of an empty range or error, consider wrapping UNIQUE in IFERROR:
=SIERREUR(UNIQUE(A2:A100); "Aucune donnée")4.3 Dynamic formatting
Thanks to “Dynamic Array”, a simple conditional formatting applied to the first cell of the range is enough to automatically style the entire unique list.
5. Limitations and workarounds
If you do not have Office 365 or Excel 2019+, UNIQUE does not exist. You will have to resort to:
- A classic array formula combining INDEX, MATCH, ROW and FREQUENCY.
- A VBA snippet that scans the column and builds a distinct array.
- The built-in advanced filter of Excel (Data → Advanced → Copy to another location → Unique records only).
6. Quick FAQ
How to automatically update the UNIQUE list?
As long as you work within a structured table or with named ranges, adding new entries will update the dynamic list.
Is it possible to sort at the same time as extracting?
Yes: combine UNIQUE with SORT. For example: =TRI(UNIQUE(A2:A50)).
Can multiple columns be extracted?
UNIQUE handles two-dimensional ranges well: =UNIQUE(B2:D100) will display each unique row according to the entirety of B:D.
7. Additional resources
- To deepen cell management, see the article on Merging and splitting cells correctly in Excel.
- To explore interactive PivotTables, this article on slicers is very useful.
- If you work with multiple conditions, take a look at the SUMIF and SUMIFS functions.
- Finally, to sort and extract text before moving to UNIQUE, discover the Excel text functions.
8. Conclusion
The UNIQUE function is a real time saver as soon as you need to clean up a list of repeated values. Easy to implement, it combines very well with SORT, FILTER, SUMIFS or even macros depending on your needs. By leveraging structured ranges and IFERROR, you create robust, dynamic reports ready for decision making.
Do not hesitate to test this formula on your real tables and share your feedback or questions in the comments!