To highlight duplicate values in Excel for Microsoft 365 on Windows, select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and select OK. Excel highlights every occurrence of a repeated value in the selected range, including the first one—not just later copies.
Apply the built-in duplicate rule
The built-in rule is the quickest way to spot repeated entries in a list, such as customer IDs, invoice numbers, or product codes. It applies conditional formatting, so it changes how matching cells look without changing their contents. You can remove or edit the rule later.
- Select the cells you want to check. For a list with a header in A1 and data in A2:A100, select A2:A100 so the heading is not included.
- On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the dialog, leave Duplicate selected if you want repeated values, or select Unique to format values that occur only once.
- Choose a preset format, or use the format options to select a different fill or text appearance, then select OK.
In Excel for Mac, select the range and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The dialog’s appearance can differ slightly by version. Excel for the web also offers conditional-formatting options, though the controls available for editing rules may differ from the desktop app.
What Excel counts as a duplicate
The rule checks the selected range, not every cell in the workbook. If a value appears twice within that range, both cells are formatted. This is useful when the aim is to find all members of a repeated group, but it can surprise people who expect only the second and later occurrences to be marked.
The rule compares cell values, including the results of formulas. For example, if two cells contain different formulas but both display the result 250, Excel can treat those results as duplicates. The built-in rule is not a test for identical formulas, matching entire rows, or matching records across multiple worksheets.
Also distinguish duplicate values from duplicate conditional-formatting rules. The first means repeated data in cells. The second means that the worksheet has more than one formatting rule affecting a range. Both situations are common, but they need different fixes: use a duplicate-value rule to inspect data, and the Rules Manager to inspect rules.
Choose the right range before formatting
Range selection determines what Excel compares. If you select A2:A100, each entry is compared with the other entries in that column. If you select A2:B100, Excel checks values across the selected cells together; it does not automatically understand that column A is an ID and column B is a department that form one record.
For example, suppose A2:A5 contains IDs 17, 17, 24, and 31. Selecting A2:A5 highlights both cells containing 17. If you instead select a whole table, a value such as “North” might be highlighted because it occurs repeatedly in the region, even though repeated department names are expected. Select only the field whose duplicates matter.
- Exclude headings unless you specifically want them included in the comparison.
- Check whether the selected range includes blank rows or unrelated columns.
- For a table that will grow, consider applying the rule to the table column or extending the range as new records are added.
- For duplicate records defined by several fields, use a formula rule rather than selecting the entire table and relying on the built-in rule.
Use a formula for more control
A formula-based rule can ignore empty cells, mark only later occurrences, or identify repeated combinations of fields. To create one, select the cells to format, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format. Enter a formula that refers to the first cell in the selected range, then choose Format to set the appearance.
With the list in A2:A100 and the rule applied starting at A2, this formula identifies any nonblank value that appears more than once:
With values in A2:A100 and the rule applied to A2:A100, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). If A2 contains P-104 and P-104 occurs twice in the range, COUNTIF returns 2, so the condition is TRUE and A2 is formatted. A value appearing once returns 1, so it is not formatted. The AND condition prevents empty cells from being treated as matches.
To format only the second and subsequent occurrences, use a running count instead:
With values in A2:A100 and the rule applied starting at A2, use =COUNTIF($A$2:A2,A2)>1. If A2 is the first P-104, the running count is 1 and the condition is FALSE; when the next P-104 appears later, the count is 2 and the condition is TRUE. Keep the first reference in the range fixed at row 2, while the final row expands as the rule is evaluated down the list.
For duplicates based on two fields, such as the same employee ID in the same location, use a COUNTIFS rule. With employee IDs in A2:A100, locations in B2:B100, and the formatting range A2:B100, use =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. If the ID-location pair in row 2 occurs again in row 8, COUNTIFS returns 2 and both cells in each matching row can be formatted. The dollar signs keep the lookup ranges fixed while the row references adjust for each record.
Understand rule scope and priority
Conditional formatting is attached to a range through a rule’s Applies to setting. When a formula rule seems to mark the wrong cells, check both the formula references and the range it applies to. A formula written for a rule beginning in A2 may give unexpected results if the rule actually begins in A1 or applies to a different column.
To inspect rules, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, use the Show formatting rules for list to view rules for the current selection or the worksheet. Check Applies to to see which cells each rule covers. If rules overlap, Excel’s rule order and the Stop If True setting can affect which format is visible. A cell may meet more than one rule, and the displayed result may not be the format you expected.
Before changing a complicated worksheet, note the existing ranges and formats. Deleting a rule removes its conditional formatting, not the cell values. If several rules appear to do the same job, compare their formulas, ranges, and formats before removing one; a rule that looks redundant may cover additional rows or columns.
Common problems and practical fixes
- Nothing is highlighted: Confirm that the duplicate entries are inside the selected range and that the rule is still present in Manage Rules. Check that the chosen format is visible against the cell’s fill and font colors.
- Too many cells are highlighted: Narrow the selection to the relevant column, or use a formula that tests a combination of fields. A broad selection can flag repeated values that are normal in other columns.
- Blank cells appear involved: Use a formula with an explicit nonblank test, such as the AND and COUNTIF example above, and check whether the selected range contains empty cells.
- Only one of two expected matches is formatted: Inspect the Applies to range and formula anchoring. In a formula rule intended for A2:A100, the cell reference should normally start with A2 so it adjusts for each row.
- Values look the same but are not matched: Check for leading or trailing spaces, inconsistent data types, or other invisible differences. Cleaning the source data may be better than building a more complex formatting rule.
- Formatting looks inconsistent: Open Manage Rules and look for overlapping rules or an unexpected order. A cell’s visible style can be affected by more than one conditional-formatting rule.
Conditional formatting identifies matches according to Excel’s comparison behavior; it does not clean or merge records. If entries contain accidental spaces or inconsistent spelling, the rule may not group them as a person would. Fixing the underlying values first makes the result easier to trust. For a quick visual check, you can filter or sort the list after applying the rule, but avoid deleting marked cells until you have confirmed which record should remain.
Keep the rule useful as data changes
A rule limited to A2:A100 will not necessarily cover a new entry typed in A101. If the list is an Excel table, adding rows within the table can make the formatting easier to maintain, but check the rule’s Applies to range after adding data. For a normal range, update the rule when you extend the list, or choose a suitably sized range while avoiding unnecessary whole-column rules on large sheets.
Use a clear, restrained format so repeated entries are noticeable without making the sheet difficult to read. Conditional formatting is a visual aid, not a permanent mark: sorting, filtering, editing a value, or changing the rule can alter which cells are highlighted. If the review is important, keep a copy of the source data or document the cleanup decisions separately.
Frequently Asked Questions
Does Excel highlight the first duplicate too?
Yes. The built-in Duplicate Values rule highlights every occurrence in the selected range, including the first one. Use a running COUNTIF formula rule if you want to highlight only the second and later occurrences.
Can I highlight duplicates in one column only?
Yes. Select only the data cells in that column before applying Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This avoids comparing repeated values in unrelated columns.
Can Excel find duplicate rows based on two columns?
Yes. Use a formula-based rule with COUNTIFS to test both fields as a pair. Set the Applies to range to the cells or rows you want formatted, and make sure the formula’s references start on the first data row.
Does conditional formatting delete duplicate values?
No. It changes only the appearance of cells that meet the rule. To remove records, use a separate data-cleanup method and verify which occurrence to keep before deleting anything.
Why are cells highlighted when their contents look different?
Review the actual values and the rule’s range. Formula results may match even if the formulas differ, while spaces or inconsistent data can make visually similar entries compare differently. Manage Rules can also reveal another overlapping rule causing the format.
Related
- Create a DAX Measure in Excel: Step-by-Step
- County-Level Heat Map in Excel: How to Build One
- 4-Quadrant Scatter Plot in Excel: How to Create One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets: How to Duplicate ColumnsTo duplicate a column in Google Sheets, click its letter at the top, copy it with Ctrl+C (Command+C…
- Excel Named Range to Table: What Works and What Doesn’tExcel does not have a command that directly converts a named range into a table by name. Select…
- AI Data Validation Tools: Uses, Limits, and How to ChooseAI data validation tools help teams check whether data is complete, consistent, correctly formatted, and suitable for analytics…
- Remove All Formatting in Word Without Losing TextTo remove formatting from a Word document, save a copy, press Ctrl+A on Windows (Command+A on Mac), then…
- Freeze Panes in OpenOffice Calc: Steps and TipsTo freeze panes in OpenOffice Calc, select the cell just below the rows and just to the right…
- How to Switch Back to the Old Outlook View on WindowsTo switch back to the old Outlook on Windows, turn off the New Outlook toggle near the upper-right…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.