Find Duplicate Entries in an Excel Column

How-to basics · Updated November 20, 2026

To find duplicate entries in an Excel column, select the values and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel will highlight values that appear more than once. For more control, use a COUNTIF formula to flag duplicates, ignore blank cells, or highlight only repeated occurrences after the first. These methods help you inspect repeated data without deleting anything.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows.
  • A column of values to check, ideally with a heading in the first row and data beneath it.
  • Optional: an empty helper column if you want a written duplicate label or a filterable TRUE/FALSE result.

The steps below use column A as an example, with a heading in A1 and entries in A2:A100. Replace those references with the range in your sheet. If your data is in an Excel Table, the conditional-formatting method still works; select the relevant table column’s data cells rather than its heading.

Step-by-Step

  1. Check which cells contain the data. Identify the first and last rows of the list, and exclude the heading. For example, if the heading is in A1 and the last value is in A100, the range to check is A2:A100. Including a heading can cause Excel to treat a repeated heading as a duplicate, and including lots of unused cells can make blank handling confusing.

  2. Highlight duplicate values with Conditional Formatting. Select A2:A100. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format from the list, and select OK. Excel highlights every cell whose value occurs more than once in the selected range, including the first occurrence and each later one. The built-in rule is the quickest visual check and does not alter the data.

    To check a different range, select that range before applying the rule. To change the highlight later, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. Check that the rule applies to the intended cells. In Excel for Mac, the Conditional Formatting commands are also on the Home tab; the dialog layout may differ slightly. Excel for the web has similar conditional-formatting options, though some rule-management controls may be arranged differently.

  3. Use a formula if blank cells should not count. Conditional Formatting’s built-in Duplicate Values rule can also highlight repeated blanks. If your column has empty cells and you only want to mark repeated nonblank entries, select A2:A100 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With values in A2:A100, the selected range starting at A2, and the heading in A1, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). Choose Format to pick a highlight, select OK, and select OK again if the rule dialog remains open. The relative A2 reference adjusts for each row; the dollar signs keep the checked range fixed. This marks every nonblank value that occurs at least twice.

  4. Make a filterable duplicate flag in a helper column. If you want to sort or filter the results, enter a formula beside the first data row and fill it down. With the list in A2:A100, enter =COUNTIF($A$2:$A$100,A2)>1 in B2, then copy or fill the formula through B100. TRUE means the value in that row appears at least twice in the checked range; FALSE means it appears once. The same value gets TRUE on all its matching rows. You can then choose Data > Filter and use the arrow in the helper-column heading to show TRUE rows. Add a heading such as Duplicate? in B1 so the filter is easy to identify.

  5. Flag only later occurrences when the first copy should stay unmarked. Sometimes the first appearance is the original record and only subsequent repeats need review. With entries in A2:A100, select the cells in B2:B100 and enter =COUNTIF($A$2:A2,A2)>1 in B2, then fill down. The expanding range counts from A2 to the current row: the first occurrence returns FALSE, and each later occurrence returns TRUE. For example, if A2:A5 contains Pine, Oak, Pine, Pine, the formula in B2:B5 returns FALSE, FALSE, TRUE, TRUE. This is useful for identifying rows to review while preserving the first entry.

  6. Inspect the matches rather than deleting them immediately. With a helper column, filter TRUE to gather the flagged rows. With conditional formatting, look for cells sharing the highlight color. Compare relevant fields such as dates, account numbers, or addresses before deciding that two rows are genuinely redundant. A repeated name might represent two different people, and a repeated product code may be expected in a transaction list. Highlighting identifies repeated values in the checked column; it does not prove that entire records are identical.

  7. Remove duplicates only after you have confirmed what should be removed. If you decide that duplicate rows should be deleted, save a copy of the workbook first. Select the complete data range, including all columns that belong to each record, then choose Data > Remove Duplicates. In the dialog, select the columns Excel should compare and confirm. Excel keeps one matching record and removes the other matching rows. If you select only the name column in a multi-column table, Excel may remove records that share a name even though other details differ. Conditional formatting and formulas are safer for reviewing because they do not delete data.

  8. Update the check when the list grows. A formula limited to A2:A100 will not include a new entry in A101. Extend the range in the formula and the conditional-formatting rule, or convert the list to a Table with Insert > Table so it is easier to maintain an expanding dataset. After adding entries, verify the rule’s Applies to range through Home > Conditional Formatting > Manage Rules. Always check that formulas and formatting cover the same rows.

