Remove Duplicate in excel

Meenu singh - November 22, 2025

 

Here’s the quickest and simplest way to remove duplicates in Excel 👇

✅ Method 1: Remove Duplicates (Built-in Feature)

  1. Select the data range (or click anywhere inside your data).
  2. Go to Data tab → click Remove Duplicates.
  3. Choose the columns to check for duplicates.
    • Select all columns → removes rows that are entirely duplicated.
    • Select specific columns → removes duplicates based on selected columns only.
  4. Click OK → Excel removes duplicates and shows how many were removed.

✅ Method 2: Use “Unique” Formula (Excel 365 / 2021)

If you want to keep both original data and a clean version:

=UNIQUE(A1:A100)

For entire rows:

=UNIQUE(A1:D100)

This displays only unique values.

✅ Method 3: Remove Duplicate Using “Advanced Filter”

  1. Select your data.
  2. Go to Data → Advanced (under Sort & Filter).
  3. Select Copy to another location.
  4. Check Unique records only.
  5. Choose where to copy → Click OK.

Sure! Here are more detailed, beginner-friendly ways to remove duplicates in Excel—with extra tips, tricks, shortcuts, and common mistakes to avoid.

🔥 1. Remove Duplicates (Most Common Method – Detailed)

Steps:

  1. Select your full dataset
    Example: A1:D200
  2. Go to Data tab
  3. Click Remove Duplicates
  4. A box appears → select which columns to check
  5. Click OK

📌 Important Notes

  • Excel removes the entire row, not just the duplicate cell.
  • If duplicates exist only in one column, select that single column.

🔥 2. UNIQUE Formula (Dynamic & Best for Clean Lists)

Works in Excel 365 / Excel 2021.

Example: Remove duplicate names

=UNIQUE(A2:A100)

Remove duplicate rows

=UNIQUE(A2:D100)

Extra options:

  • Sort + Unique:
=SORT(UNIQUE(A2:A100))




🔥 3. Advanced Filter (Keeps original data safe)

Good if you don’t want to delete anything.

Steps:

  1. Select your data → Data tab → Advanced
  2. Choose Copy to another location
  3. Check Unique records only
  4. Choose a cell to copy clean data
  5. Click OK

🔥 4. Using Pivot Table to Remove Duplicates

Pivot tables automatically show unique items.

Steps:

  1. Select your data
  2. Insert → PivotTable
  3. Put the repeated column in Rows
  4. The list will show only unique values.

🔥 5. Remove Duplicate with Power Query (Best for large data)

Useful for thousands of rows.

Steps:

  1. Select data → Data tab
  2. Select From Table/Range
  3. Power Query window opens
  4. Select column → Right-click → Remove Duplicates
  5. Click Close & Load

🔧 6. Highlight Duplicates Instead of Removing (For checking)

  1. Select data
  2. Home → Conditional Formatting
  3. Highlight Cells Rules → Duplicate Values
  4. Choose any color
  5. Now check duplicates before deleting.

💡 7. Shortcut Keys

  • Alt + A + M → Remove Duplicates window
  • Ctrl + A → Select all
  • Ctrl + Shift + ↓ → Select entire column
  • Ctrl + T → Convert to Table (recommended)

❗ Common Mistakes to Avoid

✔ Not selecting the whole table
✔ Removing duplicates in only one column by mistake
✔ Having extra spaces (use TRIM)
✔ Using mixed text formats like “ABC” vs “abc” (use LOWER)

Sure! Here is a more detailed explanation with additional tips, tricks, and advanced methods for removing duplicates in Excel.

📌 1. Remove Duplicates (Standard Method – Permanent Delete)

Steps

  1. Select the entire data range (e.g., A1:C100).
  2. Go to Data tab.
  3. Click Remove Duplicates.
  4. A dialog box appears:
    • Tick the columns you want Excel to check.
    • Example:
      • Tick only "Email" → Removes rows with duplicate emails.
      • Tick "Name + Phone" → Removes rows only if both match.
  5. Click OK → Excel removes duplicate rows and shows how many were removed.

Important Notes

  • This action is permanent.
  • Excel keeps the first occurrence and deletes the rest.

📌 2. Using UNIQUE Formula (Safe – Does NOT delete anything)

Useful when you want a clean list without changing original data.

Examples

➤ Unique values from a single column

=UNIQUE(A2:A100)

➤ Unique rows from multiple columns

=UNIQUE(A2:C100)

➤ Unique values sorted

=SORT(UNIQUE(A2:A100))

📌 3. Conditional Formatting (Highlight Before Deleting)

Great for reviewing duplicates manually.

Steps

  1. Select your column or table.
  2. Go to Home → Conditional Formatting.
  3. Click Highlight Cell Rules → Duplicate Values.
  4. Excel highlights duplicates.
  5. Now you can delete them manually or sort them together using:
    • Data → Sort by color

📌 4. Using Advanced Filter (Extract Unique Records)

This creates a unique filtered list in a new place.

Steps

  1. Select the data.
  2. Go to Data → Advanced (under sort & filter).
  3. Choose:
    • Copy to another location
  4. Check Unique records only
  5. Choose where to copy them.
  6. Click OK.

This is useful when you want:

  • A unique list of names
  • Unique records from multiple columns

📌 5. Remove Duplicates in Excel Table (Smart Option)

If your data is in a Table (Ctrl + T):

Steps

  1. Click inside the table.
  2. Go to Table Design → Remove Duplicates.
  3. Select columns.
  4. Click OK.

Tables automatically expand — easier for large datasets.

📌 6. Power Query (Best for Large Data)

For huge files or automation.

Steps

  1. Select data → Data → From Table/Range
  2. Power Query opens.
  3. Select columns → Right click → Remove Duplicates
  4. Click Close & Load.

This is refreshable, meaning:

  • If new data comes in, duplicates get removed automatically.




Post a Comment

0 Comments