How to Locate Duplicates in Excel: A Complete Guide

Duplicate entries can find their way into your Excel workbooks for many reasons—perhaps through manual data entry errors, merging multiple spreadsheets, or overlooking duplicates when consolidating information. Regardless of how they appear, these repeated records can distort your analyses, overstate totals, and lead to confusion in reports. In this guide, you’ll learn how to locate duplicates in Excel to ensure your data remains accurate and reliable.

Before we jump in, know that you can try Enquery for 30 days free. Enquery makes working with data easier by combining SQL and AI to streamline data analysis and reporting. With Enquery, you can automate complex workflows, gain insights faster, and manage data more efficiently.

How to Find Duplicate Rows and Values in Excel

• Duplicate Rows: When every value in one row is identical to another, Excel considers it a duplicate.

• Duplicate Values: These may occur in a specific column, such as repeated customer IDs or product codes.

You can identify duplicates in the following ways.

Check Duplicates with Conditional Formatting

This method allows you to mark duplicates in Excel visually, making it easier to decide which rows or values to remove.

1. Highlight the dataset you want to check.

2. Navigate to the Home tab and select Conditional Formatting > Highlight Cell Rules > Duplicate Values.

Excel will automatically highlight every duplicate in your chosen range, often with a red fill by default. You can personalize the formatting using bold text, different colors, or other styles to make the duplicates stand out for clean-up simplicity.

Check duplicates in Excel with Formulas

This involves using formulas (usually directly) without necessarily adding a helper column to highlight or find duplicates. 

You can use a formula like the following in a specific cell to check how many times a value appears.

=COUNTIF(range, criteria)

With this method, there’s no need for an extra column; duplicates are highlighted directly in the data. It’s also useful for quick identification when you don't need to categorize or filter duplicates.

Tip: Use when you need to visually inspect duplicates or handle them on the spot without additional data manipulation.

Marking Duplicates with a Helper Column

For larger datasets, adding a helper column can provide a clearer view of duplicates. Here’s how to get it done.

1. Add a blank column next to your data.

2. Use a formula to flag duplicates, such as the following.

