Finding duplicate entries in your Excel spreadsheets is essential for maintaining accurate and reliable data. Whether you're managing customer lists, financial records, or inventory, duplicates can lead to errors and confusion. This guide provides clear, step-by-step instructions for three different methods to find duplicates in Excel. We'll cover everything from simple highlighting for a quick visual check to more powerful techniques for automatically removing unwanted entries, ensuring your data is clean and trustworthy.

Fast Answer

  • Fastest Method: Conditional Formatting
  • Menu Path: Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values
5-15 Minutes Time needed
Beginner Difficulty
Accidental Deletion Watch out for

Before You Start

Before you begin cleaning your data, it's important to prepare your file and understand what you're working with. A little preparation can prevent major headaches later on.

What You Need

  • Microsoft Excel: These instructions apply to Excel 2013, 2016, 2019, 2021, and Microsoft 365. The interface may look slightly different in older versions, but the tools are generally in the same place.
  • Your Spreadsheet: Have the Excel file with the data you want to check ready to go.

Safety and Context Checks

  • Create a Backup: This is the most critical step. Before making any changes, especially deletions, save a copy of your workbook. Go to File > Save As and give the file a new name, like "MyData_Backup.xlsx". If you make a mistake, you can always go back to the original version.
  • Define "Duplicate": Decide what counts as a duplicate in your data. Is it a row where every single cell is identical to another row? Or is it a row that has the same email address or order number as another, even if other details like the date are different? This will determine which columns you select in the following steps.
Check first: Always work on a copy of your spreadsheet. The 'Remove Duplicates' tool permanently deletes data, and there is no simple way to undo this once the file is saved and closed.

Step-by-Step Instructions

We'll cover three effective methods to find and manage duplicates. The first method is great for visually identifying them, the second is for quick removal, and the third offers more control using a formula.

Method 1: Highlight Duplicates with Conditional Formatting

This is the safest and most common method. It doesn't change or delete any data; it simply applies a colour to cells that contain duplicate values, making them easy to spot. This is perfect for reviewing your data before deciding what to do.

Step 1: Select Your Data

First, you need to tell Excel where to look for duplicates. Click and drag your mouse to highlight the column or columns you want to check. For example, to find duplicate email addresses in column C, click the column header "C" to select the entire column. To check for duplicate full names across columns A and B, click the header for column A and drag across to column B.

Step 2: Open the Conditional Formatting Menu

With your data selected, go to the Home tab on the Excel ribbon at the top of the screen. In the 'Styles' group, click on the Conditional Formatting button. A dropdown menu will appear with several options.

Step 3: Apply the Duplicate Values Rule

From the dropdown menu, move your mouse over Highlight Cell Rules, and then click on Duplicate Values from the side menu that appears. This will open a small dialogue box.

In this box, the first dropdown should already say "Duplicate". The second dropdown lets you choose how the duplicates will be highlighted. The default is "Light Red Fill with Dark Red Text", but you can choose other colours or create a custom format. Click OK.

Tip: In the same dialogue box, you can change "Duplicate" to "Unique" to highlight the values that appear only once. This can be useful for finding entries that don't have a matching record.

Step 4: Review Your Highlighted Data

Excel will instantly highlight all the cells in your selected range that contain duplicate values. You can now scroll through your data and easily see where the duplicates are. From here, you can decide whether to delete them manually, edit them, or leave them as they are. To remove the highlighting, go back to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Method 2: Find and Remove Duplicates Automatically

If your goal is to permanently delete duplicate rows, Excel has a built-in tool that does this quickly. Remember to work on a backup copy of your file before using this feature.

Step 5: Select Your Data Range

Click on any single cell inside your data table. You don't need to highlight the whole table, as Excel is smart enough to detect the entire data range automatically. However, if your data has blank rows or columns in the middle, it's safer to select the entire range manually.

Step 6: Launch the Remove Duplicates Tool

Go to the Data tab on the Excel ribbon. In the 'Data Tools' group, look for the icon with several blue and white rows and a red 'x' on it. Click this button, which is labelled Remove Duplicates.

Step 7: Configure and Execute

A dialogue box will appear, listing all the column headers from your data. This is where you tell Excel what defines a duplicate.

  • If a duplicate row is one where every single column is identical, make sure all the column boxes are ticked.
  • If a duplicate is defined by just one or two columns (e.g., any row with the same Email Address), click 'Unselect All' and then tick only the box for the 'Email Address' column.

