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. Excel highlights repeats in the selection. For more control, use a COUNTIF formula, or remove duplicates from a copy of the data after checking what Excel will delete.

What You’ll Need

A workbook in Microsoft Excel for Microsoft 365 on Windows, with the data you want to check.
A clear idea of what counts as a duplicate: a repeated value in one column, or a repeated record across several columns.
A backup copy if you plan to remove records. Removing duplicates changes the data, so keep an untouched version until you have checked the result.

Step-by-Step

Identify the values or records to compare. Decide whether duplicates mean the same email address, invoice number, or other value in one column, or whether every field in a row must match. For example, two people can share a last name without being duplicate records. If you are comparing multiple fields, decide which fields define a match before you start.
Make a safe working copy. If the information matters, save a separate copy of the workbook or duplicate the worksheet before experimenting. This is especially important if you expect to use Remove Duplicates. A visible duplicate highlight does not change your data, but removing rows does.
Select the cells to check. For one column, select its data cells, such as A2:A100, rather than the heading. If you want to compare entire records, select the full data range, such as A1:C100, including the header row. Including the header lets Excel identify column names in some dialogs; do not include unrelated notes or totals in the selection.
Highlight repeated values with Conditional Formatting. On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style, and select OK. Excel highlights values that appear more than once within the selected range. To look for values that occur only once instead, choose Unique in the dialog.
Check what the highlights mean. Look at the column or fields you selected and confirm that the highlighted entries really represent duplicates for your task. If you selected several columns, the built-in rule can highlight repeated cell values in the selection; it does not tell you that every field in a whole row matches another row. Use the formula method below when you need to compare full records.
Use a formula to label duplicates in one column. With values in A2:A100 and a result column starting at B2, enter =COUNTIF($A$2:$A$100,A2)>1 in B2, then fill the formula down through B100. TRUE means the value in that row occurs at least twice in the checked range; FALSE means it occurs once. For example, if “North” is in A2 and A7, both corresponding formula results are TRUE. The dollar signs keep the checked range fixed as the formula is copied.
Compare complete rows when needed. With records in columns A:C and data in rows 2:100, enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1 in D2 and fill down. TRUE means that the combination of values in columns A, B, and C occurs more than once. For example, a repeated combination of the same date, product, and quantity is flagged, even if a row with the same product but a different date is not. Adjust the ranges and criteria if your records use different columns.
Optionally highlight duplicate rows with a formula rule. To color whole rows based on repeats in column A, select A2:C100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =COUNTIF($A$2:$A$100,$A2)>1, choose a format, and select OK. The selected range should begin on row 2 because the formula refers to row 2. This checks column A only. To highlight full-row matches instead, use a COUNTIFS formula like the one in the previous step and apply it to the row range.
Remove duplicates only after reviewing them. Select the data range or click in the data table, then choose Data > Remove Duplicates. Confirm whether your data has headers. In the column list, select the fields that define a duplicate: select every field to remove only fully identical records, or select a specific field such as Email to keep one record per email. Select OK and read Excel’s summary of how many duplicates were removed and how many unique values remain.
Verify the result. Check a few affected records and compare the remaining row count with the message from Excel. If the wrong records were removed, use Undo immediately (Ctrl+Z on Windows; Command+Z on Mac) if the action is still available, or restore your saved copy. Remove Duplicates keeps the first matching record in the current order, so sort or review the data first if you need a particular version of a record to remain.

Common Mistakes

Checking the wrong range: If you include a heading, blank rows, or unrelated columns, the result can be confusing. Select the actual data and make sure your formula range covers all relevant rows. When a formula is filled down, keep the lookup range absolute with dollar signs, but leave the row reference for the current value relative.

Assuming matching cells mean matching records: A built-in duplicate-value highlight finds repeated cell values in the selected range. It does not necessarily mean two entire rows are identical. Use a COUNTIFS formula with each field that defines a record, or carefully choose the columns in Remove Duplicates.

Deleting before deciding which fields matter: Choosing only one column in Remove Duplicates can delete rows that differ in other ways. For instance, if you choose Customer Name, Excel treats repeated names as duplicates even if the orders or dates differ. Choose all relevant columns when records must match across the row.

Expecting Excel to treat text variations as distinct: COUNTIF and the built-in duplicate rule are not case-sensitive, so “abc” and “ABC” generally count as a match. Extra spaces, nonbreaking spaces, punctuation, and numbers stored as text can also make entries that look alike behave differently. Inspect the underlying values before deleting anything.

Forgetting that blanks can repeat: Empty cells may be flagged as duplicate entries when there is more than one blank in the range. If blanks should not count, exclude them from the check or use a formula that tests for a nonblank value as well.

Tips

Sort a copy of the data by the field you are checking before removing anything. Sorting brings matching entries together, making it easier to compare differences and decide which record should be retained. Sorting is not required for the duplicate tools, but it can make a review more manageable.

If your data is an Excel table, use the table’s column headings to keep the comparison clear. When adding a formula column to a table, Excel can fill the formula down automatically. If you use ordinary cell ranges, check that the formula reaches the final row and that new records added later are included in the range.

For values with inconsistent spacing, consider cleaning a helper column rather than changing your original data. With text in A2, a helper formula can remove leading and trailing ordinary spaces: =TRIM(A2). Put it in B2 and fill down, then check the helper results for duplicates. TRIM does not remove every unusual whitespace character, so pasted data from websites may need additional cleanup.

Conditional formatting is a useful, reversible first check: it changes how cells look, not the values themselves. To clear the highlighting later, select the range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Be careful to select only the intended range before clearing rules.

For a case-sensitive check, with values in A2:A100 and the result in B2, enter =SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1 in B2 and fill down. EXACT distinguishes uppercase and lowercase, so “abc” and “ABC” are treated as different. This formula is more specialized than COUNTIF; use it only when capitalization genuinely changes the meaning of a value.

Troubleshooting

No values are highlighted: Confirm that you selected the intended cells and chose Duplicate, not Unique. Check whether apparently repeated values contain hidden spaces or are stored differently, such as a number in one cell and text in another. Try sorting the column and inspecting the entries side by side.

The formula shows FALSE for an apparent repeat: Make sure the formula’s fixed range includes both occurrences and that the formula was copied to the correct rows. Check for extra spaces, different punctuation, or different underlying values. If the repeated record depends on several columns, COUNTIF on one column is not sufficient; use COUNTIFS for all fields that define a match.

Too many cells are highlighted: Check whether blank cells are included, whether the selected range includes other columns, and whether the chosen field is actually unique. If you applied a formula-based rule, confirm the Applies to range and that its first row matches the row number used in the formula.

Remove Duplicates keeps the wrong copy: Excel keeps the first matching row it encounters. Undo the removal if possible, then sort the data so the preferred record appears first, or create a reviewed working copy and remove duplicates again. If Undo is no longer available, restore the original backup.

The menu or dialog looks different: These steps describe Excel for Microsoft 365 on Windows. Excel for Mac has the same Conditional Formatting and Remove Duplicates features, though ribbon layout and dialog appearance can vary; look on the Home and Data tabs. In Excel for the web, the main duplicate-highlighting and removal options are available, but some ribbon details may differ. If a menu item is missing, check that the workbook is editable rather than opened in a restricted view.

Frequently Asked Questions

What is the quickest way to find duplicates in Excel?
Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and select OK to highlight repeated values.

How do I find duplicates in just one column?
Select the column’s data cells and apply the Duplicate Values rule. Or, with values in A2:A100, use =COUNTIF($A$2:$A$100,A2)>1 in a neighboring column and fill it down.

How do I find duplicate rows instead of duplicate cells?
Compare all fields that define a row. A COUNTIFS formula can test the combination of values across those columns; the built-in duplicate-value rule alone is not a reliable test for identical full records.

Does Remove Duplicates delete every copy?
No. It keeps the first matching row and removes later matches according to the columns you selected. Review the selected columns and row order before you confirm.

Can I find duplicates without deleting them?
Yes. Conditional Formatting and COUNTIF formulas identify repeats without removing data. Use either method to review results, and reserve Data > Remove Duplicates for cases where you have confirmed which records should be removed.

Why does Excel mark blank cells as duplicates?
More than one blank in the selected range can be treated as repeated blank values. Narrow the range to populated entries or use a formula that separately checks whether the cell is blank.

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.