Find Duplicates in Excel: A Practical Step-by-Step Guide

To find duplicate values in Excel, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. To check repeated records across multiple columns or mark matches with a formula, use the methods below. Before changing or deleting data, save a copy of your workbook and decide what counts as a duplicate in your list.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows.
A clear idea of which cells or columns define a duplicate. A repeated email address may be a duplicate by itself; a repeated customer record might require the same name and email together.
Optional: a spare column if you want to label matching rows with a formula, and a backup copy if you plan to remove records.

Step-by-Step

Choose what you are comparing. Decide whether you want to find repeated entries in one column, such as invoice numbers, or repeated combinations across several columns, such as a product code and location. This decision matters: two records with the same name but different email addresses may be valid separate records. Make a note of the columns that need to match before you apply a rule.
Check the shape of your list. Look at the headings and data boundaries. A single header row should not be included in the comparison range. Also check whether there are completely blank rows inside the list, because they can make selecting the intended range less straightforward. If your data is in an Excel table, you can select the relevant table column; otherwise, select the cells containing the data, such as A2:A100, rather than the whole worksheet column.
Highlight duplicates in one column. Select the data cells, excluding the heading. On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style, and click OK. Excel highlights every value that appears more than once in the selected range. The default formatting is only a visual flag; it does not change the cell contents or remove anything.
Review what Excel highlighted. Scan the colored cells and check the surrounding rows before taking action. Conditional formatting marks every instance of a repeated value, including its first appearance. For example, if the same order number appears three times, all three matching cells are highlighted. If you expected only later copies to be marked, use the helper formula in the next step instead.
Use a formula to label duplicate values. With the values you want to compare in A2:A100 and a helper column available, enter this in B2. The formula returns TRUE when the value in A2 appears more than once in the full range, and FALSE otherwise. Copy or fill it down alongside the list. For a repeated value, the result is TRUE for each occurrence, including the first one.

With the values in A2:A100 and the formula entered in B2, use =COUNTIF($A$2:$A$100,A2)>1. For example, if A2:A100 contains the order number 104 twice and the other values only once, the formula returns TRUE for both rows containing 104 and FALSE for a value that appears once.

Mark only the second and later occurrences. Sometimes you want to preserve the first record and flag only subsequent copies. With the list in A2:A100 and the helper formula starting in B2, enter the following and fill it down. The expanding end of the range counts how often the current value has appeared from the first data row through the current row. The first appearance returns FALSE; later appearances return TRUE.

With values in A2:A100 and the formula entered in B2, use =COUNTIF($A$2:A2,A2)>1. If A2 contains 104 for the first time and A8 contains 104 again, B2 returns FALSE and B8 returns TRUE.

Check duplicates across multiple columns. If a record is only a duplicate when two fields match, use COUNTIFS in a helper column. For example, compare both the customer ID in column A and the date in column B. Fill the formula down; TRUE marks records where that same pair occurs more than once. Add another range-and-cell pair for each additional field that must match.

With customer IDs in A2:A100, dates in B2:B100, and the formula entered in C2, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1. If the same ID and date appear in two rows, both rows return TRUE, even if another field, such as a note in column C, differs.

Filter or sort to inspect the results. If you used a helper column, turn on filters with Data > Filter, then filter that column to show TRUE. Review the whole row for each match and decide which record to keep. You can also sort by the helper column to group the matches, but sorting only one column can disconnect values from their associated records. Select the full table before sorting.
Remove duplicates only after checking them. If you are certain you want to delete repeated records, make a backup first. Select the data range or a cell in the list, then choose Data > Remove Duplicates. In the dialog, confirm whether your data has headers and select the columns Excel should use to identify duplicates. Click OK only after checking those choices. Excel keeps the first matching record and removes later matching rows, then reports how many it removed. This changes the data, unlike highlighting or formulas.
Clear temporary checks when finished. To remove a conditional formatting rule, select the checked cells and choose Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from the selected cells or the whole sheet. If you used a helper column, keep it as an audit marker or delete it after you have finished reviewing. Clearing the color or helper formula does not undo any rows already removed.

Common Mistakes

Selecting the heading with the data: Including a header can cause it to be treated as a value in the range. Start at the first actual record, not the column label. If you have multiple heading rows, select the data below all of them.

Checking the wrong fields: A duplicate in one column is not always a duplicate record. Removing duplicates based only on a name, for example, could delete two different people with the same name. Conversely, checking every column can miss records that share the important ID but differ in a note or timestamp. Choose the comparison fields to match the purpose of the data.

Assuming the first copy is unmarked: The Duplicate Values rule and the full-range COUNTIF formula flag all occurrences, not just the extra copies. Use the running COUNTIF formula if you need to identify only the second and later appearances.