Ensure the 'My data has headers' box is ticked if your first row is a header. This prevents Excel from treating your titles as data. Click OK.

Excel will delete the duplicate rows, keeping the first instance of each value. A message will pop up telling you how many duplicate values were found and removed, and how many unique values remain. Click OK to finish.

Method 3: Identify Duplicates with a Formula

For more complex situations, using a formula gives you the most flexibility. This method adds a new column to your sheet that labels each row as a duplicate or unique, allowing you to filter, sort, or review them without immediately deleting anything.

Step 8: Add a "Helper" Column

Go to the first empty column to the right of your data. In the header row, give it a title like "Duplicate Check". This will be where our formula's results appear.

Step 9: Enter the COUNTIF Formula

In the first cell below your new header (e.g., cell F2 if your data is in A-E and your header is in F1), type the following formula: =IF(COUNTIF(A:A, A2)>1, "Duplicate", "Unique").

Let's break this down:

  • COUNTIF(A:A, A2): This part counts how many times the value in cell A2 appears in the entire A column.
  • >1: This checks if the count is greater than one. If it is, that means it's a duplicate.
  • IF(..., "Duplicate", "Unique"): This is an IF function. If the condition (count > 1) is true, it will write "Duplicate" in the cell. If it's false, it will write "Unique".

You can adjust the formula. To check for duplicates in the email column (e.g., column C), you would change the formula to =IF(COUNTIF(C:C, C2)>1, "Duplicate", "Unique"). After typing the formula, press Enter.

Step 10: Copy the Formula Down and Filter

Click on the cell where you just entered the formula. You will see a small green square at the bottom-right corner of the cell. Double-click this square. Excel will automatically copy the formula all the way down to the last row of your data.

Now, click on your "Duplicate Check" header. Go to the Data tab and click the Filter button (it looks like a funnel). A small dropdown arrow will appear on your header. Click the arrow, uncheck 'Select All', and then tick the box for "Duplicate". Click OK. Your sheet will now be filtered to show only the rows that were identified as duplicates, which you can then review or delete.

Step-by-Step Instructions

Test the result

Verify the outcome against the original goal and fix any warning signs before treating the task as complete.

Tip: Keep the step simple and verify the result before continuing.

Document what changed

Save the final settings, decisions, or checks so the same process is easier to repeat later.

Tip: Keep the step simple and verify the result before continuing.

Quick Reference

Situation Use This Method Why It's Best
I just want to see where the duplicates are without changing anything. Conditional Formatting It's fast, visual, and non-destructive. You can easily see the duplicates in context with the rest of your data.
I need to permanently delete all duplicate rows and clean my list quickly. Remove Duplicates Tool This is the fastest way to get rid of duplicate records. It's a powerful, one-click solution built directly into Excel.
I need to find and review duplicates before deciding what to do. COUNTIF Formula This method gives you a flexible helper column. You can sort by duplicates, filter them, or perform other actions without deleting anything upfront.
I need to find duplicates based on a combination of columns (e.g., same First Name AND Last Name). Remove Duplicates Tool or COUNTIFS Formula The Remove Duplicates tool lets you tick multiple columns. An advanced COUNTIFS formula can do the same for identification.

Common Problems When Finding Duplicates

Sometimes things don't go as planned. Here are a few common issues and how to fix them.

Problem: Excel Isn't Finding Obvious Duplicates

This is often caused by small, invisible differences in the cells. The most common culprits are leading or trailing spaces. A cell with "John Smith " (with a space at the end) is different from "John Smith".

Solution: Use the TRIM function to clean your data. Insert a new column next to the one you want to clean. If your data is in column A, in the new column type =TRIM(A2) and copy it down. This creates a new, clean version of your data without extra spaces. You can then copy this new column and use Paste Special > Values to replace the original messy data.

Problem: The 'Remove Duplicates' Tool Deleted the Wrong Data

This can happen if you don't configure the column selection correctly. For example, if you're trying to remove duplicate email addresses but leave the 'First Name' column unticked, the tool might keep two people with different names but the same email, deleting the wrong one.

Solution: This is why working on a backup is so important. Close the file without saving and open your backup. When you run the tool again, think carefully about which column(s) uniquely identify a record. If the email address is the unique identifier, make sure only the email column is selected in the 'Remove Duplicates' dialogue box.

Problem: The Tool Is Not Case-Sensitive

By default, Excel's duplicate-finding tools are not case-sensitive. They treat "example@mail.com" and "Example@mail.com" as the same. In most cases, this is what you want, but sometimes you may need a case-sensitive check.

