How to Find Duplicate Entries in Excel with Formulas

To find duplicate entries in Excel, select the cells you want to check and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel will mark repeated values. For more control, use a COUNTIF formula to label or count repeats, or use COUNTIFS to compare complete rows. Check duplicates before deleting anything, because two matching values are not always errors.

The directions below are for Excel for Microsoft 365 on Windows. The conditional-formatting and formula methods also work in recent Excel versions; some newer functions are not needed here. Excel for Mac and Excel for the web have similar options, with interface details noted where useful.

What You’ll Need

An Excel workbook with the list you want to check. Save a copy first if you may remove records.
A clear idea of what counts as a duplicate: an identical value in one column, or a row with the same values across several columns.
For the formula method, a spare column beside your data where you can display a duplicate flag or count.

Step-by-Step

Decide what you are comparing. If each value in a column should be unique, such as an email address or invoice number, check that column alone. If a record is identified by several fields, such as first name, last name, and date of birth, compare those fields together. Comparing a whole table as one selection checks whether a value appears more than once anywhere in the selection; it does not establish that two entire rows match.
Prepare the range. Click the first data cell and select the values to check, excluding the header. For example, if column A has the heading Email in A1 and entries in A2:A100, select A2:A100. Excluding the heading prevents Excel from including it in the comparison. If the list will grow, you can select the current data or convert it to a table with Insert > Table so the list is easier to manage as rows are added.
Highlight matching values with Conditional Formatting. With the range selected, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a highlight style, and click OK. Excel marks every value that occurs at least twice in the selected range, including the first occurrence and later repeats. The rule is a visual check; it does not change the data.
Review the highlighted results. Look at the surrounding fields and decide whether each match is genuinely a duplicate. Two people may share a name, or two orders may have the same amount, without being the same record. Filter or sort the list to bring highlighted entries together if that makes review easier. When you are done, you can remove the rule through Home > Conditional Formatting > Clear Rules, choosing whether to clear it from the selected cells or the whole worksheet.
Use COUNTIF to label duplicates in one column. If the entries are in A2:A100, place a formula in B2 and fill it down through B100. The formula returns TRUE for every value that appears at least twice in the checked list, and FALSE for a value that appears only once. With the list in A2:A100 and the formula beside A2, enter =COUNTIF($A$2:$A$100,A2)>1. The dollar signs keep the checked range fixed as you fill the formula downward. If the first entry is in another column or row, adjust the references accordingly.
Exclude empty cells if needed. A blank may be treated as a repeated value by a counting check, and blank-looking cells can make the result confusing. With values in A2:A100 and the formula going in B2, use this version to return TRUE only when A2 is nonblank and its value occurs more than once: =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). Fill it down the helper column. This check excludes genuinely empty cells; a cell containing a space is not empty, so clean such entries if they should count as blank.
Compare several fields to find repeated records. Suppose columns A, B, and C contain first name, last name, and date, and the data runs from row 2 to row 100. In D2, enter the following formula and fill it down to D100. It returns TRUE when that exact combination occurs more than once: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1. Change the columns and row limits to match your record. This checks the combination of fields, not whether every other column in the row is identical.
Count how many times an entry appears. A count can be more informative than a TRUE/FALSE flag. With values in A2:A100 and the formula placed in B2 beside A2, use =COUNTIF($A$2:$A$100,A2) and fill down. A result of 1 means the value occurs once; 2 or more means it is repeated. You can filter the helper column to show counts greater than 1, or sort by it to group repeated values. Keep the helper column until you have verified the records.
Remove duplicates only after checking them. Make a backup, select the data, then choose Data > Remove Duplicates. In the dialog, confirm whether your selection has headers and check only the columns that define a duplicate record. If you select just one identifying field, Excel may remove rows that share that field even when other columns differ. Click OK only when the chosen comparison matches your intention. Excel keeps the first occurrence and removes later matching rows, so check which row comes first before proceeding.

On Excel for Mac, the ribbon command is also under Home > Conditional Formatting, though the dialog layout may vary slightly; the formulas and menus are generally the same. In Excel for the web, use the Home tab’s Conditional Formatting options or enter the formulas in cells. To undo an accidental removal immediately, use Ctrl+Z on Windows or Command+Z on Mac, but do not rely on undo as a substitute for saving a copy.

