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. Pick a format and select OK. Excel marks matching values in the selected range, making them easier to review without deleting or changing any data.
What You’ll Need
- An Excel workbook with the data you want to check.
- A clear idea of whether you want to compare one column, a range of cells, or combinations of values across columns.
- Optional: permission to edit the workbook, since you need to add a formatting rule. Saving a copy first is sensible if the workbook is important or shared.
Step-by-Step
- Open the workbook and identify the data range. Decide which cells should be compared. For example, to find repeated email addresses, select the email cells in that column, not the entire worksheet. If the column has a heading in row 1 and data from row 2 through row 200, select the data cells only. Including a heading can cause it to be treated as part of the values being checked.
- Select the cells to check. Click the first cell and drag to the last, or click the first cell, hold Shift, and click the last. You can also enter a range such as A2:A200 in the Name Box to the left of the formula bar, then press Enter. To select several separate ranges, hold Ctrl while selecting each range on Windows. On a Mac, hold Command instead. For the clearest results, start with one continuous range.
- Open the duplicate-value rule. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The Conditional Formatting menu contains rules that change how cells look based on their contents. You do not need to sort the data first, and the rule does not remove or move any rows.
- Choose which values to highlight. In the Duplicate Values dialog, use the left-hand list to choose Duplicate or Unique. Duplicate is normally selected by default. The rule compares the values in the range you selected; it does not compare entire records across multiple columns as a single combined record.
- Choose a visible format. The format list usually starts with Light Red Fill with Dark Red Text. You can choose another preset, or select Custom Format to set a fill, font, border, or number format. Pick a style that stands out but remains readable, especially if the sheet already uses color to convey other information.
- Apply the rule. Select OK in the dialog. Excel formats matching values in the selected range. If a value appears twice or more, each matching occurrence is highlighted, including the first one—not just the later entries. The underlying cell contents remain unchanged.
- Check the result against a few known examples. Look at a value you know appears more than once and one you believe appears only once. Confirm that the rule covers the intended rows and that the highlight is visible. If the sheet has filters, remember that hidden rows can still be included in the rule’s comparison range.
- Review or adjust the rule later. Select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, check the rule’s Applies to range, edit its format, or delete it. If more than one rule applies, use the manager to inspect their order and settings. In some Excel versions, choose This Worksheet in the Show formatting rules for list to see all rules on the current sheet.
To highlight only later occurrences: the built-in Duplicate Values rule highlights every instance, including the first. For a rule that leaves the first instance unformatted, use a formula-based conditional formatting rule. With values in A2:A100, select A2:A100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =COUNTIF($A$2:A2,A2)>1. Choose a format and select OK. This counts each value from the top of the range to the current row, so only its second and subsequent appearances meet the condition. If your data starts on a different row, change the formula references to match the first selected cell.
To compare a two-column combination: for example, if column A contains first names and column B contains last names, you may want to flag repeated name pairs rather than repeated names in either column alone. With the records in A2:B100, select A2:B100 and create a formula-based rule using =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. Choose a format and apply it. The rule highlights both cells in each row whose A-and-B combination occurs more than once. Adjust the ranges and column references to fit your sheet.
Common Mistakes
- Selecting too much or too little. A rule applied to a whole column can include headings, notes, or unrelated values. A rule applied to only part of a list can miss duplicates outside that selection. Check the Applies to range in Manage Rules.
- Expecting the first copy to stay unformatted. The built-in Duplicate Values option marks all occurrences. Use the formula-based method above if you want to distinguish the first appearance from later ones.
- Checking one column when you mean to check a record. Selecting a table does not automatically make Excel compare each whole row as one unique record. The built-in rule compares individual cell values. Use a suitable multi-column formula rule when a combination of fields defines a duplicate.
- Assuming a highlight means the records are identical in every respect. A duplicate rule only checks the selected values or, with a custom formula, the fields specified in that formula. It does not validate related columns, spelling, or whether two records should actually be considered duplicates.
- Deleting highlighted cells without checking the whole row. A highlighted cell is a signal to investigate, not an instruction to delete. If you remove a repeated entry, select and review the entire record so you do not leave mismatched information behind.
Tips
- Use a specific range for a large sheet. Selecting only the rows that contain data can make the rule easier to manage and avoid including future notes or unrelated content. If new rows are added frequently, check whether the Applies to range includes them.
- Sort or filter after highlighting if that helps your review. Conditional formatting identifies matches but does not group them. You can sort or filter the data to bring related entries together; keep the full table selected when sorting so values from different columns stay on the correct rows.
- Choose a format that works with the rest of the workbook. A strong fill color may obscure existing status colors. A font color or border can be a better choice when the sheet already relies on color coding.
- Remember that duplicate matching is generally not case-sensitive. Values such as North and NORTH are normally treated as matches by Excel’s duplicate-value rule and by COUNTIF or COUNTIFS. If capitalization is meaningful in your data, review those cases separately or build a more specific approach.
- Keep an unchanged copy of the data before cleanup. Highlighting is reversible, but deleting rows or editing values may not be. A backup lets you compare the original list if a suspected duplicate turns out to be a valid separate record.
Troubleshooting
Nothing is highlighted. Confirm that Duplicate is selected in the dialog, not Unique, and check that the rule’s Applies to range covers the data. Also confirm that the values really match. Extra spaces, different punctuation, or a number stored as text in one cell and as a number in another can make entries look alike without being identical for the rule. Inspect the formula bar or compare the underlying cell contents.
Too many cells are highlighted. The selected range may include a heading, blank areas, or unrelated columns. Open Home > Conditional Formatting > Manage Rules and narrow the Applies to range. If you created a formula rule, confirm that its references match the first row and the intended columns. A relative reference that shifted incorrectly can make a rule behave unexpectedly.
The highlight appears on only some repeated entries. Look for inconsistent spaces or characters, and verify that every row is inside the rule’s range. If rows were added after the rule was created, they may not be included. Update the range in Manage Rules or recreate the rule using the complete data range.
The color does not change when you edit a cell. Conditional formatting is automatic, but another rule may override its appearance or the cell may be outside the Applies to range. In Manage Rules, inspect the rules for that range and their order. You can also remove a rule and create it again if its settings are unclear. On Excel for the web, basic conditional formatting is available, but some rule-management options can differ from the desktop app; open the workbook in Excel for Microsoft 365 on Windows or Mac for more detailed editing.
The formatting is hard to see or prints poorly. Reopen the duplicate-value dialog or edit the rule through Manage Rules and choose a higher-contrast format. If color printing is not reliable, use a font or border change as well as, or instead of, a fill.
Frequently Asked Questions
Does Excel highlight every copy or only the second one?
The built-in Duplicate Values rule highlights every occurrence of a repeated value, including the first. Use the formula-based COUNTIF rule in the steps above to highlight only later occurrences.
Can I highlight duplicates across multiple columns?
Yes, but decide what counts as a duplicate first. The built-in rule checks individual cell values in the selected range. To flag rows where a pair of fields repeats, use the COUNTIFS example above and adjust it for the columns in your data.
Will highlighting duplicates delete or change my data?
No. Conditional formatting changes how cells look; it does not edit values or remove records. You can clear or edit the formatting rule without changing the cell contents.
Can I highlight duplicates in an Excel table?
Yes. Select the table column or cells you want to compare and apply the same rule. When the table grows, check the rule’s Applies to range to make sure newly added rows are included. Table behavior can depend on how the rule was originally applied.
Does Excel treat uppercase and lowercase text as different?
Usually not for the built-in duplicate rule: text that differs only by capitalization is treated as a match. If capitalization distinguishes valid values in your data, do not rely on this rule alone; inspect those results with a case-sensitive method.
Can I use the same steps on a Mac?
Excel for Mac also has conditional formatting and a Duplicate Values rule, though dialog layouts can differ slightly. Use the Home tab and Conditional Formatting menu, then choose the duplicate-value option. For keyboard selection shortcuts, use Command where the Windows instructions use Ctrl.
Related
- Aggregate Excel Rows by Category, Product, or Date
- Excel SUBTOTAL: Does It Ignore Errors in Your Data?
- Excel SUBTOTAL AVERAGE: Average Filtered Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Highlight Duplicates in an Excel Row with RulesTo highlight repeated values in one Excel row, select the cells, then choose Home > Conditional Formatting >…
- Box Plot in Excel Without Outliers: Hide or Exclude?To hide outlier markers in an Excel box plot, select the chart, right-click a data series, choose Format…
- Excel Freeze Panes: Change Them While Scrolling?No—Excel does not change the Freeze Panes boundary automatically as you scroll. To move it, unfreeze the current…
- Adding a Header in Word: Steps and OptionsTo add a header in Microsoft Word for Microsoft 365 on Windows, choose Insert > Header, select a…
- How to Make a Booklet in Word 2016: Setup and PrintTo make a booklet in Word 2016, open Layout > Margins > Custom Margins, set Multiple pages to…
- How to Clear Browser Cache in Outlook on the Web SafelyTo clear the browser cache for Outlook on the web, open your browser’s privacy settings and delete cached…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.