Solution: For a case-sensitive check, you'll need a more advanced formula. You can use a combination of `SUMPRODUCT` and `EXACT` for this, which is a more advanced technique. For most users, however, the standard non-case-sensitive check is sufficient.

Advanced Tips for Managing Duplicates

Once you've mastered the basics, these techniques can give you even more power and control over your data.

Using Power Query to Find Duplicates

For very large datasets (tens of thousands of rows or more), Excel can become slow. Power Query (also known as Get & Transform Data, found in the Data tab) is a much more powerful and efficient tool for this job.

Inside the Power Query Editor, you can select one or more columns, right-click, and choose Remove Duplicates. Alternatively, on the Home ribbon of the editor, you can use the Keep Rows > Keep Duplicates command to isolate only the duplicate entries. Power Query is non-destructive to your original source data and can handle millions of rows with ease.

Finding Duplicates Between Two Different Lists or Sheets

What if you have two separate lists and want to find which names on List A also appear on List B? You can use the COUNTIF formula again, but with a reference to the other sheet.

Let's say List A is on Sheet1 and List B is on Sheet2. On Sheet1, in a helper column, you could use the formula: =IF(COUNTIF(Sheet2!A:A, A2)>0, "Duplicate Found in List B", ""). This will check each name in Sheet1's column A and tell you if it exists anywhere in column A of Sheet2.

Using a PivotTable to Summarise and Spot Duplicates

PivotTables are excellent for summarising data. You can use one to quickly get a count of how many times each item appears.

  1. Select your data and go to Insert > PivotTable.
  2. In the PivotTable Fields pane, drag the field you want to check for duplicates (e.g., 'Email Address') into both the Rows area and the Values area.
  3. Make sure the Values field is set to 'Count of Email Address'. The PivotTable will show you a unique list of all email addresses and how many times each one appears. You can then sort the count column from largest to smallest to see the biggest offenders at the top.

How To Find Duplicates In Excel FAQ

How do I find duplicates in just one column in Excel?
Simply select only that single column before using any of the methods. Click the column letter at the top (e.g., 'B') to highlight the entire column, then use Conditional Formatting or the Remove Duplicates tool. The tools will only operate on the data you have selected.
Can I find duplicates based on multiple columns (e.g., first name and last name)?
Yes. For the Remove Duplicates tool, simply tick the checkboxes for all the columns that need to match (e.g., tick 'FirstName' and 'LastName'). For Conditional Formatting, you need a formula-based rule. Select all your data and use a new rule with a formula like =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1, assuming first names are in column A and last names are in column B.
How do I highlight unique values instead of duplicates?
In the Conditional Formatting dialogue box (Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values), there is a dropdown menu on the left that is set to "Duplicate" by default. Click this and change it to "Unique". Excel will then highlight the values that appear only once in your selected range.
Does this process work in Google Sheets?
Yes, the process is very similar. Google Sheets also has a Conditional Formatting tool with a "Custom formula is" option for finding duplicates. For removing them, Google Sheets has a dedicated tool under Data > Data cleanup > Remove duplicates, which works almost identically to Excel's version.
Why is the 'Remove Duplicates' button greyed out?
The 'Remove Duplicates' button can be greyed out and unclickable for a few reasons. The most common is that your worksheet is protected or shared in a way that prevents structural changes. Another reason could be that you have multiple worksheets grouped together. Ungroup the sheets by right-clicking a sheet tab and selecting 'Ungroup Sheets', and the option should become available.

Final Checklist for Finding Duplicates

Before you consider the job done, run through this quick final checklist to ensure you've covered all the bases and your data is clean and accurate.

  • Backup Created: You have a safe copy of your original file stored separately.
  • "Duplicate" Defined: You were clear on whether a duplicate was an entire identical row or just a match in one or two key columns.
  • Correct Range Selected: You selected the right columns and rows for the check, making sure to include headers if necessary.
  • Right Method Chosen: You used the best method for your goal-highlighting for review, removing for cleaning, or a formula for flexible analysis.
  • Results Verified: You briefly scanned the results to ensure the tool performed as expected and no important data was lost by mistake.
  • Formatting Cleared (Optional): If you used Conditional Formatting for a review, you can clear the formatting afterwards via Conditional Formatting > Clear Rules to clean up the visual appearance of your sheet.

By following these steps, you can confidently find and manage duplicate data in your Excel spreadsheets, leading to more accurate analysis and reporting.