How to Remove Duplicates in Excel (3 Fast Methods) 2026

How to Remove Duplicates in Excel

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.

Method 1: The Remove Duplicates Button (Fastest)

This is the one most people reach for, and it is genuinely fast. Here is how it works:

1. Click anywhere inside your dataset.
2. Go to the Data tab on the ribbon.
3. Click Remove Duplicates in the Data Tools group.
4. A dialog box opens listing every column in your table. Check the columns you want Excel to consider when deciding what counts as a duplicate.
5. Click OK.

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.

Choosing the Right Columns

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.

Method 2: COUNTIF Formula (Non-Destructive)

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:

1. Add a new column next to your data—call it something like "Duplicate?"
2. In the first data row of that column, enter: =COUNTIF(:A2,A2)>1
3. Drag the formula down through all your rows.

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.

Using COUNTIFS for Multi-Column Duplicates

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.

Method 3: Power Query (Best for Large or Recurring Data)

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:

1. Select your data range and go to Data then From Table/Range. Excel opens the Power Query Editor.
2. Select the column or columns you want to deduplicate on. Hold Ctrl to select multiple.
3. Right-click the column header and choose Remove Duplicates.
4. Click Close and Load to send the cleaned data back to your sheet.

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.

Conditional Formatting to Highlight Duplicates

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:

1. Select your column or the whole table.
2. Go to Home then Conditional Formatting then Highlight Cells Rules then Duplicate Values.
3. Choose a fill color and click OK.

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.

Preventing Duplicates with Data Validation

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.

1. Select the column where duplicates keep appearing.
2. Go to Data then Data Validation then Custom.
3. Enter: =COUNTIF(:,A1)=1

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.

Common Mistakes When Removing Duplicates

• Not backing up first. Remove Duplicates is permanent. Copy your sheet before running it on anything important.
• Forgetting to include all relevant columns. If your duplicate is defined by matching two or more fields, make sure all of them are checked in the dialog.
• Ignoring leading or trailing spaces. "Apple" and "Apple " look the same to you but not to Excel. Use TRIM() on your data first if spacing inconsistency is a concern.
• Case sensitivity. Excel built-in Remove Duplicates is not case-sensitive—"APPLE" and "apple" count as the same. If case matters, you will need a custom formula approach.

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.

Which Method Should You Use?

Here is a quick summary to help you pick:

• Remove Duplicates button—best for a quick one-time cleanup where you are confident in the data
• COUNTIF formula—best when you want to review duplicates before deleting, or need a non-destructive flag
• Power Query—best for recurring imports or large datasets where you need the process to be repeatable
• Conditional Formatting—best as a visual preview before you commit to any deletion

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.

Many readers pair this guide with the microsoft xl to check their readiness before exam day.

Many readers pair this guide with the ms excel download to check their readiness before exam day.

Many readers pair this guide with the test excel to check their readiness before exam day.

Microsoft Excel Exam at a Glance

Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.

Using the UNIQUE Function (Excel 365 and Excel 2021)

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.

Removing Duplicates from Multiple Sheets

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:

1. Go to Data then Get Data then From Other Sources then Blank Query.
2. Use the M formula =Excel.CurrentWorkbook() to pull in all tables from your workbook.
3. Expand the Content column to see all rows.
4. Apply Remove Duplicates on the relevant columns.
5. Load the result back to a new sheet.

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.

Tips for Working with Large Datasets

When your spreadsheet has tens of thousands of rows, a few extra habits pay off:

• Convert to a Table first. Use Ctrl+T to turn your range into an Excel Table. Tables play well with both Remove Duplicates and Power Query, and they make column references cleaner in formulas.
• Sort before deduplicating. If you care about which duplicate gets kept—say, you want the most recent record—sort by date descending first. Remove Duplicates always keeps the first row it encounters.
• Check your row count before and after. Use the status bar at the bottom of Excel or a quick COUNTA formula to confirm how many rows you started with and how many remain after the cleanup.

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.

Putting It All Together

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.