Duplicate data can cause chaos in your spreadsheets, leading to inaccurate reports, flawed analysis, and wasted time. Whether you're cleaning a customer list, tidying up survey results, or preparing financial data, finding and dealing with these unwanted entries is a crucial skill. This guide shows you two simple, built-in Excel methods to quickly highlight, review, and remove duplicates. We’ll cover the safest way to just see them, and the permanent way to delete them, ensuring your data is clean, accurate, and reliable.
Fast Answer
- Action: Select your data range.
- Path: Go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
- Result: Excel will instantly highlight all duplicate cells in a colour of your choice.
Before You Start
- A computer with Microsoft Excel: These instructions are for Excel 2007 and newer versions, including Microsoft 365. The interface is very similar across versions.
- Your spreadsheet file: Have your .xlsx, .xls, or .csv file open and ready.
- A clear goal: Decide if you want to simply highlight duplicates for review or remove them permanently.
Step-by-Step Instructions
Method 1: Highlight Duplicates (The Safe Way)
This method is perfect for reviewing duplicate entries without changing or deleting any data. It uses Conditional Formatting to apply a colour to any cells that have duplicate values, making them easy to spot.
Select the Data You Want to Check
First, you need to tell Excel where to look. Click and drag your mouse to select the cells, column, or multiple columns you want to check for duplicates. For a single column, you can click the column letter (e.g., 'A') at the top. To select all your data quickly, click any single cell within your data table and press Ctrl + A.
Navigate to Conditional Formatting
With your data selected, look at the main Excel ribbon at the top of the screen. Click on the Home tab. In the 'Styles' group, you will find the Conditional Formatting button. Click it to open a dropdown menu of options.
Apply the Duplicate Values Rule
From the Conditional Formatting menu, hover over the first option, Highlight Cell Rules. A secondary menu will appear. From this menu, click on Duplicate Values.... This will open a small dialogue box to configure the rule.
Choose a Formatting Style
The dialogue box gives you two simple choices. The first dropdown should already say 'Duplicate'. The second dropdown, labelled 'values with', lets you pick how the duplicates will be highlighted. The default is 'Light Red Fill with Dark Red Text', which is usually fine.
You can click this dropdown to choose other presets like 'Yellow Fill' or 'Green Fill'. For a custom look, select 'Custom Format...' to choose your own font, border, and fill colours. Once you're happy, click OK.
Review and Filter the Highlighted Duplicates
Instantly, all duplicate values in your selected range will be coloured in. You can now scroll through your data to see them. If you have a large dataset, it's easier to group them. Go to the Data tab and click the Filter button. A small dropdown arrow will appear in your header row. Click the arrow for the column with duplicates, choose Filter by Colour, and select the highlight colour you just applied. Now, only the duplicate rows will be visible, making them easy to review or delete manually.
Method 2: Remove Duplicates Permanently (The Fast Way)
If you're confident you want to get rid of duplicate records entirely, Excel's 'Remove Duplicates' tool is the most efficient way. Remember, this action is permanent, so ensure you are working on a copy of your file.
Select Your Data Range
Just as before, select the entire range of data you want to clean. It's usually best to select the whole table by pressing Ctrl + A to ensure Excel looks at entire rows as records, preventing accidental deletion of data that is only partially duplicated.
Locate the 'Remove Duplicates' Tool
Navigate to the Data tab on the Excel ribbon. In the 'Data Tools' group, look for the icon with two blue columns and a red 'x'. This is the Remove Duplicates button. Click it.
Configure the Duplicate Check
This is the most critical step. A dialogue box will appear, listing all the columns in your selected data. Excel will ask you which columns it should use to identify a duplicate.
- To remove rows that are 100% identical across all columns, make sure all column boxes are ticked.
- To remove rows based on a duplicate value in just one specific column (like an email address), untick all boxes except for the 'Email' column. Be very careful with this, as it could remove people with unique names who accidentally share an old email address.
Also, ensure the 'My data has headers' box is ticked if your first row contains column titles. This prevents Excel from treating your headers as data. Click OK.
Confirm the Result
Excel will immediately process the data and show a confirmation message, telling you how many duplicate values were found and removed, and how many unique values remain. For example: "15 duplicate values found and removed; 120 unique values remain." Click OK. Your data is now clean. Take a moment to scroll through and spot-check the result to ensure everything looks correct before saving.
Quick Reference
| Situation | Use this | Why |
|---|---|---|
| I just want to see the duplicates | Conditional Formatting | It's a safe, non-destructive way to highlight cells without changing any data. |
| I need to permanently delete duplicate rows | Data > Remove Duplicates | It's the fastest way to clean a list, but be careful as it's a permanent action. |
| I need to find rows that are duplicated across specific columns (e.g., same name and postcode) | Data > Remove Duplicates (and select only those columns) | It gives you precise control over what defines a 'duplicate record'. |
| I need to count how many times each value appears | COUNTIF Formula (`=COUNTIF(A:A, A2)`) | This lets you identify not just duplicates, but triplicates and more, giving you greater insight. |
Common Problems When You Find Duplicates In Excel
Sometimes things don't go as planned. Here are some common issues and how to fix them.
Duplicates Aren't Being Found or Highlighted
If Excel isn't finding duplicates you can clearly see, the problem is almost always subtle differences in the data. Check for:
- Extra Spaces: A value like " John Smith" (with a leading space) is different from "John Smith". Use the TRIM function in a new column (`=TRIM(A2)`) to clean your data first.
- Inconsistent Formatting: A number stored as text will not match the same number stored as a number. Use the 'Format Painter' or change the cell format to ensure consistency.
- Hidden Characters: Data imported from other systems can contain non-printing characters. The CLEAN function (`=CLEAN(A2)`) can help remove them.
- Case Sensitivity: Standard duplicate checks are not case-sensitive ("Apple" and "apple" are duplicates). However, if you are using more advanced formula-based checks, this can become a factor.
The 'Remove Duplicates' Tool Deleted Unique Data
This is a scary moment, but usually fixable if you act fast. Press Ctrl + Z immediately to undo the action. The most common cause is misconfiguring the columns in the 'Remove Duplicates' dialogue box. If you only selected the 'First Name' column in a customer list, Excel will delete every person who shares a first name after the first instance, regardless of their last name or address. Always double-check which columns are ticked and ensure they collectively represent a unique record.
Advanced Tips for Finding Duplicates in Excel
Once you've mastered the basics, you can use more powerful techniques for complex situations.
Use the COUNTIF Formula for More Control
The `COUNTIF` formula lets you count how many times a value appears in a range. This is incredibly useful for finding items that appear more than once. In a blank column next to your data, enter the formula: `=COUNTIF($A$2:$A$100, A2)` (adjust the range `$A$2:$A$100` to match your data). Drag this formula down. Any row with a result greater than 1 is a duplicate. You can then filter this column to show all values `> 1`.
You can even combine this with Conditional Formatting. Select your data, go to `Conditional Formatting > New Rule > Use a formula to determine which cells to format`, and enter the formula `=COUNTIF($A$2:$A$100, A2)>1`. This gives you precise, formula-driven highlighting.
Use Power Query for Large and Recurring Tasks
If you work with large datasets or need to clean data regularly, Excel's Power Query (also known as Get & Transform Data) is the ultimate tool. You can find it under the Data tab. Power Query allows you to connect to your data source, and one of its standard transformations is 'Remove Duplicates'. The key benefits are:
- It's Non-Destructive: Power Query works on a preview of your data and loads the clean version into a new sheet, leaving your original data untouched.
- It's Repeatable: Every cleaning step you take is recorded. The next time you get a new version of the file, you can simply click 'Refresh', and Power Query will repeat all the steps automatically.
How To Find Duplicates In Excel FAQ
How do I find duplicates based on two columns?
The easiest way is using the Data > Remove Duplicates tool. When the dialogue box appears, make sure only the two columns you want to check are ticked. This tells Excel to consider a row a duplicate only if the values in both of those specific columns match.
Can I find duplicates without deleting them?
Yes, absolutely. This is the recommended first step for any data cleaning task. Use the Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values method. This will simply apply a colour to the duplicate cells, allowing you to review them safely without altering any data.
Is there a keyboard shortcut to find duplicates?
There isn't a single shortcut like Ctrl+D, but you can use the keyboard sequence that accesses the ribbon commands. Press and release the keys one by one: Alt, H, L, H, D. This quickly opens the 'Duplicate Values' formatting dialogue box after you've selected your data.
How is finding duplicates different in Google Sheets?
The process is very similar. To highlight duplicates in Google Sheets, you use a custom formula in conditional formatting. Select your data, go to `Format > Conditional formatting`, set the format rule to `Custom formula is`, and enter the formula `=COUNTIF(A:A,A1)>1`. For removing duplicates, Google Sheets also has a built-in `Data > Data cleanup > Remove duplicates` feature that works almost identically to Excel's.
Final Checklist for Finding Duplicates in Excel
Before you save and close your file, run through this final checklist to ensure you've handled your duplicates correctly and safely.
- Saved a Backup: Did you make a copy of your file before you started deleting anything?
- Selected Correct Range: Was your entire data table selected, or just the specific columns you intended to check?
- Chose the Right Method: Did you use Conditional Formatting for a safe review or the Remove Duplicates tool for permanent deletion?
- Checked Column Selections: If removing duplicates, did you carefully tick the correct columns that define a unique record?
- Reviewed the Output: Have you scrolled through the cleaned data to spot-check that no important, unique information was lost?
- Saved the Final Version: Once you're confident, save your cleaned file, perhaps with a new name like `filename_cleaned.xlsx` to distinguish it from the original.
Reader note
When a page mentions a commercial resource, Hotgingerweb tries to keep the decision criteria visible beside the link. The practical fit matters more than the promotion.