Select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and select OK. Excel will highlight every occurrence of a value that appears more than once in the selected range, making repeated entries easier to spot without deleting or changing any data.
This guide is for Excel for Microsoft 365 on Windows. The built-in rule is quick to set up and updates when the selected cells change. It checks cell values, though, not whether entire rows are identical. If you need to check one column or highlight whole rows, use the tips and options below.
What You’ll Need
- An Excel workbook with the list or range you want to check.
- A clear idea of which cells should be compared. For example, select only the email-address column if you want to find repeated email addresses.
- Optional: a color or formatting style that will stand out from the existing sheet formatting.
You do not need to add a formula or sort the data to use Excel’s built-in duplicate rule. If the workbook is protected or opened read-only, you may need permission to change its formatting.
Step-by-Step
- Open the worksheet and identify the comparison range. Locate the cells that contain the values you want Excel to compare. If your list has a heading, leave the heading out of the selection. For example, if customer emails are in B2:B250 and B1 says Email, select B2:B250. Excluding the header prevents its text from being treated as part of the data.
- Select the cells. Click the first cell and drag to the last, or click the first cell, hold Shift, and click the last. You can also select a range by typing its address in the Name Box to the left of the formula bar, then pressing Enter. For the example above, type B2:B250. A focused selection makes the rule easier to understand and avoids flagging matching values in unrelated columns.
- Open the duplicate-values command. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This opens a small dialog where you can choose whether Excel should format duplicate or unique values. If you do not see the command, check that you are on the Home tab and that the workbook is editable.
- Keep Duplicate selected. In the dialog’s first list, choose Duplicate. The other choice, Unique, highlights values that occur only once; it does not mark repeated values. The duplicate option applies to the range you selected, so check the selection again if the result you want is limited to a particular column or block.
- Choose a format. Use the second list to select a preset, such as Light Red Fill with Dark Red Text, or choose Custom Format to set a fill, font, border, or number format. Pick a style that is visible against the sheet’s current colors. Conditional formatting changes how matching cells look; it does not replace their contents.
- Apply and inspect the results. Click OK. Excel highlights each occurrence of any value that appears at least twice in the selected range. For example, if the selected list contains the email address alex@example.com in rows 12 and 47, both cells are highlighted. The rule does not mark only the second occurrence: it formats all cells in a repeated group.
- Check whether the highlighted cells are true duplicates. Compare the displayed values and, where needed, inspect the formula bar. Extra spaces, numbers stored as text, or different underlying values can make entries look alike while Excel treats them differently. Conversely, values that differ only in letter case may be treated as duplicates by this built-in rule. Confirm the data before removing or correcting anything.
- Adjust or remove the rule if necessary. Select a cell in the formatted range, then choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the range shown in Applies to and edit the rule or its formatting. To remove it, select the rule and choose Delete Rule. You can also use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells to remove conditional formatting from the current selection without deleting the data.
In Excel for Mac, the Conditional Formatting commands are also on the Home tab, and the Duplicate Values option is under Highlight Cells Rules. Dialog details can vary slightly by release. In Excel for the web, use the Home tab’s Conditional Formatting menu and look for Highlight Cells Rules and Duplicate Values; available formatting controls may be more limited than in the desktop app.
Common Mistakes
Selecting too much or too little. If you select the whole worksheet or several unrelated columns, Excel compares values throughout that selection. A repeated number in a date column and an unrelated quantity column could both be highlighted. Select the smallest range that represents one meaningful comparison. If you want to compare two columns separately, apply a rule to each column rather than treating both as one list.
Expecting Excel to find duplicate rows. The built-in rule looks for repeated cell values. If two records share the same email address, it can highlight those email cells, but it does not decide that every field in two rows must match. For repeated records, first choose a reliable key, such as an order ID, or use a custom formula that checks the key column.
Thinking only the later entry will be marked. Excel highlights every occurrence in a duplicate group. That is useful for reviewing the whole group, but it is different from a process that flags only entries after the first one. Do not assume the first highlighted cell is the one to keep; check the records and your data-cleaning rules.
Deleting highlighted cells without reviewing them. A highlight is an alert, not a verdict. Two people may share a name, or two transactions may legitimately have the same amount. Verify the information and keep a backup before removing records. Conditional formatting itself does not delete duplicates.
Including the heading or using an old range. A heading can be formatted if it falls within the rule’s range, and new rows pasted below the range may not be checked. Select only the data, then review Applies to in Manage Rules after adding rows or moving the list.
Tips
Use a single identifying column when possible. A unique customer number, email address, or invoice ID is usually more useful than a name or amount. Highlighting duplicates in a key column makes it easier to judge whether records refer to the same item. If the key is not unique by design, choose a different field or a combination of fields.
Use a custom formula to highlight matching rows. For example, assume IDs are in A2:A100 and the records span A2:D100. Select A2:D100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1). Choose a format and confirm. The dollar signs keep the check in column A while the row reference changes for each selected row. If ID INV-104 appears in A2 and A8, the rule returns TRUE for both rows and formats the selected cells in those rows. Blank ID cells are excluded.
Make the rule easy to read. Choose a fill color that contrasts with the worksheet but does not obscure text. If the sheet already uses colored fills to communicate status, consider a border or a different font style instead. You can change the formatting later through Manage Rules.
Keep the check current. When you add entries, verify that the Applies to range includes them. For a regularly growing list, turning the data into an Excel table with Home > Format as Table can make it easier to manage rows, although you should still confirm that the conditional-formatting rule covers new table rows as expected.
Separate finding from cleanup. Highlighting helps you review; it does not tell you which record is correct. If you decide to remove duplicates, save a copy first and use Excel’s Remove Duplicates command only after choosing the relevant columns and confirming what should count as a duplicate.
Troubleshooting
No cells are highlighted: Confirm that Duplicate, rather than Unique, is selected and that the intended cells were selected before you created the rule. Open Manage Rules and check that Applies to covers the data. If the values only look identical, inspect for leading or trailing spaces, different punctuation, or numbers stored as text. Correcting inconsistent source data may be necessary before the values match.
Unexpected cells are highlighted: Check whether your selection includes other columns or unrelated values. The rule compares cells within the range, not just corresponding positions in rows. Also check whether entries differ only by capitalization; the built-in duplicate rule may treat those as the same. Narrow the range or use a custom formula when you need a specific comparison.
New rows are not checked: Select a cell in the rule’s range and open Home > Conditional Formatting > Manage Rules. Check the Applies to field and extend the range to include the added rows. If a table is being used, verify that the rule has expanded to cover its new rows.
The highlight disappears or looks different: Another conditional-formatting rule may take precedence, or the fill may be difficult to see against existing formatting. In Manage Rules, review the rules for the selected range and their order, then change the duplicate rule’s format. If the rule was cleared, recreate it using the Duplicate Values command.
The command is unavailable: Make sure the worksheet is not protected from formatting changes and that the file is not read-only. If you are using Excel for the web, try opening the workbook in the desktop app for more formatting controls. In a shared workbook, permission settings can also prevent edits.
Frequently Asked Questions
Does Excel highlight every copy or only the second one?
It highlights every occurrence of a value that appears more than once in the selected range, including the first occurrence. Use a custom formula if you specifically need to mark later occurrences only.
Can I highlight duplicates in just one column?
Yes. Select the data cells in that column, leaving out the heading, and apply the Duplicate Values rule. Excel will compare entries in that selection rather than searching the entire sheet.
Can I highlight duplicate rows?
The built-in rule highlights repeated cell values, not complete matching rows. You can highlight rows based on a duplicate ID with a custom formula, such as the one in the Tips section, or use a formula that checks multiple fields if the whole row must match.
Will the highlights update when I edit a value?
Yes. Conditional formatting recalculates as values in the rule’s range change. If you add cells outside that range, update Applies to so those new entries are included.
Does highlighting duplicates remove them?
No. It only changes their appearance. Review the highlighted records and decide what to keep; deleting or removing duplicates is a separate action.
Can I choose my own highlight color?
Yes. In the Duplicate Values dialog, choose Custom Format to set a fill, font, border, or number format. You can also edit the style later through Manage Rules.
Related
- COUNTA in Excel: Count Nonblank Cells for Data Analysis
- Power Query Excel: Aggregate Data into Summary Tables
- Count Cells by Color in Excel: Methods and Their Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Indent Within the Same Cell in Excel: Step-by-StepTo indent only one line within an Excel cell, insert a line break with Alt+Enter on Windows, then…
- Scatter Plot Worksheet: Build and Read One in ExcelA scatter plot worksheet organizes paired numerical data and displays each pair as a point, making it easier…
- Excel Keyboard Shortcuts: What You Can Customize in ExcelIn Excel for Microsoft 365 on Windows, you cannot directly remap most built-in keyboard shortcuts through an Excel…
- Excel Data Run Analysis: A Practical Runs TestExcel data run analysis checks whether values in a sequence appear in a meaningful order or are consistent…
- Box Plot in think-cell: What Works and How to Build Onethink-cell does not provide a dedicated box-and-whisker chart in its standard chart menu. In PowerPoint for Microsoft 365…
- Google Sheets Dropdowns From Named Ranges: How to Set UpYes. In Google Sheets, create a named range for your list, then use that name as the source…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.