Tired of spending hours cleaning duplicate data in Excel? You’re not alone! Double data not only messes up analysis and makes reports inaccurate, but it also becomes a major hindrance to productivity.
Excel has a powerful solution that is often forgotten: the UNIQUE function. With this magic formula, you can get rid of duplicate data instantly without the need for laborious manual work!
From 1000 rows of messy data, you can filter out unique entries in the blink of an eye. No longer relying on the outdated Remove Duplicates feature, the UNIQUE formula offers a dynamic advantage: results will be automatically updated when the source data changes.
Getting to Know UNIQUE Functions in Excel
The UNIQUE function is a modern Excel formula that automatically filters and extracts unique data from a range or table. Unlike the manual method, this formula is dynamic, meaning that the results will be automatically updated whenever the source data changes.
Simple example:
=UNIQUE(A2:A20)The above formula will generate a list of unique values from cells A2 to A20.
Versions of Excel That Support UNIQUE Functions
Not all versions of Excel have this function. UNIQUE is only available on:
- Microsoft Excel 365 (all editions)
- Microsoft Excel 2021
- Excel versi web (Excel for the Web)
- Excel Mobile (Android & iOS)
Differences between UNIQUE Functions and the Remove Duplicates Feature
| Aspects | UNIQUE Functions | Remove Duplicates |
| Kind | Formula (Formula-Based) | Built-in features (Command-Based) |
| Data Dynamics | Results change automatically if the data source changes | Static results must be rerun if data changes |
| Original Data | Not changing the original (non-destructive) data | Delete duplicates directly from the original (destructive) data |
| Ease of Automation | Suitable for automated reports and dashboards | More suitable for occasional manual cleaning |
| Availability | Only in Excel 365, 2021, and later versions | Available in all versions of Excel |
In short, UNIQUE is an ideal choice if you want an automated process that keeps unique data up to date in real-time. Meanwhile, Remove Duplicates is more suitable for manual and permanent deletion of duplicate data.
Syntax of UNIQUE Functions in Excel
Basic Structure of the UNIQUE Formula
=UNIQUE(array, [by_col], [exactly_once])Parameter Explanation:
- array (required)
The range of data you want to filter (example: A2:A100 or Table1[Column]). - [by_col] (optional)
TRUE: Compare data per column (for horizontal data).
FALSE (default): Compare data per row (vertical). - [exactly_once] (optional)
TRUE: Only show truly unique values (only 1 time).
FALSE (default): Display all unique values, including those that appear repeatedly.
Usage Examples
1. Simple Example (Vertical Unique Data Filter)
=UNIQUE(A2:A20)Result: Displays all unique values of the range A2:A20.
2. Horizontal Unique Data Filter
=UNIQUE(A1:Z1, TRUE)Result: Extracted a unique value from the first row (A1-Z1).
3. Filter Values That Only Appear Once
=UNIQUE(A2:A100, FALSE, TRUE)Result: Shows only data that appears once (no duplicates at all).
4. Combine with Other Functions:
- UNIQUE + SORT for sorted data:
=SORT(UNIQUE(A2:A100))- UNIQUE + FILTER for conditional data:
=UNIQUE(FILTER(A2:B100, B2:B100>500))Example of Deleting Duplicate Data
Example 1: Employee Name List
Suppose you have a list of employee names in column A2:A15, and several names in the list appear more than once.
Initial data:
| Employee Name |
| Andi Pratama |
| Siti Aminah |
| São Paulo |
| Andi Pratama |
| Dwi Lestari |
| São Paulo |
| Rina Wijaya |
| Siti Aminah |
Steps to Use UNIQUE:
- Select the blank cell where the results will be displayed, for example, C2.
- Type the following formula:
=UNIQUE(A2:A15)- Press Enter.
- Excel will display a list of unique names automatically, without duplicates, and the results will be dynamic as the source data changes.
Output:
| Unique Name |
| Andi Pratama |
| Siti Aminah |
| São Paulo |
| Dwi Lestari |
| Rina Wijaya |
