To highlight duplicate values in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style and select OK; Excel marks repeated entries in the selected range without deleting or changing any data.
What the Duplicate Values rule does
Conditional formatting changes how cells look when they meet a condition. Excel’s built-in Duplicate Values rule checks the selected range and applies a format to entries that occur more than once there. The rule is visual: it does not remove duplicates, combine records, or alter cell contents. You can still edit, sort, copy, or calculate with the values as usual.
The rule compares values across the range you selected. If you select A2:A100, Excel checks for repeats in that list. If you select A2:C100, it can highlight a value that appears more than once anywhere within those three columns, even if the two occurrences are in different columns. That distinction matters when each column represents a separate list: select and format each list independently if you only want within-column comparisons.
Excel also offers a choice between Duplicate and Unique in the rule dialog. Duplicate highlights values occurring more than once; Unique highlights values occurring exactly once in the selected range. The selected color or style is only a signal, so consider adding a legend or using an accessible color if other people will review the workbook.
Apply the built-in rule step by step
- Select the cells to check. For a list with a header in A1, select the data cells, such as A2:A100, rather than including the heading.
- On the ribbon, select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the dialog, confirm that Duplicate is selected. Choose a preset format or choose Custom Format to set a fill, font, or border.
- Select OK. Excel formats the matching cells immediately. Add or change entries in the range to see the formatting update.
On Excel for Mac, the equivalent command is available from Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values; ribbon layout can vary with window size and version. In Excel for the web, use the Home tab’s Conditional Formatting controls and choose the duplicate-values rule if available in your current interface. If the command is not visible, opening the workbook in the desktop app provides the full ribbon controls.
Choose the right range before formatting
The selected range defines the comparison. Selecting entire columns can be convenient, but it may include headings, notes, or unused cells and can make it harder to interpret the result. For a short customer list, selecting A2:A500 is usually clearer than selecting all of column A. If your data grows regularly, use an Excel table: select the data and choose Insert > Table, then apply the formatting to the table column. Table ranges generally expand when new rows are added, although it is wise to confirm the rule’s applied range after changing the table.
Be deliberate with a rectangular selection. Applying the rule to A2:C100 asks Excel to compare entries across all three columns together. For example, if A contains first names and B contains city names, a repeated word appearing once in each column may be treated as a repeated value in the selected area. To check duplicates in column A only, select column A’s data cells; repeat separately for B if needed.
Use a formula for more control
The built-in rule is the quickest option, but a formula-based rule gives you control over which occurrences count and which cells receive formatting. To highlight every repeated entry in A2:A100, select that range, then choose Home > Conditional Formatting > New Rule. Choose the option to use a formula to determine which cells to format, enter the formula below, choose a format, and confirm. With the list in A2:A100, the formula is:
=COUNTIF($A$2:$A$100,A2)>1
COUNTIF counts how many times the current cell’s value appears in the fixed list; the rule is true when that count exceeds one. The dollar signs keep the checked range fixed while the reference A2 adjusts for each row. If you instead want to mark only the second and later appearances—not the first—use this formula, with the same A2:A100 layout and selection:
=COUNTIF($A$2:A2,A2)>1
For the first row, the formula counts only through that row, so it is false. As the rule moves down the list, it checks the current value against the entries above and including itself. The first occurrence stays unformatted; subsequent occurrences are highlighted.
To shade an entire record when its ID in column A is repeated, select A2:F100 and use this formula, assuming the IDs are in A2:A100:
=COUNTIF($A$2:$A$100,$A2)>1
The reference to column A is fixed, while the row adjusts. Consequently, every cell across a record’s selected row gets formatted when its ID occurs more than once. Make sure the applied range begins on the same row as the formula’s first reference; a mismatch is a common cause of highlighting the wrong records.
Handle blanks, capitalization, and data quality
Blank cells can distract from a duplicate check, particularly when a range contains many empty rows. For a formula-based rule that ignores blanks, select A2:A100 and use:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)
This requires a nonblank current cell as well as a count greater than one. It does not clean the data; it simply avoids applying the format to empty cells.
COUNTIF comparisons are not case-sensitive, so values such as “North” and “north” are treated as matching for this kind of rule. If capitalization must distinguish entries, a case-sensitive formula can use EXACT. With data in A2:A100 and the rule applied to that same range, use:
=SUMPRODUCT(--EXACT($A$2:$A$100,A2))>1
EXACT compares text with case sensitivity, and SUMPRODUCT counts the matching results. Also check for less obvious differences: leading or trailing spaces, inconsistent punctuation, and numbers stored as text can make values that look alike behave differently. If results seem surprising, inspect the cell contents or use a cleaned helper column before deciding that the formatting rule is wrong.
Manage, edit, or remove a rule
To change a rule or check the cells it covers, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the Applies to range. A rule may be set to apply only to the original selection, so entries added outside that range will not be highlighted. Edit the range or recreate the rule for the intended cells, then apply the change.
Conditional formatting rules can overlap. If a cell meets more than one rule, the displayed result depends on rule order and settings. The rules manager lets you review the order and edit or delete rules. To remove only duplicate-value formatting, select the relevant cells and use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Avoid choosing the command that clears rules from the entire worksheet unless that is what you intend.
Highlighting is not the same as removing duplicates
Use conditional formatting when you want to review repeated entries while keeping all rows intact. To delete duplicate records instead, Excel has a separate Data > Remove Duplicates command. That operation changes the data by removing rows based on the columns you select, so make a copy of the data first if you may need to restore the original list. The built-in conditional-formatting rule is safer for investigation because it only changes appearance.
For a different way to inspect a list, a helper column with COUNTIF can display a count beside each entry. With names in A2:A100, put this in B2 and fill it down:
=COUNTIF($A$2:$A$100,A2)
A result of 1 means the value occurs once in the checked range; a result greater than 1 means it repeats. This makes counts explicit and can help when sorting or filtering, while conditional formatting is quicker when a visual scan is enough. Neither approach determines whether two records are truly duplicates in a business sense; that depends on which fields define a matching record.
Frequently Asked Questions
Does conditional formatting remove duplicate values?
No. It changes the appearance of cells that meet the rule. Use Data > Remove Duplicates only when you intend to delete repeated rows or values, and check which columns define a duplicate before running that command.
Can Excel highlight duplicates across two columns?
Yes. Select both columns’ data cells before applying Duplicate Values to compare values across the combined selection. If you want duplicates within each column separately, apply the rule to each column’s data range instead.
Why are some apparent duplicates not highlighted?
The cells may contain extra spaces, different punctuation, or different underlying values, such as a number stored as text in one cell. The built-in comparison is also case-insensitive, so capitalization alone is not a distinction. Check the actual contents and the rule’s Applies to range.
Will new entries be highlighted automatically?
Only if they fall inside the rule’s applied range. Check Home > Conditional Formatting > Manage Rules and extend the range if necessary. Using a table column can help the formatted area grow along with added table rows.
Can I highlight only the second occurrence?
Yes. Use a formula-based rule with =COUNTIF($A$2:A2,A2)>1, assuming the values are in A2:A100 and the rule applies starting at A2. This leaves the first instance unformatted and highlights later instances.
Related
- Excel Column Shortcuts for Selecting, Inserting and Hiding
- Excel Column Width: Units, Settings, and AutoFit
- Excel SWITCH Greater Than: Test Ranges and Conditions
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Address Book Cache: How to Clear It SafelyTo clear Outlook’s saved recipient suggestions in classic Outlook for Windows, go to File > Options > Mail…
- How to Double Space in Word for the WebTo double-space text in Word for the web, select the paragraphs you want to change, open Home >…
- Google Sheets: Count Cells With Specific Text in a RangeTo count cells that exactly match text in Google Sheets, use =COUNTIF(A2:A100,"Paid"); to count cells that contain the…
- Make a Flowchart in Word 2016: Steps, Layout, and TipsTo make a flowchart in Word 2016, choose Insert > Shapes, select flowchart shapes and connectors, then place,…
- Create Labels in WordPad: A Practical WorkaroundYou can make basic labels in WordPad by setting the page size and margins, then arranging text with…
- Excel Goal Seek Shortcut Key: Open and Use the ToolIn Excel for Microsoft 365 on Windows, press Alt+A, W, G in sequence to open Goal Seek; do…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.