Delete 1000 duplicate data in Excel in 3 seconds

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

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:

1. array (required)
   The range of data you want to filter (example: A2:A100 or Table1[Column]).
2. [by_col] (optional)
   TRUE: Compare data per column (for horizontal data).
   FALSE (default): Compare data per row (vertical).
3. [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:

Steps to Use UNIQUE:

1. Select the blank cell where the results will be displayed, for example, C2.
2. Type the following formula:

=UNIQUE(A2:A15)

3. Press Enter.
4. Excel will display a list of unique names automatically, without duplicates, and the results will be dynamic as the source data changes.

Output:

Example 2: Sales Product List

Sometimes duplicates occur on combinations of multiple columns, such as Product Name, Price, and Category.

Initial data:

Steps to Use UNIQUE:

1. Select a blank cell to display the results, for example, E2.
2. Enter the formula:

=UNIQUE(A2:C6)

3. Press Enter.
4. Excel will generate a list of unique product combinations, removing duplicate rows that have identical information in all columns.

Output:

Using UNIQUE with Additional Criteria

UNIQUE function can become more powerful when combined with other functions such as SORT and FILTER.

Sorting Unique Data with SORT

To get a unique list of names arranged alphabetically, use:

=SORT(UNIQUE(A2:A20))

This formula will generate a unique list of A2:A20, and then sort them from A–Z.

Filtering Unique Data with FILTER

If you only want to display unique data with certain criteria, combine it with FILTER functions. Example: showing a unique customer whose payment status is “Lunas”.

=UNIQUE(FILTER(A2:A20, B2:B20=“Lunas”))

• A2:A20 = customer name field
• B2:B20 = payment status column

Advantages of UNIQUE Functions Over Remove Duplicates

Although Remove Duplicates has long been a built-in feature of Excel for removing duplicate data, the presence of the UNIQUE function provides a significant advantage, especially for dynamic and continuous data processing needs. Here are some of the key advantages of UNIQUE:

1 Dynamic and Real-Time

The UNIQUE function will automatically update the results when the source data changes. Example: If you add a new entry or delete data in a source column, the UNIQUE result list will adjust immediately without having to redo the deduplication process.

2 Not Changing Original Data (Non-Destructive)

Unlike Remove Duplicates, which deletes data directly from the source (destructive), UNIQUE works by displaying results in separate locations without touching the original data. This is especially important if you need to maintain a raw database for auditing or archiving purposes.

3 Efficient for Automated Reports

In the creation  of a dashboard or periodic reports, UNIQUE is the best choice because:

• Saves time (no need for repeated manual cleaning).
• Ensure report data is always up-to-date.
• It is easy to combine with other functions such as SORT, FILTER, or COUNTIF for advanced analysis.

4 Flexibility on Column Combinations

UNIQUE can remove duplicates based on the combination of multiple columns at once, something that requires an additional step if using Remove Duplicates manually.

Conclusion:

If you need a one-time data cleanup, Remove Duplicates may be enough. However, for the needs of automation, long-term accuracy, and dynamic reporting, UNIQUE is a far superior choice.