=IF(COUNTIF(<DataRange>

Ensure you replace DataRange with the range of your dataset. This formula will label duplicate rows as “Duplicate” and unique rows as “Unique.”

Tip: Once you’ve found duplicates in your dataset, review them before deleting anything to ensure critical information isn’t accidentally removed. 

3. Sort or filter your dataset by the helper column to quickly group duplicates together for review.

To sort the dataset by the Helper Column, you can do the following.

• Select the Entire Dataset:
  Click and drag to highlight all your data, including the helper column.

• Open the Sort Menu:
  Go to the Excel ribbon and click on the Data tab. Click Sort in the "Sort & Filter" group.

• Choose the Helper Column to Sort By:
  In the "Sort by" dropdown menu, select the column with the helper formula (e.g., "Helper Column" or "Column B"). Then, choose A to Z (or Z to A) to group "Duplicate" and "Unique" entries together.

• Review Duplicates:
  Once sorted, rows labeled as "Duplicate" will appear together, making it easy to review them.

How to Locate Duplicates in Excel with Remove Duplicates Feature

Excel simplifies the process of removing duplicates with its built-in Remove Duplicates feature. This tool is quick and efficient for cleaning up datasets without requiring advanced knowledge of formulas or macros. It also automatically keeps one occurrence of each duplicate for reference. 

Here’s what to do.

1. Select your data by highlighting the range of cells or rows where you want to remove duplicates.

2. Access the Remove Duplicates tool.

• Navigate to the Data tab.

• Click on the Remove Duplicates button in the Data Tools group.

3. Choose columns for deduplication.

• In the dialog box that appears, select the columns you want to check for duplicates.

• For instance, if you want to find duplicates based only on Customer Name and Order Date, ensure those columns are selected.

4. Remove duplicates.

• Click OK.

• Excel will remove the duplicates and display a summary of how many duplicate rows were deleted and how many unique rows remain.

If you frequently work with duplicates, learning the Excel shortcut to remove duplicates can save time. Press Alt > A > M (Windows) to quickly open the Remove Duplicates tool. For Mac users, use Command + Shift + K to open the Remove Duplicates tool in Excel.

How to Remove Duplicates After Using a Helper Column or Formula

If you’ve used a helper column or a formula to pinpoint duplicates, you have multiple options to clean up your data. Choose the method that best fits your workflow.

Filter and Delete Rows

1. Filter for "duplicate" rows.

• Highlight your entire dataset (including the helper column).

• Go to the Data tab and click Filter.

• Click the dropdown arrow in the helper column and select only "Duplicate."

2. Delete the filtered rows.

• Select the visible duplicate rows (filtered by the helper column).

• Right-click and choose Delete Row, or use the Delete button on the Home tab.

3. Remove the filter.

• Clear the filter to restore the view of your dataset.

Copy "Unique" Rows to a New Sheet

1. Filter for duplicates.

• Follow the same steps above to filter for duplicate rows, but filter for "Unique" instead of "Duplicate." 

2. Copy the unique data.

• Select all visible rows and press Ctrl + C to copy them.

• Go to a new worksheet or a new location in your current worksheet and press Ctrl + V to paste.

This method keeps the original data, which is safer if you're unsure whether duplicates should be deleted permanently.

Sort and Manually Review

1. Sort the dataset by the helper column.

• Use the Sort feature to group "Duplicate" rows together, as explained earlier.

2. Manually delete duplicate rows.

• Scroll through the sorted dataset.

• Select and delete the rows labeled "Duplicate," ensuring you review critical information first.

Tip: Always create a backup before removing duplicates, especially if your dataset is large or critical.

Advanced Techniques: How to Locate Duplicates in Excel

When dealing with complex scenarios—such as multiple conditions, multiple worksheets, or large datasets—Excel’s standard tools may not be enough. The advanced methods below will help you handle these situations more effectively.

Comparing Two Excel Spreadsheets for Duplicates

If your data is spread across multiple worksheets or workbooks, try these approaches to identify and remove duplicates.

1. Use conditional formatting.

• Open both worksheets or files.

• Highlight the range in the first worksheet and apply Conditional Formatting.

• Use a formula like this to compare with the second worksheet:

=COUNTIF(Sheet2!$A$1:$A$100,A1)>0

This highlights any values in “Sheet1” that are also present in “Sheet2”.

2. Combine data into a single sheet.

• Copy data from both sheets into one worksheet, adding a column to indicate the source (e.g., “Sheet1” or “Sheet2”).

• Use the Remove Duplicates tool on the combined dataset, ensuring you include all relevant columns for comparison.

Automating deduplication with VBA

Consider automating the deduplication process using VBA (Visual Basic for Applications). This allows you to delete duplicates in Excel with macros. Here’s how to set up a simple one

1. Open the VBA editor.

• Press Alt + F11.

2. Insert a new module.

• In the editor, click Insert > Module.

3. Enter the code.

• Copy and paste the following VBA code:

Sub RemoveDuplicates()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.UsedRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

Replace Array(1, 2) with the column numbers you want to evaluate for duplicates (e.g., 1 for Column A, 2 for Column B).

4. Run the macro.

• Close the VBA editor and press Alt + F8.

• Select the macro RemoveDuplicates and click Run.

The macro will remove duplicates in the active worksheet based on the specified columns.

Note that for Mac, some advanced features, like VBA macros, may require enabling developer tools, which are disabled by default.

Best Practices for Preventing Duplicates in Excel

While removing duplicates is helpful, preventing them in the first place will save you time and effort.  Consider the following best practices.

Use Data Validation to Prevent Duplicates

Excel’s Data Validation feature helps ensure unique entries as data is being entered. Here’s how you can set it up.

• Select the range where you want to prevent duplicates (e.g., a column of IDs).

• Go to the Data tab and choose Data Validation.

• In the settings, select Custom in the Allow field. Then, enter the formula: 

=COUNTIF(A:A,A1)=1

• Replace A:A with your selected column.

This code will trigger a warning if a duplicate is entered.

Standardize Data Entry Practices

Consistency in data entry reduces the chance of duplicates. Establish clear data entry guidelines for your team, such as using dropdown lists or predefined formats for columns (dates, categories, names, etc.). This ensures uniformity and reduces the risk of duplicate entries.

Consolidate Data Regularly

If you rely on multiple data sources, make a habit of consolidating them into a single master sheet. Tools like Power Query can assist by identifying and merging duplicate entries during the consolidation process.

Use Conditional Formatting for Real-Time Checks

Conditional formatting is a great way to highlight duplicates in frequently updated datasets. It provides an immediate visual cue, helping you spot and address duplicate entries as they occur.

Avoid Copy-Pasting Without Verification

Copying data from external sources without checking for inconsistencies can introduce duplicates. Ensure proper formatting and validation before adding any new data to your spreadsheet. Hidden characters or mismatched formats often lead to errors.

Switch to Scalable Tools for Large Datasets

When working with datasets that exceed Excel’s capacity, transitioning to scalable tools like SQL databases (which we’ll cover next) can be a game-changer. SQL offers advanced deduplication capabilities for managing large volumes of data. 

How to Locate Duplicates in Excel with SQL

Now, let's talk about SQL queries. If you aren’t familiar with SQL or “sequel,” it is the language of databases. It’s powerful and efficient, and when it comes to deduplication, it's like having a Swiss Army knife in your pocket.

Here’s a basic example to see deduplicated data in SQL:

SELECT DISTINCT * FROM YOUR TABLE

That query retrieves all the unique rows from the table named YOUR_TABLE, effectively deduplicating the data in the query result.

Or, if you need more control, like removing duplicates based on specific columns:

WITH CTE AS (
  SELECT *,
  ROW_NUMBER() OVER (PARTITION BY column1, column2) AS row_num
  FROM your_table
)

Just two lines of code (or a few more for complex needs), and your data is deduplicated. SQL handles large datasets with grace and speed, making it a no-brainer for data professionals.

Making SQL Queries Even Simpler with AI

This is where the real magic happens. Imagine having an AI tool like Enquery that simplifies sequel queries even further. That's right—no more memorizing complex syntax or googling how to write that perfect query. This software harnesses AI to transform your natural language instructions into sequel coding. Think of it as having a personal SQL assistant right at your fingertips.

Here’s why ai-powered SQL queries are a game-changer

• User-Friendly: You don’t need to be an SQL wizard. Just type what you need in your language, and let the Enquery do the heavy lifting.

• Time-saving: No more hunting for the right functions or worrying about syntax errors.

• Error Reduction: Enquery helps prevent common mistakes, ensuring your data stays intact and accurate.

Consider the following real-world example of Enquery in action.

Let’s say you have a customer database and need to remove duplicates based on email addresses. With Enquery, simply describe what you're trying to do, and the AI will help you craft the query.

Give Enquery a try for 30 days FREE. Download Now.