If you have ever worked with a large dataset, you know the pain of spotting repeated rows. Duplicate records skew your totals, break your vlookup excel lookups, and make your spreadsheet harder to trust. The good news? Excel gives you several ways to deal with them—and most take under a minute once you know where to look.
This guide walks you through three reliable methods: the built-in Remove Duplicates button, the COUNTIF formula approach, and Power Query for more complex scenarios. You will also pick up a few habits that prevent duplicates from showing up in the first place.
This is the one most people reach for, and it is genuinely fast. Here is how it works:
Excel will tell you how many duplicate values were removed and how many unique values remain. That is it—done.
One thing to keep in mind: this method permanently deletes the duplicate rows. It does not move them or hide them. If you think you might need them later, make a copy of your data first. Ctrl+Z does undo the action, but only in the current session.
The column selection step trips a lot of people up. By default, all columns are checked—meaning Excel looks for rows identical across every single column before removing anything. That is usually what you want. But sometimes you only care about one column. Say you have got a customer list and want one row per email address, even if the names differ. In that case, uncheck everything except the Email column and Excel will keep only the first occurrence of each address.
Sometimes you want to see which rows are duplicates before you delete them. The COUNTIF method lets you flag them with a helper column first—no data gets deleted until you decide.
Here is the setup:
This formula counts how many times the value in column A has appeared up to and including the current row. The first occurrence gets FALSE; every repeat gets TRUE. You can then filter for TRUE and review those rows before deleting them.
This approach ties in nicely with the countif function on excel you might already use for other validation tasks. The same logic applies—you are just using it to catch repeats rather than count categories.
If a duplicate means matching on two or more columns—say, same first name AND same last name—swap in COUNTIFS: =COUNTIFS(:A2,A2,:B2,B2)>1
Same idea, extended to as many columns as you need. Each pair of arguments adds another condition Excel must match before it flags a row as a repeat.
If you are pulling data from external sources—CSV exports, database dumps, or merged tables—Power Query is worth learning. It handles duplicates as part of a repeatable transformation step, which means every time you refresh your query, the deduplication happens automatically.
To get there:
The real power here is the applied steps panel on the right. Every transformation you make gets recorded. Next time your source data updates, you just hit refresh and Power Query runs the whole process again—remove duplicates included.
Power Query is especially useful when you are working with the cell reference in excel style formulas and need the underlying data to stay clean without manual intervention every week.
Before you remove anything, it is smart to see what you are dealing with. Conditional formatting lets you color-code duplicates so you can review them visually:
All duplicate entries get highlighted instantly. You can scroll through, spot anything that looks off—maybe it is not actually a duplicate, just a coincidence—and then proceed with removal once you are confident.
Removing duplicates after the fact is fine, but stopping them from entering your sheet in the first place is better. Data validation can do this.
Now Excel will reject any entry that already exists in that column. Users get an error message and cannot move on until they enter a unique value. It works especially well for ID columns, email addresses, and product codes—anywhere uniqueness is a business rule, not just a preference.
These are not obscure edge cases. They come up in real spreadsheets with real messy data. The excel if formula and TRIM combination is something experienced users reach for all the time before running any deduplication.
Here is a quick summary to help you pick:
Most Excel users end up using all four at different times. A quick conditional format to see what is there, a COUNTIF to flag the rows, a review, then the Remove Duplicates button to finish. That is a solid workflow and it only takes a few minutes once you are comfortable with each step.
If you are building toward a certification or want to sharpen your skills beyond basic deduplication, the Excel Skills Practice Test covers the full range of Excel functions tested in workplace assessments.
If you are on a modern version of Excel—Microsoft 365 or Excel 2021—you have access to the UNIQUE function, and it changes things considerably. Instead of deleting duplicate rows in place, UNIQUE spills a deduplicated list into a separate range. The original data stays untouched.
The syntax is simple: =UNIQUE(A2:A100). That returns every unique value in the range as a dynamic array. You can wrap it with SORT to alphabetize the result, or nest it inside other functions for more complex analysis.
For full rows not just a single column, use: =UNIQUE(A2:C100). Excel returns all rows that are unique across all three columns—same logic as Remove Duplicates, but non-destructive and auto-updating.
This is genuinely one of the most useful additions in modern Excel. The downside is compatibility—if you share the file with someone on Excel 2016 or earlier, the formula breaks. In that case, the COUNTIF approach is safer for shared workbooks.
If your duplicates are spread across worksheets rather than within a single sheet, the process gets a bit more involved. Power Query handles this well—you can combine multiple sheets into one query, then remove duplicates across the merged dataset.
Here is a quick approach:
It is more steps than a single-sheet cleanup, but once the query is set up, you can refresh it any time new data gets added. The how to share a workbook in excel workflow becomes much smoother when the underlying data is already clean.
When your spreadsheet has tens of thousands of rows, a few extra habits pay off:
These steps add maybe two minutes to the process but they save you from discovering a data issue three hours later when your pivot tables do not match expectations. The excel subtract formula and SUM checks are useful sanity tests to run on totals before and after deduplication to make sure nothing important was accidentally removed.
Excel always keeps the first occurrence and deletes subsequent ones. If you need to keep the last occurrence instead, sort your data in reverse order first, run the deduplication, then sort back to the original order.
Yes—Ctrl+Z will undo a Remove Duplicates action, but only within the current session. Once you close and reopen the file, the undo history is gone. Always save a backup copy before running the deduplication if there is any chance you will need the original data.
This usually comes down to invisible differences—trailing spaces, different capitalization, or hidden characters. Run =TRIM(A2) on your data first to strip extra spaces, then try again. If the issue is capitalization, use =LOWER() to normalize everything before the deduplication step.
Remove Duplicates modifies your existing data in place—it permanently deletes the duplicate rows. UNIQUE (available in Excel 365 and 2021) creates a separate output range showing only unique values, leaving your original data completely intact. UNIQUE is the safer choice when you are not certain you want to delete.
In the Remove Duplicates dialog, check both columns. Excel will then only remove a row if it matches another row in both of those columns simultaneously. For the COUNTIF formula approach, switch to COUNTIFS and add a second range and criteria pair for the second column.
Yes—that is the main advantage. Once you set up a Remove Duplicates step in Power Query, every time you click Refresh, it applies that step again to the incoming data. This is ideal for weekly or monthly data imports where duplicates are a recurring issue.
No. Remove Duplicates ignores any active filters—it runs on all rows in your selected range, including hidden ones. If you only want to clean up visible rows, copy the filtered results to a new sheet first, then run Remove Duplicates on that copy.
Cleaning duplicate data does not have to be a chore. The Remove Duplicates button gets you there in seconds for straightforward cases. COUNTIF gives you control when you want to review before deleting. Power Query makes the whole process repeatable for ongoing data pipelines. And UNIQUE—if you are on a modern Excel version—is the cleanest approach of all: no deletion, just a fresh deduplicated output that updates automatically.
The key habits are backing up before you delete, checking which columns define duplicate for your specific dataset, and using TRIM or LOWER to normalize data when you are seeing unexpected mismatches. Those three steps will save you from most of the headaches that come with deduplication in real-world spreadsheets.
Want to go deeper? The how to use excel guide covers formulas, data tools, and best practices that go well beyond what most users pick up on the job. It is a solid next read once you have got deduplication handled—especially if you are heading toward a certification or a role where Excel is a daily tool.