How To Identify Duplicates In Excel
A practical step-by-step guide to how to identify duplicates in excel, including preparation, instructions, common issues, tips, and next steps.
How To Identify Duplicates In Excel
This guide shows you how to find and manage duplicate entries in your Microsoft Excel spreadsheets. Cleaning up duplicate data is essential for accurate calculations, creating reliable reports, and keeping your lists tidy. We'll cover three simple methods: a safe way to highlight duplicates for review, a quick tool to delete them permanently, and a flexible formula-based approach for more control. Whether you're a student, a small business owner, or just organising personal data, these steps will help you maintain clean and trustworthy data.
Fast Answer
- Tool: Conditional Formatting
- Menu Path: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values...
- Result: Instantly highlights duplicate cells in a colour of your choice without deleting anything.
Before You Start
Before you begin cleaning your data, it's important to prepare your file and understand the tools you're about to use. A small amount of preparation can save you from a big headache later on.
- Microsoft Excel: You will need a working copy of Microsoft Excel. These instructions are for modern versions like Excel for Microsoft 365, Excel 2021, and Excel 2019, but the core features work in older versions too.
- Your Data File: Have your Excel file (.xlsx, .xls, or .csv) open and ready. This could be a customer list, sales report, or any other dataset where you suspect duplicates exist.
Step-by-Step Instructions
We'll walk through three effective methods for dealing with duplicates. Start with the first method if you just want to see where the problems are, and use the second when you're ready to delete them.
Method 1: Highlight Duplicates with Conditional Formatting
This is the safest and most common way to identify duplicates. It doesn't change or delete any of your data; it simply applies a colour to cells that contain repeated values, allowing you to review them manually. It's perfect for getting a quick visual overview.
- Select your data. Click and drag your mouse to highlight the cells, column, or multiple columns you want to check. For example, to find duplicate email addresses, click the letter at the top of the email column to select the entire thing.
- Find Conditional Formatting. On the Excel ribbon at the top of the screen, click on the Home tab. Look for the Conditional Formatting button in the 'Styles' section.
- Choose the duplicate rule. Click on Conditional Formatting, hover your mouse over Highlight Cells Rules in the dropdown menu, and then click on Duplicate Values....
- Set the formatting. A small window will pop up. The first dropdown box should already be set to Duplicate. In the second box, you can choose how you want the duplicate cells to look. The default 'Light Red Fill with Dark Red Text' is usually fine. Click OK.
Instantly, all the values that appear more than once in your selected range will be highlighted with the colour you chose. You can now scroll through your data and see exactly where the duplicates are.
Method 2: Remove Duplicates with the Built-in Tool
When you're confident that you want to permanently delete duplicate rows, Excel's 'Remove Duplicates' tool is the fastest way to do it. This action is irreversible, so remember the warning from the 'Before You Start' section: always work on a copy of your file.
- Select your entire data set. It's important to select all the columns of your table, including the headers. An easy way to do this is to click any single cell inside your data and press Ctrl + A on your keyboard.
- Open the Remove Duplicates tool. Go to the Data tab on the Excel ribbon. In the 'Data Tools' group, look for and click on the Remove Duplicates button.
- Configure the columns to check. A new window will appear, listing all the column headers from your data. If your list has headers, make sure the box for 'My data has headers' is ticked.
- Select which columns define a duplicate. This is the most important step. You need to tell Excel what makes a row a duplicate.
- To remove rows with a duplicate email address, tick only the 'Email' column.
- To remove rows where the First Name and Last Name are both the same, tick both of those columns. This is more precise and won't remove two different people who happen to share a last name.
- Confirm the removal. After selecting your columns, click OK. Excel will scan your data, remove the duplicate rows, and then show a message telling you how many duplicates were removed and how many unique rows remain.
Method 3: Use the COUNTIF Formula to Find Duplicates
This method gives you the most flexibility. Instead of highlighting or deleting, you'll use a formula to add a new 'helper' column to your sheet. This column will count how many times each value appears in your list. It’s a non-destructive way to get more insight into your data.
- Insert a new helper column. Right-click on the column letter to the right of your data (for example, if your data ends in Column C, right-click on Column D) and choose Insert. Give this new column a name in the first row, like 'Duplicate Count'.
- Enter the COUNTIF formula. Let's say you want to check for duplicate names in Column A, and your data starts on row 2. In the first cell of your new helper column (e.g., D2), type the following formula: =COUNTIF($A:$A, A2)
- Understand the formula.
- $A:$A tells Excel to look in the entire Column A for every calculation. The dollar signs ($) lock the reference so it doesn't change as you copy the formula down.
- A2 tells Excel to count how many times the value from the current row's cell (A2) appears in the range we just defined.
- Press Enter. The formula will return a number. If it's a 1, that value is unique (so far). If it's 2 or higher, it's a duplicate.
- Copy the formula down. Click once on the cell containing your new formula. Move your cursor to the small square at the bottom-right corner of the cell until it turns into a black cross (this is called the fill handle). Double-click the fill handle, and Excel will automatically copy the formula all the way down to the end of your data.
You now have a new column that clearly flags every duplicate. To easily see them, you can click the 'Filter' button on the 'Data' tab and filter your 'Duplicate Count' column to only show rows where the value is greater than 1.
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.
Document what changed
Save the final settings, decisions, or checks so the same process is easier to repeat later.
Quick Reference
Not sure which method to use? This table summarises the best tool for each situation.
| 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, completely safe, and gives you a great visual guide. It makes no permanent changes to your data. |
| I'm sure I need to delete all duplicate rows for good. | Remove Duplicates Tool | This is the quickest way to permanently clean a list. Just be sure to back up your file first as it cannot be undone easily. |
| I need more control, or want to filter or move the duplicates elsewhere. | COUNTIF Formula | This method is flexible. It creates a new column of data you can sort and filter without touching your original information. |
Common Problems When You Identify Duplicates in Excel
Sometimes things don't go as planned. Here are a few common issues and how to fix them.
Problem: The "Remove Duplicates" button is greyed out.
This usually happens for one of two reasons. First, you might be in 'cell editing mode' (with the blinking cursor inside a cell). Click out of the cell to fix this. Second, your worksheet may be protected. Go to the Review tab and look for an Unprotect Sheet button. You may need a password if one was set.
Problem: Excel is highlighting things that don't look like duplicates.
The most common cause is hidden characters, especially extra spaces. To Excel, "London " (with a space at the end) is a completely different value from "London" (with no space). You can fix this by using the TRIM function to clean your data. In a helper column, use the formula =TRIM(A2) to remove extra spaces from the text in cell A2. Copy this formula down, then copy the clean data and use Paste Special > Values to replace the original messy data.
Problem: The wrong data was deleted when I used "Remove Duplicates".
This is a critical issue and highlights the importance of backups. It likely happened because you didn't select the right columns to define a duplicate. For example, if you remove duplicates based only on a 'City' column in a customer list, you will be left with only one customer per city. To fix this, close the file without saving and open your original backup. Try the process again, but be much more careful about which column checkboxes you tick in the 'Remove Duplicates' window.
Advanced Tips for Identifying Duplicates in Excel
Once you're comfortable with the basics, you can try these more powerful techniques for complex situations.
- Find Duplicates Across Two Different Sheets: The standard tools only work on one sheet at a time. To compare Sheet1 against Sheet2, you can use a formula. On Sheet1, in a helper column, you could use a formula like =IF(COUNTIF(Sheet2!A:A, A2)>0, "Duplicate", "Unique"). This formula checks if the value from cell A2 on Sheet1 exists anywhere in column A of Sheet2 and labels it accordingly.
- Use Power Query for Large Datasets: If you work with very large files, Power Query (also known as Get & Transform) is an incredibly powerful tool. You can load your data into the Power Query editor (from the Data tab), where you can find a dedicated 'Remove Duplicates' button. The advantage is that this creates a repeatable process that doesn't harm your original data source and can be easily refreshed if the source data changes.
- Highlight the Entire Duplicate Row: By default, Conditional Formatting only colours the single cell containing the duplicate value. To highlight the entire row for better visibility, you need a formula-based rule. First, select all of your data. Then go to Conditional Formatting > New Rule > Use a formula to determine which cells to format. In the formula box, enter a COUNTIF formula that refers to the key column, making sure to lock the column with a dollar sign. For example: =COUNTIF($C:$C, $C2)>1. This tells Excel to check the count for the value in column C for each row, and if it's greater than 1, format the entire row.
How To Identify Duplicates In Excel FAQ
Can Excel find duplicates automatically?
Yes. The Conditional Formatting feature can highlight duplicates automatically as soon as they are entered, providing real-time feedback. For cleaning an entire list at once, the 'Remove Duplicates' tool finds and deletes them in a single operation.
How do I find duplicates based on two or more columns?
The 'Remove Duplicates' tool is perfect for this. When the tool's window opens, simply tick the checkboxes for all the columns you want to check. For example, to find people with the same first name and last name, tick both the 'First Name' and 'Last Name' columns. Excel will then only consider a row a duplicate if the data in all selected columns matches another row.
Does this work for numbers and dates as well as text?
Yes, all these methods work perfectly with any data type, including numbers, dates, and times. Excel treats the number 500 as a duplicate of 500 in the same way it treats the word 'Report' as a duplicate of 'Report'.
Is there a way to move duplicates to another sheet instead of deleting them?
Yes, this is a great use for the COUNTIF formula method. After you've created your helper column that flags duplicates (with numbers greater than 1), apply a filter to that column to show only the duplicate rows. Then, select all the visible filtered rows, copy them (Ctrl + C), go to a new worksheet, and paste them (Ctrl + V). This effectively archives your duplicates for review without deleting them from the original list.
Final Checklist for Identifying Duplicates in Excel
Before you consider your data clean, run through this quick final checklist to make sure you've covered all the bases and avoided common mistakes.
- Saved a Backup Copy: Have you saved a separate copy of your original file before using any deletion tools?
- Selected the Right Data: Did you correctly highlight the entire column or data table you intended to check?
- Chose the Right Method: Did you use Conditional Formatting for a safe visual check, or the Remove Duplicates tool for permanent cleaning?
- Checked Correct Columns: If you removed duplicates, are you certain you selected the correct combination of columns that defines a unique entry?
- Reviewed the Results: After the process, did you do a quick visual scan of your data to ensure the outcome was what you expected?
