To find duplicate values in Excel, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and select OK to highlight repeats. You can also use a COUNTIF formula to label duplicates, or remove them with Excel’s Remove Duplicates command. The best method depends on whether you want to spot, flag, or delete repeated entries.
What You’ll Need
- An Excel workbook with the list you want to check. These steps are for Excel for Microsoft 365 on Windows.
- A clear idea of what counts as a duplicate: the same value in one column, or a repeated record made up of several columns.
- A backup copy if you plan to remove duplicates. Removing rows changes the data, though you can usually undo the action immediately with Ctrl+Z.
You do not need to sort the list first. If the data has a header, keep it outside the range you select for highlighting or formula checks. This helps avoid accidentally treating the header as a list entry.
Step-by-Step
- Choose the range and define a duplicate. Decide whether you are checking one column, such as customer email addresses, or whether a record is duplicated only when several fields match. For a single-column check, select the data cells without the header—for example, A2:A100. For a multi-column record, decide which columns together identify the record, such as name and date of birth. This decision matters: matching one field does not necessarily mean two entire rows are duplicates.
- Highlight repeated values with Conditional Formatting. Select the cells to check. On the Windows ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style from the list, and select OK. Excel marks every matching value in the selected range, including the first occurrence and later occurrences. To change the appearance afterward, select the range and revisit the Conditional Formatting menu to manage or edit its rule.
- Check what the highlight means. A highlighted cell indicates that its value appears more than once in the range you selected; it does not tell you which copy to keep. For example, if A2:A6 contains Maple, Cedar, Maple, Birch, and Cedar, both Maple cells and both Cedar cells are highlighted. The first and second occurrence are treated alike. This built-in rule is a quick visual check, not a way to compare full rows or distinguish uppercase from lowercase.
- Use a formula when you want a clear label. Suppose the values are in A2:A7 and you want a status in column B. In B2, enter
=IF(A2="","",IF(COUNTIF($A$2:$A$7,A2)>1,"Duplicate","Unique")), then fill the formula down through B7 using the fill handle or copy and paste. With Maple, Cedar, Maple, a blank, Birch, and Cedar in A2:A7, the formula returns Duplicate for A2, Unique for A3, Duplicate for A4, a blank for A5, Unique for A6, and Duplicate for A7. COUNTIF finds two occurrences of each repeated word; the formula labels every occurrence, rather than just later copies. The blank check leaves empty cells unlabelled. - Use a formula rule to highlight without including blanks. This is useful if the built-in Duplicate Values rule is marking empty cells or if you want a rule you can control. With the values in A2:A100, select A2:A100, choose Home > Conditional Formatting > New Rule, and select the option to use a formula to determine which cells to format. Enter
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1), choose Format to set a fill or font style, and select OK to save the rule. The A2 reference is relative so Excel checks each cell in the selected range; the dollar signs keep the COUNTIF search range fixed. Make sure the formula’s first cell matches the top-left cell of the selected range. - Check duplicate records across multiple columns. If a row counts as a duplicate only when both its name and date match, use COUNTIFS to test the pair. With names in A2:A100 and dates in B2:B100, enter
=IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"Duplicate","Unique")in C2, then fill it down. For instance, if rows 2 and 8 have the same name and date, C2 and C8 return Duplicate; a row with the same name but a different date returns Unique. COUNTIFS checks that both conditions match on the same row. If a blank row should not be considered a duplicate record, add an appropriate blank check for the fields that define a valid record. - Remove duplicates only after reviewing them. Make a copy of the worksheet or workbook first. Select a cell in the data, then choose Data > Remove Duplicates. In the dialog, confirm whether the data has headers, then select the columns Excel should compare. To remove repeated values from a single-column list, select only that column. To remove repeated records, select the columns that together define a duplicate. Choose OK and read the result message to see how many duplicate values were removed and how many unique values remain. Excel keeps the first occurrence it encounters and removes later matching rows; it does not merge information from them.
In Excel for Mac, the corresponding ribbon commands are generally under Home and Data as described above, although dialogs can look different. The Mac undo shortcut is Command+Z; on Windows it is Ctrl+Z. Excel for the web has similar ribbon options, but available dialogs can vary; if a command is not shown, try the formula method or open the workbook in the desktop app.
Common Mistakes
- Selecting the header as data. A header such as Email can be highlighted or counted if it is included in the range. Start at the first data row, not the header row.
- Confusing duplicate values with duplicate rows. Applying the built-in rule to several columns checks repeated cell values in the selected range; it does not verify that every field in two rows matches. Use a multi-column formula or choose the relevant columns in Remove Duplicates when comparing records.
- Removing data before checking the comparison columns. If you select only Email in Remove Duplicates, Excel can remove a row even if other details differ. Choose all fields that define a repeated record, and keep a backup before deleting anything.
- Expecting a case-sensitive match. The Duplicate Values rule and COUNTIF/COUNTIFS comparisons are not case-sensitive. Excel treats maple and Maple as the same for these checks. If capitalization must distinguish entries, use a case-sensitive method rather than relying on these steps.
- Overlooking extra spaces or inconsistent entry formats. Maple and Maple followed by a space may not compare as you expect. Dates stored as text, numbers stored as text, and hidden characters can also make a list look consistent while values differ. Inspect suspicious entries before deleting records.
Tips
- Sort or filter after flagging. Once you have a status column, use Excel’s filter controls to show only Duplicate rows. This makes it easier to review groups without deleting anything.
- Keep a source column unchanged. Put formulas or cleanup work in a new column or copied worksheet. That gives you a way to compare the result with the original list and recover information if your definition of a duplicate changes.
- Use the narrowest valid comparison. For an email list, checking the email column may be enough. For orders, an order number alone may identify a record, while customer name and date together may be needed for another kind of list. Decide based on what makes a record unique in your data.
- Review the first occurrence as well as later ones. A formula that labels all matches is useful for review, but it does not designate a preferred row. Before removing anything, compare the records and decide which one has the most complete or current information.
- Refresh your check after editing. Conditional formatting and formulas usually update when values change, but a rule may not cover rows added below its original range. Extend the selected range or adjust the rule’s Applies to range when your list grows.
Troubleshooting
Nothing is highlighted. Confirm that the range includes the repeated cells and excludes only the header. Check that the entries are truly the same, including spaces, punctuation, and number or date formatting. If the range is part of a table, verify that the conditional formatting rule applies to the table’s data rather than an unrelated range. You can use the formula method to check a specific range and see a text result beside each entry.
Blank cells are being marked. Empty cells can complicate duplicate checks, especially when formulas return an empty string. Use the formula-based conditional formatting rule with the A2:A100 layout shown above; its A2<>"" test excludes empty-looking cells. If your formula results contain spaces rather than a truly empty string, those cells are not blank to Excel and need cleaning or a more specific test.
The formula shows an error or the same answer on every row. Check that the formula was entered in the intended first row and that you filled it down. In the COUNTIF formula, keep the dollar signs around the list range so it remains fixed as the formula moves; leave the individual cell reference relative so it changes from A2 to A3, A4, and so on. In regional settings that use semicolons as separators, Excel may require semicolons instead of commas; the formulas here use the standard comma-separated syntax requested for Excel.
Remove Duplicates deletes more rows than expected. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, if you have not made other changes since. Then reopen the command and review the selected columns: Excel compares only the columns checked in the dialog. If you need to keep all rows for inspection, use highlighting or a helper formula instead of deletion.
Frequently Asked Questions
Does Excel highlight every copy or only the later one?
The Duplicate Values conditional formatting rule highlights every occurrence in the selected range, including the first one. A COUNTIF formula that checks whether the count is greater than one also labels every occurrence.
Can I find duplicates in more than one column?
Yes. You can select multiple columns for a visual check, but that highlights repeated cell values across the selection, not duplicate rows as complete records. To identify repeated records, compare the defining columns together with COUNTIFS or select those columns in Remove Duplicates.
Does Remove Duplicates keep the first or last entry?
It keeps the first occurrence in the current row order and removes later rows that match the selected comparison columns. Sort or review the data first if you need to control which record remains.
Can I find duplicates without deleting them?
Yes. Use Conditional Formatting to mark them or add a COUNTIF/COUNTIFS helper column. Both approaches let you review the matches while leaving the source rows in place.
Are uppercase and lowercase treated as different values?
No. The built-in duplicate rule and the COUNTIF and COUNTIFS methods described here are case-insensitive. If capitalization matters, use a specifically case-sensitive comparison method.
Related
- How to Enable Track Changes in Excel
- Enable Scroll Lock in Excel: Keyboard and On-Screen Methods
- How to Enable the Share Option in Excel for Collaboration
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Slow? Causes, Fixes, and Ways to Speed It UpIf Excel is slow, first find out whether the delay affects one workbook or every workbook, then check…
- XLOOKUP vs VLOOKUP in Google Sheets: Which to Use?In Google Sheets, XLOOKUP is usually the more flexible choice: it can search in either direction, uses exact…
- Percentage of a Number in Excel: How to Calculate ItTo find a percentage of a number in Excel, multiply the number by the percentage. For example, with…
- How to Add a Citation in Word: A Step-by-Step GuideTo add a citation in Microsoft Word, place the cursor where the citation belongs, open References > Insert…
- Excel: How to Hide Column Lines and Gridlines on a WorksheetTo hide the faint lines between columns and rows in Excel for Microsoft 365 on Windows, open the…
- Excel Import CSV With Headers: Steps, Options, and FixesTo import a CSV file with its first row as column headings in Excel for Microsoft 365 on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.