The fastest way to spot duplicates in Excel

Excel has a built-in tool called Conditional Formatting that highlights duplicate values in seconds. Select the column or range where you want to find duplicates, go to the Home tab, click Conditional Formatting, choose Highlight Cell Rules, then select Duplicate Values. Excel will shade every repeated entry in that range with color — usually red or yellow depending on your version.

If you need a list of just the duplicates rather than highlighted cells, use the Remove Duplicates feature on a copy of your data first. This tells you what values repeat, though it actually removes them from that copy, so always work on a duplicate file. Go to the Data tab, click Remove Duplicates, select your columns, and Excel will show you how many duplicate rows it found before deleting them.

For older versions of Excel or if you prefer a manual method that gives you more control, you can use a formula approach with COUNTIF to flag which rows contain values that appear more than once in your list.

Key Takeaways

  • Conditional Formatting is the fastest visual method — select your data, go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values, and duplicates appear highlighted.
  • The Remove Duplicates tool on the Data tab shows you how many duplicate rows exist, but it deletes them from your working file, so always test on a copy first.
  • A COUNTIF formula like =COUNTIF($A$2:$A$100,A2)>1 will mark rows containing duplicates without removing anything, giving you a chance to review before deleting.
  • When you have multiple columns, decide whether you want to find duplicates within a single column or duplicates where entire rows match — the tool you choose depends on which one matters for your data.

Using Conditional Formatting to highlight duplicates

Conditional Formatting is the method most people reach for because it works in real time and requires no setup. Click the column header or drag to select the range containing your data. If your data is in column A from row 2 to row 500, click on A2 and drag down to A500, or click A2 and then Shift+Click A500.

Once selected, go to the Home tab at the top of the ribbon. Find the Conditional Formatting button — it usually sits near the right side of the Home tab. Click it, then hover over Highlight Cell Rules. A submenu appears with options including Duplicate Values. Click that option. A dialog box opens asking which cells to format; the range you selected should already be filled in. Click OK, and within a second, every duplicate value in that range turns a highlight color.

The color depends on your Excel version and theme. You can change it by going back to Conditional Formatting, choosing Manage Rules, selecting the rule you just created, and clicking Edit Rule to pick a different format. This method works for single columns, but if you want to find rows where multiple columns match, you will need a different approach.

Finding duplicates across multiple columns

When your data has several columns and you need to know if an entire row repeats — not just one column — Conditional Formatting alone will not work the way you need. Instead, add a helper column with a COUNTIFS formula that checks whether the combination of values in those columns appears more than once.

In a blank column next to your data, write a formula like =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2). This counts how many times the combination of values in columns A, B, and C appears in your data. If the result is greater than 1, that row is a duplicate. Copy this formula down for every row. Then use Conditional Formatting on this helper column to highlight any cell with a value greater than 1.

After you have identified the duplicates, you can delete the helper column and remove the duplicate rows manually, or use Remove Duplicates on the original data now that you know what to expect. This method gives you control — you see exactly which rows match before anything is deleted.

Using the Remove Duplicates tool

The Remove Duplicates feature lives on the Data tab and works quickly, but it permanently deletes rows from your file. Always save a backup or work on a copy of your spreadsheet before using it. Select your entire data range including headers, go to the Data tab, and click Remove Duplicates.

A dialog box appears listing all your columns. By default, all columns are checked, meaning Excel will only consider a row a duplicate if every single column matches another row exactly. If you want to find duplicates based on only certain columns — for example, only columns A and B — uncheck the other columns. Click OK.

Excel then shows a message telling you how many duplicate rows it found and removed. The duplicates are gone from your file. If you realize you made a mistake, press Ctrl+Z when ready to undo. If you closed the file, the duplicates are permanently deleted, which is why working on a copy is essential.

The COUNTIF formula method for more control

If you want to mark duplicates without deleting anything, a COUNTIF formula gives you that safety. In a blank column next to your data, type =COUNTIF($A$2:$A$100,A2). The dollar signs lock the range so it does not change when you copy the formula down. This formula counts how many times the value in A2 appears anywhere in the range A2:A100.

Copy this formula down for every row in your dataset. Any cell showing a number greater than 1 means that value appears more than once. You can then sort or filter by this column to see all duplicates grouped together. Once you have reviewed them, you can delete the rows you want to remove, then delete the helper column.

This method is slower than Conditional Formatting for just spotting duplicates visually, but it is safer if you are not sure which duplicates you want to keep. Some datasets have legitimate repeats — the same customer ordering twice, for example — and this approach lets you decide row by row rather than deleting everything at once.

Dealing with near-duplicates and typos

Excel's duplicate tools find exact matches only. If one entry says "John Smith" and another says "john smith" or "Jon Smith," Excel will not flag them as duplicates. For data with typos or inconsistent capitalization, you have to clean it first.

Use Find and Replace to standardize capitalization. Press Ctrl+H to open Find and Replace, search for lowercase versions of common entries, and replace them with the correct version. For typos, there is no automatic fix — you have to spot them manually or use a tool outside Excel. Some people copy their data into Google Sheets and use the FUZZY MATCH function, which finds near-duplicates, then bring the results back to Excel.

If your data has leading or trailing spaces — a space before or after a name, for example — Excel will treat "John " and "John" as different values. Use the TRIM function in a helper column to remove extra spaces, then run your duplicate check on the trimmed data.

Checking for duplicates before you import data

If you are about to bring data into Excel from another source — a database export, a CSV file, or a form response list — check for duplicates before you start working with it. Open the file in Excel, select all the data, and run Conditional Formatting or Remove Duplicates right away. This prevents duplicate entries from spreading through your analysis or reports.

Some data sources create duplicates automatically. If you export the same list twice and combine them, you will have every entry twice. If you import form responses over several days, people might submit twice by accident. Checking when ready after import saves you from discovering duplicates halfway through a project.

Frequently Asked Questions

Does Excel's duplicate finder work on numbers and text the same way?

Yes. Conditional Formatting and Remove Duplicates treat numbers and text identically — they look for exact matches. The only difference is that Excel may store numbers differently depending on formatting (as currency, percentage, or plain number), so two cells that look the same might not match if one is formatted as text and the other as a number. Check your column formatting if duplicates are not being found.

What if I only want to find duplicates in specific rows, not the whole column?

Select only the rows you want to check instead of the entire column. Click on the first cell you want to include, then Shift+Click on the last cell. This creates a smaller range. Then explore Conditional Formatting or Remove Duplicates to just that selection. Excel will only look within the range you chose.

Can I undo Remove Duplicates if I realize I deleted the wrong rows?

Only if you have not closed the file. Press Ctrl+Z when ready after running Remove Duplicates to undo it. If you have closed and reopened the file, the undo history is gone and the deletions are permanent. This is why saving a backup copy before using Remove Duplicates is essential.

How do I find duplicates if the same value appears in different columns?

Conditional Formatting only checks within the range you select, so if you select columns A and B together, it will find any value that repeats anywhere in that combined range. If you want to know whether a value from column A appears in column B, use a COUNTIF formula like =COUNTIF($B$2:$B$100,A2) in a helper column. Any result greater than 0 means that value exists in column B.

Why does Excel say it found duplicates but I do not see them highlighted?

The highlight color might be too subtle for your screen or theme. Go back to Conditional Formatting, click Manage Rules, select the duplicate rule, and click Edit Rule. Change the format to a brighter color or add bold formatting so the duplicates stand out more. You can also sort your data by the helper column to group duplicates together, which makes them easier to spot.