Finding duplicates in Excel is one of the most common data cleaning tasks — whether you're auditing a customer list, checking for double-entered transactions, or comparing two spreadsheets for matching records. Excel has several built-in tools for identifying duplicates, but each one works differently and excels (no pun intended) in different scenarios. Choosing the right method depends on whether you want to see duplicates visually, flag them with a formula, or extract a clean deduplicated list.

The fastest method is Conditional Formatting, which highlights duplicate cells in a range with color so you can spot them at a glance without writing any formulas. But visual highlighting has limits — it doesn't tell you how many times a value appears, and it doesn't let you filter or remove duplicates programmatically. For that, you need the COUNTIF formula, which gives you a count of how many times each value appears and lets you use that count to build filters, conditional logic, or triggered actions elsewhere in your spreadsheet.

More advanced users reach for Power Query when working with large tables. Power Query can identify duplicates across multiple columns simultaneously, let you keep only the first occurrence of each record, and handle datasets with tens of thousands of rows that would slow down formula-based approaches. It's also non-destructive — Power Query creates a new output table, leaving your source data intact. For everyday data quality work, most Excel users end up switching between two or three of these methods depending on the size and format of the data they're checking.

This guide covers all five major methods for finding duplicates in Excel, with step-by-step instructions for each. You'll also learn how to find duplicates across two columns, how to find row-level duplicates where every cell in the row matches another, and how to avoid common mistakes like missing duplicates caused by trailing spaces or inconsistent data types. By the end, you'll know exactly which method to reach for in any situation.

The COUNTIF formula is the most powerful and flexible way to find duplicates in Excel because it gives you exact counts and works in any version of Excel from 2007 onward. The formula counts how many times a specific value appears in a range. For duplicate detection, you use it in a helper column that counts each row's value against the entire column.

Here's how it works: in a blank column next to your data, enter the formula =COUNTIF($A$2:$A$100,A2) where $A$2:$A$100 is the full range of your data (adjust for your actual range) and A2 is the current row. The dollar signs lock the range while the row reference changes as you copy the formula down. Any cell where the result is greater than 1 is a duplicate. A result of 1 means the value appears only once; 2 means it appears exactly twice; and so on.

To make this more readable, wrap the COUNTIF in an IF statement: =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate",""). This produces the text "Duplicate" in every row where the value appears more than once and leaves the cell empty for unique values. You can then sort or filter by this helper column to group all duplicates together for review.

For finding duplicates across two columns — for instance, checking whether a name in column A also appears in a separate list in column D — use =COUNTIF($D$2:$D$100,A2). Any result greater than 0 means the value in A also exists somewhere in column D. This cross-list matching is how you'd check, for example, whether any email addresses in a new subscriber list already exist in your unsubscribe list.

Conditional Formatting's built-in duplicate highlighter is the fastest starting point when you just need to see which values repeat in a column. Select the range of cells you want to check — let's say A2:A200 — then go to the Home tab, click Conditional Formatting, choose Highlight Cell Rules, and select Duplicate Values. A dialog box opens with two dropdowns: the first lets you choose between 'Duplicate' and 'Unique,' and the second lets you pick the highlight color. Click OK and Excel immediately highlights every cell whose value appears more than once in the selected range.

One important detail: the duplicate rule compares values within the selected range only. If your list has 1,000 rows but you only selected the first 200, Excel won't catch duplicates outside your selection. Always select the full column or the complete data range before applying the rule. You can select the entire column A by clicking the column header, but be aware that empty cells at the bottom won't trigger false duplicates — Excel ignores truly empty cells in duplicate checks.

Conditional Formatting for duplicates is also available across multiple columns if you write a custom formula rule. Instead of using the Duplicate Values shortcut, select your entire data range, then create a new rule with a formula like =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. This highlights rows where both column A and column B match another row — a true row-level duplicate check rather than a single-column check.

This matters when you have data where, say, the same first name appears multiple times but with different last names, and you only want to flag entries where both the first and last name are an exact match. The COUNTIFS function is the key to building these multi-column custom rules.

Finding duplicate rows — where every column in the row matches another row exactly — requires a slightly different approach than single-column duplicate detection. Excel's built-in Conditional Formatting duplicate rule only works on individual columns, not entire rows. To identify row-level duplicates, you need to either use Power Query's 'Keep Duplicates' feature or create a helper column that concatenates all the relevant columns before running COUNTIF.

The concatenation approach is straightforward. In a blank helper column, write a formula like =A2&"|"&B2&"|"&C2 to combine the values from columns A, B, and C into a single text string, with a separator character (the pipe symbol is common) between each value to prevent false matches from adjacent values bleeding together. Then run COUNTIF on this helper column to identify rows where the combined string appears more than once. Any row where the COUNTIF result is greater than 1 is a true row-level duplicate.

Power Query's approach is cleaner for large datasets. Load your table into Power Query via Data → From Table/Range. In the Power Query editor, select all columns by clicking the first column header, then Shift-clicking the last. Then go to Home → Keep Rows → Keep Duplicates. This produces a table containing only the rows that are duplicated — a useful starting point for reviewing which records need cleanup before you decide how to handle them.

