To highlight repeated values in an Excel table, select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and select OK. Excel marks matching entries without removing or changing the data, making it easier to review a list before you clean it up.
The built-in rule is the quickest choice when you want to find repeated values in one column, such as customer names or invoice numbers. You can also apply it to a larger range, but be clear about what counts as a duplicate: matching a value in one column is different from finding two entire rows that match. The steps below use Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with the table or list you want to check.
- A clear idea of which column or columns define a duplicate. For example, a repeated email address may be a duplicate even if the person’s name differs.
- Permission to edit the workbook. Conditional formatting changes the display, not the underlying values.
Step-by-Step
- Open the worksheet and identify the field to check. Decide whether you are looking for repeated entries in one field, such as order IDs, or repeated combinations of fields, such as the same first name and last name. For the usual case, use one column. If the list is an Excel table, find its column heading and check that you are looking at the intended table.
- Select the data cells, not the heading. Select the cells in the column that contain records. For example, if the heading is in A1 and the entries are in A2:A100, select A2:A100. Leaving the heading out avoids formatting it as part of the data and makes the rule easier to manage. You can drag across the cells or click the first cell, then Shift-click the last one.
- Open the duplicate-value rule. On the ribbon, select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The Duplicate Values dialog opens. In Excel for Mac, the same conditional-formatting command is available from the Home tab; ribbon layout can vary slightly by release. In Excel for the web, use Home > Conditional Formatting and choose the duplicate-values rule if it appears in the available rules.
- Choose what to highlight. In the dialog, keep Duplicate selected to mark values that occur more than once. The other choice, Unique, highlights values that occur just once, so it is not the setting to use when searching for repeats. Select the formatting menu to choose a preset style, such as Light Red Fill with Dark Red Text, or choose Custom Format to set a different fill, font, border, or number format.
- Apply the rule. Select OK. Excel highlights every occurrence of a duplicated value in the selected cells, not only the second and later occurrences. For example, if “North” appears in A2, A8, and A20, all three cells receive the format. The cell contents remain unchanged; the color is a visual indicator.
- Check that the rule covers the right cells. Click a highlighted cell and open Home > Conditional Formatting > Manage Rules. In the rules list, check the “Applies to” range. It should contain the intended data cells and normally exclude the header. If the rule should cover the whole table column, adjust the range or recreate the rule with that column selected.
- Test a known repeat and a non-repeat. Compare the formatting with a value you know appears twice and another that appears once. This catches selection mistakes early. If the same displayed value seems to be treated differently, review the underlying entries; one may contain an extra space or a different character.
- Keep the rule useful as the table changes. Add a test row at the bottom of the table and check whether the rule includes it. Excel often extends table formatting as new rows are added, but confirm the “Applies to” range if the highlight does not extend. When a rule uses a fixed range, update that range when your data grows.
To highlight duplicates across several selected columns: select the data area, excluding headings, and use the same Duplicate Values command. Be aware that this flags repeated individual values anywhere in the selected area. It does not check whether every field in two rows matches. For example, a city name repeated in many customer records may be highlighted even though those records are not duplicate rows.
To check full-row matches instead: use a formula-based conditional-formatting rule. For example, with IDs in A2:A100 and dates in B2:B100, and with the data rows spanning A2:B100, select A2:B100 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For this layout, use =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. Choose a format and confirm. The rule highlights both rows whenever the ID and date combination occurs more than once. Change the columns and row limits to match your own data. If blank rows are included, add a condition to avoid marking rows that are empty in the key fields.
Common Mistakes
- Selecting the whole worksheet or the wrong column. A broad selection can make unrelated repeated values appear to be duplicates. Select just the relevant data cells, or deliberately select the intended table columns.
- Including the heading. A heading is usually unique, but including it makes the rule’s range less tidy and can complicate later edits. Start with the first record row.
- Expecting the rule to find duplicate records. The built-in rule compares cell values, not whole rows as records. If a row should count as a duplicate only when multiple fields match, use a formula rule based on those fields.
- Thinking Excel has removed or corrected a value. Conditional formatting only changes appearance. It does not delete duplicates, merge records, or decide which entry to keep. Review highlighted cells before making changes.
- Assuming similar-looking text is identical. “Lee” and “Lee ” (with a trailing space) may look alike but are different cell contents. Punctuation, hidden spaces, and imported characters can also affect comparisons.
- Creating overlapping rules without checking them. If other conditional-formatting rules already apply to the same cells, they may affect the final appearance. Review them in Manage Rules rather than adding repeated rules blindly.
Tips
- Choose the key field carefully. If every email address should be unique, check that column rather than the name column. Two people can share a name, and one person may appear with slightly different name spellings.
- Use a distinctive format. A soft fill makes duplicates visible while keeping the text readable. If the worksheet already uses many colors, choose a border or font style that stands out without making the data hard to scan.
- Filter or sort after highlighting. A filter can help you review rows around flagged entries, while sorting by the key field can bring matching values together. Conditional formatting remains in place as you sort the table.
- Keep an original copy before cleanup. If you plan to delete or edit records after reviewing the highlights, save a copy first. The highlight is a review aid, not proof that a record should be removed.
- Use the formula route only when you need a different definition. The built-in Duplicate Values rule is simpler for a single field. Formula rules are more flexible, but their cell references and “Applies to” range must match the worksheet layout.
Troubleshooting
Nothing is highlighted. First confirm that Duplicate, rather than Unique, is selected and that the selected range contains the repeated entries. Then check Manage Rules to see whether the rule applies to those cells. If the values look the same but are not flagged, compare their contents for spaces, punctuation, or other differences. You can also temporarily choose a stronger fill color to rule out a subtle format.
Too many cells are highlighted. Check whether you selected several columns. The built-in rule looks for repeated values across the selected cells, so common words, dates, or status labels can all be marked. Reapply the rule to just the key column, or create a formula rule that defines the exact combination that should count as a duplicate.
Only some expected matches are colored. Open Manage Rules and inspect “Applies to.” The range may stop before the latest rows, or it may start in the wrong row. Correct the range and make sure formula-based rules use relative row references correctly. For instance, a rule intended for row 2 should refer to row 2 in its relative cell references, while dollar signs should lock the columns or lookup ranges that need to stay fixed.
The highlight disappears after adding records. The new rows may be outside the rule’s range. Extend “Applies to” in Manage Rules, or apply the rule to the table’s data column again. If you add rows inside the table and the rule still does not extend, check that the cells are actually part of the table rather than merely formatted to look like it.
The colors look wrong or inconsistent. Another conditional-formatting rule may overlap the duplicate rule. In Manage Rules, inspect the rules for the current worksheet and check their order and ranges. You can edit or remove an obsolete rule there. If the cells have manual formatting, choose a more visible conditional format and verify the result on a known duplicate.
Frequently Asked Questions
Does Excel highlight every occurrence or only the later duplicate?
It highlights every matching occurrence within the selected range. If a value appears three times, all three cells are marked, which makes the complete set easier to review.
Can I highlight duplicates in an Excel table as new rows are added?
Usually the table’s formatting extends when you add rows within the table, but check the rule’s “Applies to” range after adding data. If the new row is outside that range, extend it or recreate the rule for the table column.
Does the built-in rule find duplicate rows?
No. It compares individual cell values in the selection. To identify rows that match on chosen fields, apply a formula-based conditional-formatting rule to the row range and compare those fields, as in the ID-and-date example above.
Are uppercase and lowercase text treated as different?
For this built-in duplicate check, text that differs only by letter case is generally treated as the same value. If the distinction matters for your task, test your data and use a case-sensitive approach rather than relying on the default rule.
Can I remove the highlights without deleting the values?
Yes. Select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the conditional-formatting rule from that selection; it does not delete cell contents. Choose the worksheet option only if you intend to clear rules across the whole sheet.
Can I use this in Excel for Mac or Excel for the web?
Yes. The duplicate-values conditional-formatting feature is available in current Excel versions on Mac and the web, though ribbon placement and dialog appearance can differ. On Mac, use the Home tab’s Conditional Formatting menu; in the web app, open Home > Conditional Formatting and look for the duplicate-values rule.
Related
- Excel Sum Text? What SUM Does With Text Cells
- How to Remove Duplicates in Excel Safely, Step by Step
- Remove Duplicates in Excel by Entire Row: A Safe Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Mailbox Rules: How to Create and Manage ThemCreate a mailbox rule in Outlook by choosing a condition, such as who sent a message or words…
- Google Sheets COUNTIF Range: Syntax, Examples, and FixesIn Google Sheets, COUNTIF counts cells in a range that meet one condition. The basic formula is =COUNTIF(A2:A20,"Open");…
- How to Create a Word Template with Fillable FieldsTo create a Word template with fillable fields, build a document in Word for Microsoft 365, add content…
- How to Change the Background View in Outlook on Any DeviceTo change Outlook’s appearance, open its appearance or theme settings and choose a light, dark, or colored theme;…
- Microsoft Excel Scatter Plot: How to Create and Read OneIn Microsoft Excel for Microsoft 365 on Windows, put the X values in one column and their matching…
- Excel Keyboard Shortcuts to Select an Entire ColumnIn Excel for Microsoft 365 on Windows, select a cell in the column you want and press Ctrl+Space…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.