Common Mistakes

Selecting the wrong range: Including a heading, unrelated columns, or an incomplete list can produce misleading highlights or counts. Check the range shown in the formula and ensure it covers every intended record.
Assuming a match means a duplicate record: Conditional Formatting compares cell values in the selected area. It does not know whether two rows represent the same person, order, or transaction. Use a combination of fields that identifies the record properly.
Deleting before reviewing: Remove Duplicates permanently changes the visible data and retains the first matching row. Verify the selected columns and keep a backup before clicking OK.
Missing differences that are hard to see: Leading or trailing spaces, inconsistent punctuation, and different spellings may make entries appear alike while Excel sees different text. Conversely, COUNTIF is not case-sensitive, so entries that differ only by letter case are generally treated as matches.
Forgetting to fill a formula down: A formula in the first helper cell does not automatically label every row in a normal range. Drag the fill handle, copy and paste, or double-click the fill handle to copy it through adjacent data.

Tips

Use Conditional Formatting for a quick visual scan, and a helper formula when you need results you can filter, sort, audit, or reuse.
For a growing list, use an Excel table and put the helper formula in its new column. Excel typically copies a calculated-column formula to new table rows automatically; verify it when you add records.
Keep an original ID or source column when reviewing possible matches. It helps you distinguish genuine duplicate records from separate records that happen to share a name or value.
To handle extra spaces, consider cleaning a separate copy of the data with TRIM before comparing it. Do not overwrite the original until you have checked that cleaning did not change meaningful content.
When using Remove Duplicates, check the status message Excel displays afterward. It reports how many duplicate values were removed and how many unique values remain, which gives you a quick chance to spot an unexpected result.

Troubleshooting

Nothing is highlighted. Confirm that the rule covers the cells you intended and that the values really match. Extra spaces or different punctuation can prevent a match. Select the range again and recreate the rule if its “Applies to” range is too short; manage rules through Home > Conditional Formatting > Manage Rules.

The formula shows FALSE for an apparent duplicate. Check that the formula’s fixed range includes both occurrences and that the row reference points to the current row. In a multi-field check, make sure each COUNTIFS pair uses the correct column and corresponding value. Text that looks identical may contain spaces or nonprinting characters, so compare the underlying cell contents or clean a copy before judging the result.

Every row appears to be a duplicate. Make sure the formula range is fixed with dollar signs, such as $A$2:$A$100, but its criterion points to the current row, such as A2. If the criterion was accidentally fixed as $A$2, every filled formula may keep checking the first entry instead of its own row.

Remove Duplicates removes too many rows. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if the action is still available. Otherwise, restore the saved copy. Run the command again only after selecting the correct columns: checking fewer columns can define more rows as duplicates, while checking more columns requires a closer match.

A blank-looking entry is flagged or missed. Click the cell and inspect the formula bar. It may contain a space, an apostrophe, or a formula returning an empty string rather than being truly empty. Decide how those cases should be treated, then clean or exclude them consistently.

Frequently Asked Questions

Does Excel have a built-in way to find duplicates?
Yes. Select the cells and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. It highlights repeated values without deleting or changing them.

Does Conditional Formatting highlight the first occurrence too?
Yes. The Duplicate Values rule marks every instance of a value that appears more than once in the selected range. Use a helper formula with a progressively expanding range if you want to flag only later repeats.

How do I mark only the second and later occurrences?
With entries in A2:A100 and the formula in B2, enter =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) and fill down. The first appearance returns FALSE; a later repeat returns TRUE. This example ignores blank cells.

Can Excel find duplicate rows across multiple columns?
Yes. Use COUNTIFS with one range-and-criterion pair for each field that defines a record, or use Remove Duplicates and select those same identifying columns. A match across selected fields means those fields agree; it does not necessarily mean every column in the row agrees.

Are duplicates case-sensitive?
Standard duplicate highlighting and COUNTIF comparisons are generally not case-sensitive, so text such as “abc” and “ABC” is treated as the same value. If capitalization matters to your data, use a case-sensitive comparison method or review those cases separately before removing anything.

Can I remove duplicates without losing the original list?
Yes. Copy the worksheet or workbook first, or use the highlight and formula methods to review records without deleting them. Remove Duplicates changes the selected data, so keep the untouched copy until you have confirmed the result.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.