Yes. In Excel for Microsoft 365 on Windows, select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, select a format, and click OK. Excel will visually mark values that occur more than once in the selected range; it does not delete or merge them.
What duplicate highlighting does
Conditional formatting applies a visual style when a cell meets a rule. The built-in Duplicate Values rule checks the selected cells and highlights values that appear at least twice in that selection. For example, if a list contains “North,” “South,” “North,” the two cells containing “North” are marked. A value appearing only once is not.
This is a way to find possible duplicates, not a data-cleaning command. The cells keep their original contents, and you can still edit, sort, copy, or delete them normally. The formatting updates as the values change, subject to the rule’s range and calculation settings.
The selection matters. If you select A2:A100, Excel compares entries within that column. If the same name appears in column B but not in column A, it does not count as a duplicate in the A-only selection. To check across multiple columns, select the whole intended area before applying the rule. That broader check can flag values that repeat in different columns, which may or may not be what you mean by a duplicate.
Apply the built-in rule step by step
- Select the range to check, such as A2:A100. If the first row contains headings, leave it out so the heading is not treated as a data value.
- On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the dialog, leave Duplicate selected. Choose a preset format or select Custom Format to define a fill, font, or border.
- Click OK, then review the marked cells against the surrounding records.
For a table with names in A2:A100 and email addresses in B2:B100, decide which field defines a duplicate before selecting anything. If repeated email addresses are the concern, select B2:B100. If you want to identify repeated full rows, a duplicate rule applied to both columns does not necessarily compare each complete row as a single record; it can highlight individual repeated cell values. A custom formula or a helper column is better for matching combinations of fields.
On Excel for Mac, the Conditional Formatting commands are also on the Home tab; choose Conditional Formatting > Highlight Cells Rules > Duplicate Values. In Excel for the web, select the range and use Home > Conditional Formatting, then choose the duplicate-values rule if it is available in the current interface. Some dialog details and formatting controls can differ between desktop and web versions, but the rule’s purpose is the same.
Choose the right range and definition
Before applying the rule, decide what “duplicate” means for the task. A repeated product name may be acceptable if it appears on separate order lines, while a repeated invoice number may indicate a record that needs investigation. Excel can only compare the values and range you provide; it cannot infer the business meaning of a repeat.
- One column: Select the field whose repeated entries matter, such as customer ID or email address.
- Several columns, any repeated value: Select the full area if you want to see repeated cell values wherever they occur in that area. This can mark different fields that happen to contain the same text.
- Several fields as one record: Compare a combined key, such as customer ID plus date, rather than treating each cell as a separate value.
- Only later occurrences: Use a formula-based rule if the first appearance should remain unmarked and only subsequent appearances should be highlighted.
Including a heading can create a misleading result if its text is repeated in the selected range. Likewise, a very large selection can make the rule harder to understand and maintain. For routine work, apply the rule to the actual data rows, and extend the range when new rows are added. Excel tables can be useful because table ranges expand as rows are entered, though you should check the rule’s Applies to range if new rows do not receive the formatting.
Use a formula for more control
The built-in rule is quick, but a formula lets you exclude blank cells, mark only later repeats, or use a specific comparison range. In Excel for Microsoft 365 on Windows, select the target cells and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set the desired format, then confirm the rule. The formula’s references must be written relative to the top-left cell of the selected range.
With values in A2:A100 and that same range selected, use this formula to highlight nonblank values that occur more than once:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)
The rule checks each cell against the fixed range A2:A100. The dollar signs keep the comparison range fixed as Excel evaluates the rule for other rows, while A2 changes to the cell being checked. The AND condition prevents empty cells from being highlighted by the count. For example, if “Maple” appears in A4 and A9, both cells are formatted; if A10 is empty, it is not.
With values in A2:A100 and that range selected, use this formula to highlight only the second and later occurrences:
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)
Here the comparison range grows from A2 to the current row. If “Maple” first appears in A4 and appears again in A9, A4 is left unformatted and A9 is marked. This is useful when the first row is the record to retain and later repeats need review. It does not remove or resolve those repeats.
In Excel for Mac, create formula-based rules through the Conditional Formatting controls on the Home tab; wording and dialog layout can vary by release. Excel for the web also supports conditional-formatting rules, although rule management and formula editing may be more limited than in desktop Excel. If a formula rule behaves unexpectedly, check the selected range’s top-left cell and the rule’s Applies to field.
What Excel considers a match
The standard duplicate-value comparison is generally not case-sensitive: “Maple” and “MAPLE” are treated as the same text for this purpose. A regular COUNTIF-based conditional-formatting formula is also case-insensitive. If capitalization distinguishes records in your workflow, the built-in rule and ordinary COUNTIF formula will not enforce that distinction; a more specialized formula or a helper column is needed.
Spaces and other invisible characters are another source of confusion. “Maple” and “Maple ” may look alike on screen but contain different text because one has a trailing space. Extra spaces copied from websites or imported files can therefore prevent an apparent match. TRIM can remove ordinary extra spaces in a separate cleanup column, but it does not remove every possible nonprinting character. Inspect and clean imported data before concluding that the duplicate rule has failed.
Numbers stored as text can also complicate comparisons, especially after data has been imported. A cell containing the number 25 and another containing text that looks like 25 may not behave as expected in every comparison context. Check the cell values and number formats, and standardize the data type when the field is meant to contain one consistent kind of value. Number formatting alone changes how a value looks; it does not necessarily change the underlying value.
Compare complete records with a key
A frequent misconception is that highlighting duplicates across a rectangular selection identifies repeated rows. The built-in rule looks for repeated values in the selected cells; it does not automatically treat each row as a complete record. For example, two order rows may both contain “Online” in the channel column without being duplicate orders.
To identify repeated combinations, create a helper key that joins the fields that define a unique record, then apply duplicate formatting to that key. With customer IDs in A2:A100 and order dates in B2:B100, put this formula in C2 and fill it down:
=A2&"|"&TEXT(B2,"yyyy-mm-dd")
The key combines the ID with a consistently formatted date, so the same customer and date produce the same text key. The vertical bar is a separator to reduce ambiguous joins; use a separator that cannot occur in the source fields, or build a more robust key if it can. Then apply the Duplicate Values rule to C2:C100. This example is useful only if those two fields together define the record for your purpose; include other fields when they are part of that definition.
A helper column makes the matching logic visible and easier to audit. It is often preferable to a long conditional-formatting formula when comparing three or more fields, or when another person needs to understand why two rows were flagged. You can hide the helper column after checking the results, but keeping it visible during review can help explain the matches.
Common problems and fixes
- Nothing is highlighted: Confirm that the intended cells are inside the rule’s Applies to range and that at least one value occurs more than once there. Review Home > Conditional Formatting > Manage Rules.
- The heading is highlighted: Edit the rule’s Applies to range to start below the heading row, or reapply the rule to data cells only.
- Blanks appear to match: Use the nonblank formula rule shown above. A formula based only on COUNTIF can count empty cells in the comparison range.
- Only some matching rows are colored: Check whether the selected range stops before all records, or whether the rule has a different Applies to range than expected.
- Colors do not change after editing: Check that the rule still applies to the edited cell and that calculation is current. Also look for another conditional-formatting rule that could affect the appearance.
- The highlight color is hard to see: Choose a higher-contrast custom format. Do not rely on color alone if the sheet will be printed in grayscale or used by people who have difficulty distinguishing colors.
Conditional formatting rules can overlap. In Manage Rules, inspect the rules applying to the same cells and their order. A later or higher-priority rule can affect the final appearance, and Stop If True can prevent lower-priority rules from being evaluated. If the result looks inconsistent, simplify overlapping rules or adjust their order rather than repeatedly applying new duplicate rules.
Highlighting versus removing duplicates
Highlighting is reversible and is suited to inspection: it draws attention to values while preserving all rows. Removing duplicates changes the data by deleting repeated entries according to the columns you select. In Excel, the Remove Duplicates command is on the Data tab. Make a copy of the data before using it, and carefully choose which columns define a duplicate; otherwise, distinct records can be removed by mistake.
Neither method determines which record is correct. If two rows share an ID but have different dates or other details, the repeat might be an error, a legitimate transaction, or a record requiring reconciliation. Compare the source records and the rules of the dataset before deleting anything. Conditional formatting is a warning signal, not a substitute for that decision.
Frequently Asked Questions
Does conditional formatting remove duplicate values?
No. It changes how matching cells look, but leaves their contents and rows intact. Use a separate data-cleaning method if you decide that duplicate records should be removed.
Can Excel highlight duplicates in one column only?
Yes. Select the column’s data cells, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The rule compares values within the selected range.
Can I highlight only the second occurrence?
Yes. Create a formula-based rule on the target range using the growing-range COUNTIF formula above. It leaves the first occurrence unformatted and marks later repetitions.
Why are duplicates not being highlighted?
Check the selection and the rule’s Applies to range first. Then look for extra spaces, inconsistent data types, or text that only looks identical. A rule limited to one column will not find a match that exists only in another column.
Does Excel treat uppercase and lowercase as different?
The built-in duplicate rule and standard COUNTIF-based rule are not case-sensitive. Values such as “Maple” and “MAPLE” are treated as a match for these checks.
Can I check whether whole rows are duplicates?
Yes, but use a key made from the fields that define a repeated record, then check that key for duplicates. Applying the built-in rule across several columns can flag repeated individual cells rather than identical complete rows.
Related
- Excel Shortcut to Move Between Tabs: Windows and Mac
- Workbook Almost Too Large? How to Shrink an Excel File
- Protect an Excel Sheet Without Locking Cells: How It Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Stop Showing Page Break Lines: FixesTo stop Excel from showing page break lines, go to File > Options > Advanced, find Display options…
- Enable Circular References in Excel: Step-by-StepTo enable circular references in Excel for Microsoft 365 on Windows, open the workbook, select File > Options…
- Why Can’t I Add a Header in Word?You usually can’t add a header in Word because the document is not currently editable, editing is restricted,…
- Box Plot Excel Formatting: Create and Style a Box PlotTo create a box plot in Excel for Microsoft 365 on Windows, arrange each group of numeric observations…
- Excel Shortcut to Go to the Top of a Sheet ExplainedIn Excel for Microsoft 365 on Windows, press Ctrl+Home to go to the top-left of the worksheet, normally…
- Nested Dropdowns in Google Sheets: How to Set Them UpYes. You can make a nested, or dependent, dropdown in Google Sheets by making the choices in a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.