Identify Duplicate Values in Excel: A Step-by-Step Guide

To identify duplicate values in Excel, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel highlights repeated entries in that selection. For more control, use a COUNTIF formula to mark duplicates or filter a helper column to see them together. Before starting, decide whether you want to flag every copy of a value or only copies after its first appearance.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows. The steps also work in recent desktop versions of Excel, though some labels can vary slightly.
A clear idea of the range or column to check. For example, a list of email addresses in A2:A500.
Optionally, an empty column beside the data if you want to count or filter duplicates using a formula.

It is usually safest to make a copy of the workbook before cleaning or deleting records. Highlighting duplicates does not change the underlying values; removing them does.

Step-by-Step

Choose what counts as a duplicate. A duplicate may mean the same value appears more than once in one column, such as a repeated customer ID, or that an entire row is repeated. The methods below check values within one selected range or column. If you need to compare several fields together, first decide which fields form a unique record—for instance, first name plus last name, or product code plus date. Checking just one field could flag legitimate entries that share that field.

Also decide whether capitalization should matter. Excel’s built-in duplicate highlighting and COUNTIF comparisons are generally not case-sensitive, so “West” and “WEST” count as the same value. Spaces, however, can make entries different.
Select the range to inspect. Click and drag over the cells, excluding the header if there is one. For a column with a heading in A1 and values below it, select A2:A100 rather than including the heading. If the data is an Excel Table, select the relevant cells in its column. Limiting the selection keeps headers and unrelated data from being marked.

For a long, continuous list, click the first data cell and use Ctrl+Shift+Down on Windows to extend the selection to the end of the contiguous block. On Mac, use Command+Shift+Down. Check the selected range before proceeding; blank rows can stop this shortcut early, and adjacent data can sometimes make the selection larger than intended.
Highlight duplicates with Conditional Formatting. With the range selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a highlight style from the list, and click OK. Excel applies the formatting to each value that occurs more than once within the selected range.

This is the quickest visual check and does not require a helper column. To remove the highlighting later, select the same range and use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. If the range is part of a Table, check that the formatting applies to the intended cells after adding new rows.
Use a formula when you want a clear TRUE/FALSE result. Put a heading such as Duplicate? in an empty column beside the list. With the values in A2:A6 and the first result cell in B2, enter =COUNTIF($A$2:$A$6,A2)>1. In this example, A2 and A4 contain North, A3 and A6 contain South, and A5 contains West. Filled down, the formula returns TRUE for both North entries and both South entries, and FALSE for West.

The dollar signs keep the range fixed as you copy the formula down, while A2 changes to A3, A4, and so on. To fill the formula through the rest of the selected cells, use Ctrl+D on Windows or Command+D on Mac. You can also drag the fill handle, the small square at the bottom-right corner of the selected cell.
Mark only later occurrences if that is more useful. Sometimes you want the first appearance to remain unmarked and identify only the repeats that follow it. With the same layout—values in A2:A6 and the formula starting in B2—use =COUNTIF($A$2:A2,A2)>1. In the North, South, North, West, South example, the first North and first South return FALSE; the second North and second South return TRUE; West returns FALSE.

Here the range grows as the formula is filled down: it begins at A2 and ends at the current row. That running count is what distinguishes a later occurrence from the first. Keep the first cell reference in the formula matched to the first row of your data; if your data starts on row 5, adjust both A2 references to A5 before filling down.
Exclude empty cells when using a formula. If the list contains blanks, you may prefer not to have empty cells counted or marked. With values in A2:A6 and the check beginning in B2, enter =AND(A2<>"",COUNTIF($A$2:$A$6,A2)>1). In the example list with North and South each appearing twice, this returns TRUE for those four populated duplicate cells, FALSE for West, and FALSE for a blank cell.

Fill the formula down the helper column as before. The test for a nonblank value prevents blank rows from being treated as potential matches. This formula still checks the whole stated range, so change A2:A6 to the full data range you intend to examine.
Filter the helper column to review the matches. Click a cell in your data, then choose Data > Filter. Use the filter arrow in the helper-column heading and select TRUE to show duplicate rows, or FALSE to see the other rows. Filtering hides rows temporarily; it does not delete them. Clear the filter when you have finished reviewing the records.

If you used Conditional Formatting rather than a formula, you can sort by color from the column’s filter arrow when that option is available for the selected range. For a straightforward review, a formula helper column is often easier to filter and audit.

Common Mistakes

