To highlight duplicates in an Excel column, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and select OK. Excel marks repeated values without changing the underlying data, so you can review them while keeping the list intact. Before you start, decide whether to include the column heading and whether you want to highlight every occurrence or only later repeats.
What You’ll Need
- A workbook open in Microsoft Excel for Microsoft 365 on Windows. The steps are similar in other recent desktop versions.
- A column or cell range containing the values you want to check. The instructions below use A2:A100 as an example; substitute your own range.
- Optional: a preferred highlight color, especially if the sheet already uses color to convey other information.
You do not need to sort the data, delete anything, or add a helper column to use Excel’s built-in duplicate rule. Conditional formatting adds a visual cue and can update when cell values change. If you need to remove duplicates rather than identify them, use a different command; highlighting is the safer first step when you want to inspect the records before making changes.
Step-by-Step
- Identify the data range. Decide which cells should be compared. If A1 contains a heading such as “Email,” and the records run from A2 through A100, select A2:A100. Excluding the heading prevents it from being treated as a value in the list. If the column has no heading, include all cells that contain records. You can select a range by dragging over it, or click the Name Box to the left of the formula bar, type a range such as A2:A100, and press Enter.
- Select the cells to check. Click and drag over the data cells in the column. To select a whole column, click its letter at the top, but be aware that this includes the heading and empty cells. A defined range is often easier to control, particularly when the sheet contains notes or unrelated data farther down. If the list is an Excel table, select the data cells in the relevant table column rather than the header.
- Open the duplicate-values rule. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This opens a small dialog with a choice between Duplicate and Unique. The Duplicate choice is normally selected by default. Leave it selected to find values that occur more than once within the cells you selected.
- Choose a visible format. Use the format menu in the dialog to select one of the suggested formats, such as a colored fill with contrasting text. If you want a different appearance, choose Custom Format, then set options such as the fill color, font color, or font style in the formatting dialog. Choose a color that stands out from existing table styles and remains readable with the text.
- Apply the rule. Select OK in the Duplicate Values dialog. Excel applies the formatting to the selected range. Look through the column to confirm that the highlighted cells are the repeated values you meant to find. The formatting does not replace or edit the values, and you can still sort, filter, copy, or correct the data as usual.
- Check the rule’s range if you will add more records. Click a cell with the new formatting, then choose Home > Conditional Formatting > Manage Rules. In the rules manager, check that the rule applies to the intended cells. You can edit the “Applies to” range to include additional rows. In the Show formatting rules for menu, choose the relevant worksheet if the rule is not immediately visible.
- Decide whether to keep the highlighting. The rule remains active as you work, so values that become duplicates can be highlighted and values that stop being duplicates can lose the formatting. To remove the rule later, select the affected range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Choose Clear Rules from Entire Sheet only if you intend to remove conditional formatting rules throughout the worksheet.
Excel’s built-in Duplicate Values rule highlights all matching occurrences, not just the second and later copies. For example, if “North” appears three times in the selected range, all three cells are highlighted. This is useful when you want to see the complete group of matching entries. If you instead want to mark only the later occurrences, use a formula-based conditional formatting rule as described in Tips.
The comparison is limited to the selected range. If you select A2:A100, Excel does not compare those cells with values in column B or with data on another worksheet. Likewise, choosing a range that starts too low can leave out duplicates above it. Selecting the correct scope is therefore as important as choosing the rule itself.
Common Mistakes
- Including the header. A heading is data to Excel unless it is excluded from the selection. Start at the first record row, not the heading row, when you select a range manually.
- Selecting only part of the list. A value that appears once in the selected cells but also appears farther down the column will not be recognized as a duplicate if that later row is outside the rule’s range. Expand the selection to cover every row that should be compared.
- Expecting only the second copy to be marked. The built-in rule highlights every occurrence of a repeated value. If the first occurrence should remain unformatted, create a formula rule instead.
- Confusing highlighting with removal. Conditional formatting does not delete, combine, or correct records. Do not assume that a highlighted row is safe to remove; check the rest of its information before changing the data.
- Assuming similar-looking text is identical. Extra spaces, punctuation, spelling differences, and numbers stored as text can affect whether entries match. For instance, “Riley” and “Riley ” may look the same at a glance but contain different characters.
- Using an inconspicuous color. A pale fill may be hard to see against the existing worksheet style, while a strong font or fill color can make values difficult to read. Preview the format on your sheet and choose a legible contrast.
- Clearing more formatting than intended. The Clear Rules from Entire Sheet command removes rules beyond the cells you are reviewing. Use Clear Rules from Selected Cells when you only want to remove the duplicate rule from a particular range.
Tips
Use a formula when you need control over which copies are marked. For example, with the list in A2:A100, select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this formula:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)
This layout assumes the values are in A2:A100, with no heading included in the range. Choose Format, set the appearance, and select OK to create the rule. The formula excludes blank cells and highlights every nonblank value that occurs more than once. The dollar signs keep the checked range fixed, while A2 changes to A3, A4, and so on for each row. If your data is in another column or has a different number of rows, update both the selected range and the formula references so they match.
To highlight only later occurrences, use a count that grows from the first data row down to the current row. With values in A2:A100 and the rule applied to A2:A100, the formula is =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). The first occurrence has a count of one and stays unformatted; a later matching occurrence has a count greater than one and is highlighted. The range’s first cell must correspond to the first cell in “Applies to.” This method is useful for spotting repeat entries in order, but it does not change or delete them.
Make the rule easier to maintain. If you regularly append rows, consider converting the list to an Excel table: select a cell in the data and choose Insert > Table, confirming whether the table has headers. Tables can expand as you add records, although you should still check the conditional formatting range after adding rows. Another option is to extend the “Applies to” range in Manage Rules to cover a reasonable number of future rows.
Review how values are stored before treating them as errors. A repeated customer ID may be intentional if one person has several transactions. A repeated email address may need review, but the correct action depends on the context and the other fields in each row. Use the fill as a way to locate matches, not as proof that one of the records is wrong.
Troubleshooting
- Nothing is highlighted. Confirm that Duplicate, rather than Unique, was selected, and verify that the rule includes all intended data rows. Check that the values really repeat within the selected range. In Manage Rules, inspect the rule and its “Applies to” range. If the range is correct but similar entries are not matched, check for extra spaces, different punctuation, or values stored in different formats.
- Unexpected cells are highlighted. The rule may cover a larger area than intended, or it may include a heading or unrelated cells. Open Home > Conditional Formatting > Manage Rules, select the rule, and edit “Applies to.” Also confirm that you chose Duplicate and that the repeated values are genuinely duplicates for your purpose.
- Blank cells appear highlighted by a formula rule. Use a formula that explicitly excludes empty cells, such as the AND and COUNTIF example in Tips. Make sure the formula’s first reference matches the first cell in the selected range; a mismatch can make the formatting appear shifted by a row.
- The highlight does not show clearly. Another conditional formatting rule, a table style, or a similar fill color may make the result hard to distinguish. Change the format in Manage Rules or choose a different color. If multiple rules apply to the same cells, review their order and formatting options in the Rules Manager.
- New rows are not checked. The rule may not include the added rows. Open Manage Rules and expand the “Applies to” range, or create the rule again after selecting the full list. If the data is in a table, confirm that the new rows were added inside the table rather than below it as unrelated worksheet cells.
- The menu looks different. In Excel for Mac, the ribbon path is generally Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, though dialog layout can vary by release. In Excel for the web, select the range and look under Home > Conditional Formatting for the duplicate-values option; some web layouts expose rules differently from desktop Excel. If the built-in Duplicate Values command is not available in your version, create a formula-based conditional formatting rule if that option is supported.
Frequently Asked Questions
Does Excel highlight both copies of a duplicate?
Yes. The built-in Duplicate Values rule highlights every occurrence of a value that appears more than once in the selected range. Use a formula rule with a running COUNTIF range if you want to mark only the later occurrences.
Can I highlight duplicates in only one column?
Yes. Select the data cells in that column before creating the rule. Excel compares the selected cells with one another, rather than automatically checking the whole worksheet.
Does the rule update when I edit a value?
Yes. Conditional formatting recalculates as cell contents change. A cell may gain or lose its highlight when its value is edited, or when another matching value is added or removed from the rule’s range.
Can Excel find duplicates without matching capital letters?
The standard duplicate-values rule and COUNTIF comparison are not case-sensitive. Entries such as “Maple” and “MAPLE” are treated as matching for this purpose. If capitalization is meaningful in your data, you will need a different, case-sensitive approach.
Can I highlight duplicates across two columns?
You can select a rectangular range covering both columns, but that compares all selected cells together. If you need to compare a value in one column only with values in another, use a formula rule designed for that comparison instead of applying the standard rule to both columns.
How do I remove the highlighting without deleting values?
Select the cells, then choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the conditional formatting rule from the selection and leaves the cell contents unchanged.
Related
- Excel Row Count: Count All, Nonblank, or Visible Rows
- Switch Excel Data From Horizontal to Vertical
- Switch Excel Columns From Letters to Numbers?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- COUNTA in Excel: Count Nonblank Cells for Data AnalysisCOUNTA in Excel counts cells that are not empty, making it useful for checking how many entries a…
- Google Sheets Absolute Percentage: Formulas and ExamplesTo return the absolute value of a percentage in Google Sheets, use =ABS(A2); to calculate the size of…
- How to Double Space in Word and Google DocsTo double space text, select it and choose 2.0 line spacing: in Word, go to Home > Line…
- How to Delete a Blank Page in Microsoft WordTo delete a blank page in Microsoft Word, show formatting marks, find the paragraph mark or break that…
- Excel File Opening Blank? Causes, Checks, and FixesIf an Excel file opens to a blank screen, first check whether the workbook or its sheets are…
- Google Sheets: Count Cells That Contain Text ValuesTo count cells containing text in Google Sheets, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. It counts text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.