Deleting before confirming the dialog: Remove Duplicates uses the selected columns to decide which rows match and generally retains the first occurrence. If the wrong columns are checked, valid records may be removed. Work on a copy and inspect the selected column list before confirming.

Ignoring blanks and inconsistent text: Empty cells, spaces at the start or end, and different punctuation can affect what appears to be a match. A blank cell is not the same as a cell containing a space. Clean or standardize the data when those differences are accidental, but do not alter values that are meaningfully distinct.

Tips

Convert a contiguous list into an Excel table with Insert > Table if you expect to add rows regularly. Tables make it easier to select a column and keep related fields together. When you extend a formula beside a table, check that it applies to new rows as intended; review the results after adding data rather than assuming an older fixed range includes every new entry.

Keep an untouched copy of the original data before cleaning. A separate worksheet or workbook lets you compare the original records with the cleaned list and recover information if you later discover that the duplicate criteria were too broad. For important lists, add a note describing which columns were used to define duplicates.

For a quick visual check, use conditional formatting. For repeatable checks, use a helper formula because TRUE and FALSE results can be filtered, counted, or reviewed alongside each record. You can combine the approaches: highlight a column first, then use a helper formula to make the matches easier to filter and explain to someone else.

Excel’s standard duplicate comparisons are generally not case-sensitive, so ABC and abc may be treated as the same value. If capitalization carries meaning in your data, do not rely on the basic duplicate rule alone; use a carefully designed case-sensitive check or review those values separately. Also be aware that comparison behavior can be affected by data type: a number stored as text may not behave like the same number stored as a numeric value.

In Excel for Mac, the Conditional Formatting and Remove Duplicates features are available, though the ribbon layout can differ slightly from Windows. In Excel for the web, commands may be arranged differently and some desktop controls may not be present in the same place. If you cannot find a command, use Excel’s search box to look for the command name, and verify the selected range before applying it.

Troubleshooting

No cells are highlighted: Confirm that you selected the cells containing the data, not just the heading or an empty area. Reopen Home > Conditional Formatting > Manage Rules and check that the rule applies to the intended range. Also confirm that duplicate values actually occur within the selected range; a matching value elsewhere on the worksheet is not included unless it is part of the range.

Values that look identical are not flagged: Compare the actual contents, not only how they display. One cell may contain an extra space, a nonbreaking space copied from a website, punctuation, or a number stored as text. You can inspect a cell in the formula bar and standardize the source values before checking again. Avoid bulk cleanup until you know which differences are accidental, since punctuation or spaces can be meaningful in IDs.

Too many values are marked: Check that the selected range does not include unrelated columns or extra cells below the list. If every blank appears to be involved, narrow the range to populated records. With formulas, confirm that the absolute range is correct and that each row reference points to the current row; a misplaced dollar sign can make every formula check the same cell.

The formula shows an error or does not update: Make sure the formula starts with an equals sign and that the range references cover the intended rows. The examples use commas between arguments, which is the usual setting in English-language Excel; some regional settings use semicolons. If Excel displays the formula as text, change the cell format to General, then re-enter the formula. Press F9 or recalculate if calculation is set to Manual.

Remove Duplicates removed unexpected records: If the action just happened, use Ctrl+Z on Windows to undo it, then verify the selected range, header setting, and checked columns. On Mac, use Command+Z. If you have since made other changes or closed the file, restore your saved copy or use version history if it is available. The safest remedy is to run the cleanup on a duplicate of the worksheet first.

Frequently Asked Questions

Does Conditional Formatting delete duplicate values?
No. The Duplicate Values rule only changes how matching cells look. It leaves the contents in place, so you can review the results before deciding whether to edit or remove anything.

Does Excel highlight the first occurrence too?
Yes. The Duplicate Values rule highlights all instances in the selected range. The COUNTIF formula that checks the full range does the same. Use the running COUNTIF formula in this guide to flag only later occurrences.

Can I find duplicate rows instead of duplicate cells?
Yes. Decide which fields define a repeated record, then use COUNTIFS with those columns as comparison pairs. If you use Data > Remove Duplicates, select the same defining columns in its dialog. Do not include unrelated fields unless they should be part of the match.

Will Remove Duplicates keep the original row?
Excel keeps the first occurrence it encounters in the selected data and removes later matches according to the columns you selected. If the order matters, sort or arrange the records so the version you want to keep comes first, then work on a backup.

Can I find duplicates across two different worksheets?
The built-in Duplicate Values rule checks the selected range, so it does not directly compare two separate worksheets as one list. You can combine the relevant values into a comparison range or use a formula that explicitly refers to the other sheet. Check the formula references carefully, especially if sheet names contain spaces.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.