Including a heading in the checked range. A header can be highlighted if its text happens to appear in the data. Select the values only, or ensure a formula’s range begins at the first data row.
Checking the wrong scope. Conditional Formatting compares cells within the selected range. If you select only part of a list, a value repeated outside that selection will not be detected. Select the full intended list, including all relevant rows.
Assuming similar-looking text is identical. Leading or trailing spaces, nonbreaking spaces, and hidden characters can prevent two entries from matching. “Maple” and “Maple ” may look alike but contain different text.
Confusing repeated values with duplicate records. Two people can share a surname or two orders can have the same date without being duplicate records. Check the right combination of fields before removing anything.
Deleting instead of identifying. Conditional Formatting and formulas only help you find matches. Data > Remove Duplicates permanently removes rows from the selected data and can remove information you meant to keep. Review results and save a copy before using it.
Filling a formula into the wrong rows. A formula that starts below or above the first data row can compare the wrong cells. Confirm the first formula references the first record and then inspect a few results before relying on the whole column.

Tips

Use a helper column for repeatable checks. Conditional Formatting is quick for a one-time scan. A formula makes the logic visible and can be filtered, copied, or reviewed later.
Make a combined key for multi-column matches. If a record is a duplicate only when both an ID and a date match, create a helper value that combines those fields, or use a formula that compares both conditions. Avoid deciding that a whole row is a duplicate based on only one column.
Clean inconsistent text first. You can use Excel’s TRIM function in a separate cleanup column to remove ordinary leading and trailing spaces. Keep the original data until you have checked the cleaned results; TRIM does not remove every kind of invisible character.
Use a Table for lists that grow. Tables expand as you add rows, which can make ongoing review more convenient. After adding records, confirm that formulas and conditional formatting extend to the new rows.
Keep a record of what you checked. Add a descriptive heading to the helper column, such as Duplicate email, and note whether the check identifies every copy or only later occurrences. This makes the result easier for someone else to interpret.
Do not treat a highlight as proof of an error. A repeated value may be valid. Use the mark as a review signal, then verify the surrounding record and the rules for your particular list.

Troubleshooting

Nothing is highlighted, but you can see repeated entries. Check that the entire list is selected and that the rule is set to Duplicate, not Unique. Look for extra spaces, inconsistent spelling, or entries stored in different formats. If values were pasted from a website or another system, try inspecting them in the formula bar or testing a cleaned copy of the data.

Too many cells are highlighted. Make sure the selection does not include unrelated columns, totals, or a heading that appears again in the list. The duplicate rule checks the whole selected range, not just the column you had in mind. Clear the rule from the selected cells and apply it again to the correct range.

The formula shows FALSE for a value you expected to match. Verify the formula’s range includes both entries and that its endpoints use the right row numbers. Check for extra spaces or different characters, and make sure the formula was filled down rather than copied with a shifted range. In the all-occurrences formula, the dollar signs should keep the full search range fixed.

The formula shows an error or does not calculate. Confirm the formula begins with an equals sign and that parentheses are balanced. Excel for Microsoft 365 on Windows normally uses commas between function arguments; if Excel is configured for a different regional list separator, it may require semicolons instead. Also check that the cells are not formatted as Text: change the format to General, then re-enter the formula.

New entries are not included. The formula’s fixed range may stop at the last row you originally listed, or Conditional Formatting may not extend to the added cells. Expand the range or use a Table and verify the rule’s applied range. Do not assume a previous selection automatically covers every future row.

You need to compare more than one column. A single-column duplicate check cannot establish that two full records match. Decide which columns define a duplicate and compare those together. For a small list, sort by the relevant fields and review adjacent records; for a recurring task, build an explicit helper key or multi-condition check and test it against known examples.

Frequently Asked Questions

Does Excel have a built-in way to highlight duplicate values?

Yes. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and click OK. The rule highlights values repeated within the selected range.

Can I find duplicates without deleting them?

Yes. Conditional Formatting only changes how cells appear, and a COUNTIF helper formula reports matches without removing data. Deleting duplicates is a separate action, so do not use Data > Remove Duplicates unless you intend to remove rows.

How do I flag only the second and later copies?

Use a running COUNTIF formula such as =COUNTIF($A$2:A2,A2)>1, with the list starting in A2 and the formula in the adjacent row. Fill it down. The first occurrence returns FALSE, and later occurrences return TRUE.

Does Excel treat uppercase and lowercase as different values?

Not for the built-in duplicate highlighting or the COUNTIF examples in this guide; those checks are not case-sensitive. If capitalization must distinguish values, use a case-sensitive comparison method rather than relying on these checks.

Can I check duplicates across two columns?

Yes, but first define what should count as a match. If a duplicate means the same value in both columns, check the pair together rather than marking repeats in either column independently. A helper key or a formula that tests both fields can do this; review examples to ensure the rule matches your records.

Why are blank cells affecting my duplicate check?

Blank cells may be part of the selected range, and a formula can evaluate them unless you exclude them. Use a nonblank condition in a helper formula, such as the AND and COUNTIF example above, and confirm that the range contains only the cells you want to check.

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.