How to Remove Duplicates in Excel Like a Pro?

Duplicates in Excel can cause confusion, errors, and inefficiencies—especially when dealing with large datasets. Whether you’re managing customer lists, financial records, or sales data, removing duplicates is a crucial skill to ensure accuracy and organization.

In this guide, we’ll cover multiple ways to remove duplicates in Excel like a pro, using built-in tools, advanced formulas, and automation techniques.

1. Use the Built-in “Remove Duplicates” Tool (Fastest Method)

Excel provides a simple one-click tool to remove duplicate values quickly.

Steps to Remove Duplicates Using Excel’s Built-in Tool:

1. Select the data range that contains duplicates.
2. Go to the Data tab and click Remove Duplicates.
3. In the pop-up window, check the columns where you want to remove duplicates.
4. Click OK, and Excel will remove duplicate values.

Best for:

✅ Quick and easy duplicate removal
✅ Datasets with no formulas or structured references
✅ Cleaning up lists like emails, product IDs, or names

2. Use Conditional Formatting to Highlight Duplicates

Before deleting duplicates, you may want to highlight them first to review the data.

Steps to Highlight Duplicates:

1. Select the column or range you want to check.
2. Go to the Home tab → Conditional Formatting → Highlight Cell Rules → Duplicate Values.
3. Choose a formatting style (e.g., red fill, bold text) and click OK.

Now, duplicates will be visually highlighted, allowing you to review and remove them manually if needed.

Best for:

✅ Identifying duplicates before deleting them
✅ Keeping control over which duplicates to remove
✅ Visualizing duplicate values in reports

3. Use Excel Formulas to Find and Remove Duplicates

A. Using the COUNTIF Formula to Identify Duplicates

The COUNTIF function helps find duplicates in a column.

📌 Formula:

=COUNTIF(A:A, A2) > 1

• This formula checks if a value in Column A appears more than once.
• If the result is TRUE, it means the value is duplicated.

B. Using UNIQUE to Extract Distinct Values (Excel 365 & Excel 2019)

If you have Excel 365 or Excel 2019, the UNIQUE function can extract distinct values without altering your dataset.

📌 Formula:

=UNIQUE(A2:A100)

• This formula lists unique values from Column A, removing duplicates automatically.

Best for:

✅ When you need more control over duplicate identification
✅ Keeping original data intact while filtering unique values
✅ Working with Excel formulas instead of deleting data

4. Remove Duplicates with Power Query (For Large Datasets)

If you’re dealing with huge datasets, Power Query is the best tool to remove duplicates without modifying the original data.

Steps to Remove Duplicates Using Power Query:

1. Select your dataset and go to Data → Get & Transform → From Table/Range.
2. In the Power Query Editor, select the column(s) where you want to remove duplicates.
3. Click Remove Duplicates in the Home tab.
4. Click Close & Load to save your cleaned data into Excel.

Best for:

✅ Handling large datasets efficiently
✅ Keeping original data intact while working on a transformed copy
✅ Automating duplicate removal with refreshable queries

5. Automate Duplicate Removal with VBA (For Advanced Users)

If you frequently deal with duplicate data, you can use VBA (Visual Basic for Applications) to automate the process.

Simple VBA Code to Remove Duplicates:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert → Module and paste this code:

Sub RemoveDuplicates()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Range("A1:A100").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

3. Run the macro, and Excel will automatically remove duplicates from Column A.

Best for:

✅ Automating duplicate removal for repeated tasks
✅ Large datasets requiring frequent cleanup
✅ Users comfortable with Excel macros and VBA

Get the cheapest Office keys with instant delivery and genuine activation—unlock Microsoft Office at unbeatable prices today!