If you need to keep only the first occurrence of each duplicate row and remove the rest, the how to remove duplicates in excel guide covers the full workflow for that, including how to choose which occurrence to retain when the duplicates differ in non-key columns like timestamps or status fields.

Power Query is the most powerful method for finding duplicates in large datasets or when your duplicate detection needs to span multiple columns with complex matching logic. It's available in Excel 2016 and later (and as an add-in for Excel 2013), and it's designed specifically for data transformation at scale. Unlike formulas that recalculate every time your source data changes, Power Query transformations run on demand when you click Refresh — making it far more efficient for periodic data quality reviews on regularly updated datasets.

To use Power Query for duplicate detection, first convert your data range to a Table (Insert → Table, or Ctrl+T). Then click anywhere in the table and go to Data → From Table/Range to open the Power Query editor. In the editor, you'll see your data with column headers and a row for each entry. To find all duplicates, select the column or columns you want to check for duplicates, then go to Home → Keep Rows → Keep Duplicates. Power Query returns only the rows that have at least one other matching row — these are your duplicates.

If you want to highlight duplicates in your original data rather than extract them, add a custom column in Power Query. Create a merge that joins the table to itself on the key columns and counts matching rows. Any row with a match count greater than 1 is a duplicate. This approach scales to millions of rows and handles multi-column duplicate keys cleanly, which makes it the go-to method for enterprise data cleaning work where formula-based approaches become unwieldy. Saving your Power Query steps means you can refresh the same duplicate report every month without rebuilding it from scratch.

Understanding how to work efficiently with data is also part of broader how to use excel skills that span formatting, formulas, and data management. Duplicate detection is one piece of a larger data quality workflow that also includes how to highlight duplicates in excel for visual review before deciding whether to remove or keep flagged records.

One scenario that trips up beginners is finding duplicates when the data has leading or trailing spaces. Excel treats 'Smith ' (with a trailing space) as different from 'Smith' (without one), so COUNTIF won't flag them as duplicates even though they look identical on screen. The fix is to clean your data before checking for duplicates using the TRIM function, which removes extra spaces: =TRIM(A2) in a helper column, then copy and paste as values to replace the original data. After trimming, your duplicate detection will catch what visual inspection shows but formulas miss.

Case sensitivity is another subtlety. By default, COUNTIF is case-insensitive — it treats 'SMITH', 'Smith', and 'smith' as the same value. This is usually what you want, but if you need case-sensitive duplicate detection (for passwords, codes, or serial numbers where case matters), you'll need an array formula using EXACT: =SUM(--(EXACT(A2,$A$2:$A$100)))>1 returns TRUE if the exact case-matched value appears more than once. This approach is slower than COUNTIF but catches case differences that standard duplicate detection misses.

Excel's excel formulas ecosystem also includes UNIQUE (available in Excel 365 and Excel 2021), which extracts only the non-duplicated values from a range into a spill array. While UNIQUE doesn't directly flag duplicates, combining it with the original list using a COUNTIF tells you which values are in the original but not in the UNIQUE output — those are by definition the duplicates. This newer dynamic array approach is worth knowing if you're on a modern Excel version.

Before committing to any duplicate removal, it's worth spending time on the finding and review phase — especially in business contexts where deleting the wrong record can have downstream consequences. Two customer entries with the same email address might look like duplicates at first glance, but one might be an active account and the other an archived or test record. Blindly removing duplicates based solely on matching email addresses could inadvertently delete active customer data.

A practical review workflow: start by using COUNTIF to flag duplicates and add a helper column with the count. Sort by the helper column descending so all the duplicated rows appear at the top of the worksheet. Review each group of duplicates manually — or use Excel's grouping and subtotals to summarize what you find. In many real datasets, the duplicates fall into a few predictable categories: exact matches you can safely remove, near-duplicates that differ in one field (usually a timestamp, status, or version number), and false positives where the values look similar but represent different entities.

Once you've reviewed and decided which rows to remove, you have options. If you want to keep only the most recent entry, sort by date descending before running Remove Duplicates — the tool keeps the first occurrence in the current sort order, so sorting by date ensures the newest record is kept. If you want to keep the entry with the most data filled in, you'll need to inspect manually or write a more sophisticated macro or Power Query transformation that scores each duplicate group by completeness and keeps the winner.

For larger organizations, duplicate prevention is more valuable than duplicate cleanup after the fact. Data validation rules in Excel can prevent users from entering a value that already exists in a column. To set this up, select the input range, go to Data → Data Validation, choose 'Custom' under Allow, and enter =COUNTIF($A$1:$A$100,A1)=1 as the formula.

This prevents any entry that would create a duplicate, surfacing the problem at data entry time rather than during a quarterly cleanup cycle. You can add a custom error message through the Error Alert tab in the Data Validation dialog to explain clearly why the entry was rejected — something like 'This value already exists in the list' is far more helpful to end users than a generic validation error.

Excel's spreadsheet tools become especially powerful when you combine them systematically. Using excel spreadsheet best practices — consistent data formatting, named ranges, and structured tables — makes duplicate detection faster and more reliable across all five methods covered in this guide. Clean, consistently formatted data is always easier to deduplicate than messy imported data with mixed types and inconsistent spacing.