Duplicate data can be a real headache, whether you're managing customer lists, analyzing sales, or cleaning up survey results. This tasks is very frequently tested as part of Excel Assessment Test.

Fortunately, Microsoft Excel offers multiple ways to remove or highlight duplicates, each suited for different needs. In this detailed guide, we’ll walk you through seven effective methods to identify and remove duplicate entries from your Excel spreadsheets.

✅ 1. Remove Duplicates Using the Built-In Tool

This is the simplest and fastest method:

• Select your data range.
• Go to the Data tab.
• Click Remove Duplicates.
• Choose the columns to check for duplicates.
• Click OK to remove duplicate rows.

Great for quick cleanup, but be cautious—it permanently deletes data.

✅ 2. Highlight Duplicates with Conditional Formatting

Want to visually spot duplicates before removing them?

• Select your range.
• Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
• Choose a highlight style (e.g., red fill).

This doesn’t remove data but helps you easily locate duplicates.

✅ 3. Use the Advanced Filter

Need to extract only the unique values?

• Select your data.
• Go to Data > Sort & Filter > Advanced.
• Choose to copy to another location and check Unique records only.

This method gives you a duplicate-free list without touching the original data.

✅ 4. Identify Duplicates with COUNTIF Formulas

For more customized control:

• Use =COUNTIF(A:A, A2)>1 to check if a value appears more than once.
• You can also create an IF formula to label values as "Duplicate" or "Unique."

Ideal for partial matches or multi-column checks.

✅ 5. Summarize Unique Data with Pivot Tables

Pivot Tables automatically group unique items:

• Insert a Pivot Table from your data range.
• Drag your column of interest into the Rows area.

You’ll instantly get a summary with only unique values.

✅ 6. Clean Data with Power Query

For large or recurring datasets:

• Select your data and go to Data > Get & Transform > From Table/Range.
• In Power Query Editor, select columns and go to Remove Rows > Remove Duplicates.
• Click Close & Load to apply changes.

Best for repeatable workflows and advanced data tasks.

✅ 7. Use the UNIQUE Function (Excel 365/2021 Only)

Want a dynamic list that updates as data changes?

• Use =UNIQUE(A2:A100) to pull unique values from a list.
• For full-row checks, use =UNIQUE(A2:C100).

This method is non-destructive and always up to date.

💾 Download the Sample Excel File

Want to practice these methods yourself? Click here to download the free Excel sample file that contains sample business data with built-in duplicates, perfect for testing all seven methods.

By mastering these tools, you'll save time, reduce errors, and keep your Excel spreadsheets clean and efficient. Whether you're an Excel beginner or looking to sharpen your skills, these techniques will make a big difference.