Common Mistakes

Including the heading: If you select A1:A100, the column heading is part of the check. Start at the first data row instead. Assuming highlighted rows are duplicate records: the rule compares values in the selected column, not every field in each row. Confirm the other columns before removing a record.

Using a formula with the wrong starting row: if your selection starts in A2, use A2 as the formula’s relative reference. A mismatch can make the highlight appear one row off. Forgetting blanks: an empty cell may be counted as a repeated value. Use the AND version above when blanks should be ignored. Deleting too soon: Remove Duplicates changes the data and may remove entire rows. Review, save a copy, and choose comparison columns carefully first.

Also remember that COUNTIF is not case-sensitive: Excel treats entries such as North and NORTH as equal for this check. Extra spaces can have the opposite effect: “North” and “North ” may be treated as different values. If the result looks surprising, inspect the underlying cell contents and clean inconsistent text before relying on the duplicate list.

Tips

Use Conditional Formatting when you need a quick visual scan, and a helper column when you need to filter, sort, or keep a record of which rows were flagged. You can use both at once: the formula column makes matching rows easy to filter, while a color helps you spot them in context.

When checking a long list, consider converting the data to a Table with Insert > Table and confirming that My table has headers is selected. Tables make lists easier to filter and expand when new rows are added. If you use a formula helper column in a Table, Excel generally fills the formula down the column as new table rows are added; still confirm the formula’s comparison range includes all relevant entries.

For data imported from websites or other systems, look for inconsistent spaces, punctuation, and number formats. For instance, an ID stored as text may not behave like the same-looking ID stored as a number. Keep a copy of the original values before cleaning them, especially if formatting or leading zeros matter. If duplicate detection is for a particular business rule, define what makes a record a duplicate first: one matching email address is different from a match across name, date, and reference number.

Troubleshooting

Nothing is highlighted, but you expect matches. Check that the selected range includes all relevant rows and that the rule’s Applies to range is correct. Confirm that the values really match: trailing spaces, hidden characters, different punctuation, or numbers stored as text can make entries differ. The built-in rule and COUNTIF do not distinguish uppercase from lowercase, so capitalization alone should not explain a missing match.

Blank cells are highlighted. Use the formula-based rule that checks A2<>"", making sure A2 matches the first cell in your selected range. A cell that looks empty may contain a space or a formula returning an empty string; inspect it if it continues to be flagged.

The formula appears as text or shows an error. Make sure the cell is not formatted as Text, and that the formula starts with an equals sign. If it was entered while the cell was formatted as Text, change the format to General and re-enter the formula. Excel installations that use a different regional list separator may require semicolons instead of commas in formulas; use the separator your Excel expects.

The highlight is on the wrong row. In a formula-based rule, the relative reference must correspond to the first selected cell. For a selection beginning at A2, use A2 in the formula, not A1. Also inspect the Applies to range in Manage Rules to find an accidental offset.

A filter does not show the expected matches. Confirm the helper formula was filled to every data row, then check that the helper column contains TRUE/FALSE results rather than text. Clear any existing filters that may be restricting the data, and verify that the filter includes the helper-column heading and all rows.

Frequently Asked Questions

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

Can I find duplicates without highlighting them?
Yes. Put a COUNTIF formula in a helper column and fill it down. TRUE identifies a value that appears more than once in the range; filtering the helper column lets you view those rows together.

How do I find duplicates but ignore blank cells?
Use a conditional-formatting formula that combines a nonblank check with COUNTIF, such as the AND formula in the steps. Match the formula’s first-row reference and comparison range to your selected data.

Does Excel treat uppercase and lowercase entries as different?
No. The Duplicate Values rule and COUNTIF are case-insensitive, so entries that differ only by letter case are treated as the same value. If case must matter, you need a different, case-sensitive checking method.

Can I highlight only the second and later copies?
Yes. Use the running COUNTIF formula in a helper column. The first appearance returns FALSE, while later occurrences return TRUE, so you can filter or format just the repeats.

Will finding duplicates delete my data?
No. Conditional Formatting and the formulas above only identify matches. The separate Data > Remove Duplicates command deletes rows, so use it only after reviewing the results and saving a backup.

Related

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.