Ever feel like your Excel sheets are getting bogged down by duplicate data? It can throw off your analysis and waste time. But don’t worry—cleaning up duplicates in Excel is quick and easy once you know the right steps.
In this guide, I’ll show you how to remove duplicates using built-in tools, Conditional Formatting, and formulas. No more headaches—just fast, accurate data.
Ready to get started? Let’s jump in!
Sidenote: If you want to take your Excel skills to the next level, check out my Complete Excel course.
You’ll learn Excel from scratch including advanced topics like data analysis & modeling, pivot tables, and VBA. You'll build your own professional spreadsheets and charts, and have the skills to get hired as a Data Analyst!
All online, and updated every year.
With that out of the way, let’s get into this 5-minute tutorial, and show you how to find and remove duplicates in your Excel spreadsheets.
How to protect your data while removing duplicates in Excel
Before you start removing duplicates, it’s important to make sure that you don’t accidentally lose any valuable information in the process.
The good news is that Excel provides a few simple ways to safeguard your data while you clean it up!
How to create a backup of your data
The easiest and safest way to avoid losing anything is to make a backup copy of your worksheet. It only takes a moment and gives you peace of mind. If something goes wrong, you’ll still have your original data.
Here’s how to back up your data:
- Right-click on the sheet tab at the bottom of Excel
- Select “Move or Copy…”
- Check the box that says “Create a copy,” then click OK
- You’ll now have a duplicate sheet to use as a backup
How to rollback mistakes with Excel’s version history
If you’re working in Excel Online or saving files in OneDrive or SharePoint, you can also use Excel’s version history. This handy feature automatically saves earlier versions of your workbook, so you can always roll back if you delete something by accident.
To access it:
- Click on File in the top menu
- Go to Info and select Version History
- You’ll see a list of previous versions, each with a timestamp. You can open any of these and restore your data if something goes wrong
Highlight your duplicates before deleting
Finally, you can also check your duplicates in advance, by highlighting and inspecting them.
This gives you a clear visual of what’s duplicated, so you can decide whether to remove it or keep it. We’ll dive deeper into how to do this with conditional formatting and formulas later in the guide.
Before then though, let's walk through how to find and remove duplicates using Excel's built-in tools.
How to use Excel’s built-in tool to remove duplicates
When you need to quickly clean up your dataset, Excel’s built-in 'Remove Duplicates' tool is your go-to. It makes finding and removing duplicates easy, but you’ll need to use it carefully to avoid deleting important data that just looks similar.
Let’s break down the steps so you can clean your data while keeping everything safe.
Step #1: Select your data
The first thing you need to do is decide which part of your data to check for duplicates. I’ve set up an example with customer IDs, names, emails, contact numbers, and purchase dates.
You can choose whether to select the entire table or just specific columns where duplicates are most likely to hide.
For now, I’ve selected everything. But remember - being picky about which data you choose to check is key. You don’t want to accidentally remove rows that aren’t true duplicates.
Step #2: Open the ‘Remove Duplicates’ tool
With your data selected, go to the Data tab and click on the Remove Duplicates button. This will open a dialog box where you’ll make a few important decisions.
Once you click on this, a new popup will appear.
Step #3: Choose the columns to check
In the Remove Duplicates dialog, you can choose which columns Excel should use to check for duplicates.
This step gives you full control over which parts of the data to compare. If you only want to check specific columns—like names and emails—uncheck the others.
Step #4: Review and confirm
Once you’ve chosen your columns, click OK. Excel will give you a summary of how many duplicates were removed and how many unique rows remain.
This lets you quickly see what’s changed.
Step #5: Verify your results
Finally, take a moment to scan through your updated data. This helps you catch any mistakes before they become an issue, especially with larger or more complex datasets.
Troubleshooting: What to do if Excel misses duplicates
Excel’s Remove Duplicates tool usually works well, but sometimes it misses duplicates based on how the data is compared across different columns.
But don’t worry - this isn’t always a mistake! Sometimes repeated data is valid and should stay in your dataset.
For example
In our dataset, we might notice that Alice Smith appears twice. At first glance, this seems like a duplicate. But is it really a mistake?
Why does this happen and should you remove it?
Excel looks at all the columns you selected for comparison. If even one value is different—like a Customer ID—Excel will treat the entire row as unique. So, in Alice’s case, the Customer ID might be different, but the Name, Email, and Phone are the same.
Now, before you remove anything, ask yourself:
- Is this data truly duplicated?
- Do the differences in certain columns (like Customer ID) represent valid distinctions?
- Could this duplicate actually be two separate, valid entries—like multiple purchases or transactions?
How to fix it (if needed)
If you’re certain the data represents true duplicates and not valid separate entries, here’s how to fix it:
- Re-open the Remove Duplicates Tool: Go back to the Data tab and click Remove Duplicates again
- Deselect the Problematic Column: In this case, deselect Customer ID since the different ID values are causing Excel to miss duplicates. You can keep Name, Email, and Phone selected to identify duplicates based on those fields
- Run the Tool Again: After deselecting Customer ID, click OK and Excel will now correctly identify the duplicate rows.
In this example, Excel successfully identified and removed the duplicate after we excluded Customer ID from the comparison:
Simple!
Again though, double-check this is actually a valid duplicate before doing this.
How to use conditional formatting to highlight duplicates
Remember how I said earlier that sometimes, before deleting duplicates, you’ll want to see them visually first?
Well, that’s where Excel’s Conditional Formatting comes in handy, as it lets you color-code duplicates based on the criteria you set.
This is especially useful if you’re dealing with partial duplicates or want more control before making any changes.
For example
Imagine you have duplicate names but different purchase dates.
With Conditional Formatting, you can scan your data visually before deciding what to do. This way, you can keep certain duplicates while removing others.
So let’s walk through how to highlight duplicates using Conditional Formatting.
Step #1. Select the range of data
Start by selecting the range of data where you want to check for duplicates. This could be a single column or multiple columns, depending on what you’re looking for.
In this example, I’ll select the entire Email column to find duplicate email addresses.
Step #2. Open conditional formatting
With your data selected:
- Go to the Home tab
- Click on Conditional Formatting
- Click on Highlight Cells Rules
- Then select Duplicate Values
This will open a dialog box.
Step #3. Customize the formatting
In the dialog box that pops up, you can choose how Excel should highlight the duplicates.
By default, Excel uses a light red fill with dark red text, but feel free to customize the colors to make the duplicates stand out however you like.
Step #4. Review the highlighted duplicates
Once you click OK, Excel will highlight all the duplicates in the range you selected.
Now you can scroll through and visually inspect the flagged duplicates. This method helps you spot patterns or entries that need further investigation before you make any changes.
Here we can see the duplicate emails. However, if we had selected the entire range like before, we would also have noticed the 2nd purchase issue again.
How to use Excel formulas to identify duplicates
If you want more control over how you identify duplicates, using formulas is a powerful approach. It’s especially helpful when you need to find duplicates based on specific criteria or when you want to flag duplicates across multiple columns.
Here’s a step-by-step breakdown on how to use formulas to spot duplicates in Excel.
Step #1. Write the COUNTIF Formula
We’ll start with Excel’s COUNTIF function, which counts how many times a specific value appears in a selected range. In this case, we’ll check for duplicate emails.
Since emails are in column C, use this formula:
=COUNTIF(C:C, C2) > 1This formula checks how many times the value in C2 (the email) appears in all of column C. If the count is greater than 1, it returns TRUE, meaning the value is a duplicate.
In our example, this shows that the email in C2 appears in customer ID 101 and 104 - indicating two seperate transactions.
Sidenote: If we wanted to check column B instead so that we could filter by name, then we would change the formula to:
=COUNTIF(B:B, B2) > 1Same for column D, E, F and so on.
Step #2: Copy the formula down the range
Once you’ve entered the formula, go ahead and drag it down until the end of the column, so that it will apply the formula to the rest of the cells in your dataset.
Also, don’t worry about having to change the code from C2 to C3 etc, as Excel will automatically adjust the cell references, checking each email for duplicates.
Step #3. Review the results
Once you’ve applied the formula, take a look at the results—specifically, the TRUE values. These indicate where duplicates are present in your dataset. Keep in mind, this method is only flagging duplicates; it hasn’t made any changes to your data.
This gives you the chance to carefully review the duplicates before taking any action. It’s especially helpful for larger datasets, where manually spotting duplicates could take a long time or result in mistakes.
By seeing where duplicates exist without making immediate changes, you maintain control over the process.
Step #4. Take action on the duplicates
Now that you know where your duplicates are, it’s time to decide what to do with them. Based on your needs, you can either delete the rows, merge the duplicated data into one entry, or keep certain duplicates if they serve a valid purpose (like multiple purchases from the same customer).
The beauty of using formulas is that it gives you flexibility. You’re not locked into one action—you have full control to handle duplicates in a way that best suits your data and workflow.
Ready to take control of your data?
So there you have it, folks! Now you’ve got three solid ways to remove duplicates in Excel:
- Excel’s built-in Remove Duplicates tool
- Using Conditional Formatting
- Applying a
COUNTIFformula
Give these methods a try in your own spreadsheets and see how much cleaner your data can be. Whether you're working on a simple list or a complex dataset, these techniques will save you time and keep your work organized.
P.S.
Remember, if you want to take your Excel skills to the next level, and even open up new career options, then check out my Complete Excel course.
You’ll learn Excel from scratch including advanced topics like data analysis & modeling, pivot tables, and VBA. You'll build your own professional spreadsheets and charts, and have the skills to get hired as a Data Analyst!
Plus, once you join, you'll have the opportunity to ask questions in our private Discord community from me, other students, and working Data professionals.
Check it out today.
More Excel Tutorials
Like this post? Then you'll love my other Excel guides and tutorials:
