How to Identify Duplicates in Excel: A Step-by-Step Guide

To identify repeated values in Excel, select the cells you want to check and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel marks values that appear more than once in the selected range. For a reusable label beside each row, use COUNTIF in a helper column. Before starting, decide whether you mean repeated entries in one column, matching combinations across columns, or identical entire rows—those checks are not the same.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows. The steps are similar in current Excel for Mac and Excel for the web, though some ribbon labels or dialog layouts can differ slightly.
A clear idea of which range or columns define a duplicate. For example, a repeated email address is different from a row that matches across name, email, and department.
Optional: a spare column for a formula result, and a saved copy of the workbook if you may later remove duplicate records.

Step-by-Step

Choose what counts as a duplicate. If you need to find repeated product IDs, select the product ID cells. If a record is only a duplicate when several fields match, such as first name and email, check the combination of those fields instead. Decide whether the first occurrence should also be marked. The built-in duplicate formatting marks all occurrences of a repeated value, while a formula can distinguish the first from later repeats.
Check that the data is laid out consistently. Make sure the column has a heading and that each record is on its own row. Remove any completely empty rows within the data if they make it harder to select the right range. If you have a header in row 1 and values in A2:A100, select A2:A100, not the heading. Excluding the heading prevents Excel from treating a repeated heading elsewhere as a data match.
Highlight repeated values with Conditional Formatting. Select the data cells to check. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format such as a colored fill, and select OK. Excel applies the formatting to cells whose values occur more than once within the selected range. You can select a different format in the dialog if the default color is difficult to see against your sheet.
Review the highlighted cells in context. Look at each marked value alongside its row and nearby fields. The rule checks cell values in the selected range; selecting several columns does not make Excel compare each complete row as a combined record. For example, if a name appears twice but the email addresses differ, both names can be highlighted even though the records may not be duplicates. Also check that the selected range starts and ends where you intended.
Add a formula label when you want a sortable result. With data in A2:A100 and a header in A1, put a heading such as Status in B1. In B2, enter =IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")), then fill the formula down through B100. This returns Duplicate for every nonblank value appearing more than once in A2:A100 and Unique for a value that appears once. The blank check leaves empty cells unlabelled. The dollar signs keep the checked range fixed as you copy the formula down.
Choose whether to mark all occurrences or only later repeats. The previous formula labels every occurrence of a repeated item, including its first appearance. To label only the second and subsequent occurrences, use the same layout—values in A2:A100 and the result formula in B2—and enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence")). Fill it down. The expanding range counts from A2 to the current row, so the first instance is labelled First occurrence and later instances are labelled Duplicate.
Check duplicates based on more than one field. Suppose the fields are in columns A and B, rows 2 through 100, and a record counts as a duplicate only when both fields match. In C2, enter =IF(OR(A2="",B2=""),"",IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"Duplicate","Unique")) and fill down. This labels a row Duplicate when that A-and-B combination occurs more than once, and leaves rows with either field blank unlabelled. Add another range-and-criteria pair to COUNTIFS if a third field must match too.
Filter or sort the results to inspect them. If you used a helper column, select your data and choose Data > Filter, then use the filter arrow in the Status column to show Duplicate. You can also sort by the helper column to group matching results. Keep the complete rows together when sorting; sorting only one column can detach values from their related records. Conditional Formatting has no result column to filter, so use the highlights to review the matching rows or add a formula label.
Save a copy before removing anything. Identifying duplicates does not delete data. If you decide to remove records, save a separate copy first and review which occurrence should remain. Excel’s Data > Remove Duplicates command deletes rows based on selected columns; it is a separate action, not a way to preview every match. Do not use it until you are sure the selected columns define a duplicate correctly.

Common Mistakes

Selecting the wrong range: Conditional Formatting compares cells within the selection. If you select only A2:A50, a match in A80 will not be included. If you select a whole worksheet or unrelated columns, the result may be confusing. Select the precise data range, including all relevant records but excluding headings.

Assuming a match in one column means matching records: A shared city, surname, or status may be common without indicating a duplicate row. For a multi-field check, use COUNTIFS with the relevant columns. Conversely, checking just one field can miss records that have the same ID but different spelling or spacing in another field.

Overlooking spaces and inconsistent entry: Excel may treat text with leading or trailing spaces as different values. For example, an address with a space at the end may not match the clean version. Review suspiciously unmarked entries and, if appropriate, clean a copy of the data with TRIM in a helper column before checking again. Do not overwrite source data until you have reviewed the changes.

Confusing case or data types: COUNTIF and the built-in duplicate rule are generally not case-sensitive, so text such as ABC and abc is usually treated as a match. A number stored as text can also behave differently from a numeric value in some comparisons or imports. Check the source values and formats when a result seems unexpected.

Deleting before checking: Remove Duplicates keeps one record and removes other matching rows according to the columns you choose. It does not decide which record is more complete or current. Use a copy, inspect the results, and make sure you selected the right fields before removing rows.

Tips

Convert a well-formed data range into an Excel table with Insert > Table. Tables make headings easier to work with and usually expand when you add records. If you use a fixed formula range such as $A$2:$A$100, remember to extend it when new rows are added; otherwise, new entries will not be included in that check.

For a quick visual scan, use a fill color that contrasts with the sheet and make sure the font remains readable. Conditional formatting is useful for ongoing review because it can update as cell values change within the applied range. If you copy cells from another workbook, review the rule through Home > Conditional Formatting > Manage Rules to confirm that its “Applies to” range covers the data you want.

Keep the original data intact while investigating. A helper column makes the result explicit and can be filtered, copied, or reviewed with other fields. If you need a list of distinct values rather than marked duplicates, that is a different task; do not remove rows simply because you want a summary of unique entries.

For an exact full-row comparison, create a helper key that joins the relevant fields, or use COUNTIFS to test each field as shown above. Joining fields can be ambiguous if values contain the same separator, so COUNTIFS is often safer for a small fixed set of columns. Decide how blanks should be handled: two blank fields can otherwise appear to match, which is why the example formula leaves incomplete rows unlabelled.

Troubleshooting

No cells are highlighted: Confirm that you chose Duplicate Values rather than Unique Values, and check the selected range and its Conditional Formatting rule. Open Home > Conditional Formatting > Manage Rules and inspect “Applies to.” If the data was pasted after the rule was created, the new rows may be outside that range. Expand the rule’s range or select the full data area and create the rule again.

Values that look the same are not flagged: Compare the underlying contents, not just how they appear on screen. Extra spaces, nonprinting characters, punctuation, or different number formats can make entries differ. Try widening the column to inspect text, or use a separate cleanup column and review the cleaned values before applying a duplicate check. Imported data can include unusual spaces that a regular visual scan does not reveal.

Too many results appear: Check whether you selected multiple columns and expected a whole-row comparison. The built-in rule checks repeated cell values, not repeated combinations of fields. Use COUNTIFS for the combination, and verify that each criteria range covers the same rows. Also consider whether a commonly repeated value is genuinely a unique identifier; duplicates in a non-unique field may be normal.

The formula shows an error or the same result everywhere: Confirm the formula is in the intended row, that the ranges have not shifted, and that it was filled down. In the examples, the lookup range is locked with dollar signs while the cell reference changes from A2 to A3 and onward. Excel normally uses commas between formula arguments under standard regional settings. Some regional configurations use semicolons instead; if Excel rejects a correctly copied formula, check the list separator setting used by your installation.

The formula does not update after a change: Check Formulas > Calculation Options and select Automatic if the workbook is set to manual calculation. Then recalculate the sheet. Also confirm that the changed row falls within the formula’s checked range; a formula limited to A2:A100 will not count a new value in A101 unless you extend the range.

Frequently Asked Questions

Does Excel highlight every copy or only the second one?
The Duplicate Values conditional-formatting rule highlights every occurrence of a repeated value in the selected range, including the first. Use the expanding COUNTIF formula in the steps if you want to identify only later occurrences.

Can I find duplicates across two columns?
Yes. Use COUNTIFS to check whether a combination appears more than once, as in the example for columns A and B. This differs from selecting both columns for Conditional Formatting, which checks repeated cell values rather than matching pairs.

Does Excel consider uppercase and lowercase text different?
Ordinary duplicate formatting and COUNTIF comparisons are not case-sensitive, so capitalization alone generally does not make entries unique. If capitalization matters to your task, use a case-sensitive method rather than relying on these checks.

Will the duplicate rule include new rows I add?
Only if the rule’s “Applies to” range includes those rows. Check it in Home > Conditional Formatting > Manage Rules, or use an Excel table and verify the rule after adding data. A fixed formula range must also be extended when the list grows.

How do I remove duplicates after identifying them?
Save a copy, select the data, then choose Data > Remove Duplicates and specify the columns that define a duplicate. Excel keeps one matching record and removes the others. Review the result carefully; removal is not a substitute for identifying and verifying the right rows first.

Are these steps different on Mac or Excel for the web?
The core Conditional Formatting and formula approach is available in current Excel for Mac and Excel for the web. Ribbon placement or dialog appearance can vary, so look for the same Conditional Formatting and Duplicate Values options. Menu names and formula separators may also depend on version and regional settings.

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.