Highlight Duplicates in Excel Lists: Step-by-Step

To highlight repeated values in an Excel list, 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 every occurrence of a repeated value, making it easier to review duplicates without deleting or changing your data.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows. The same basic conditional-formatting feature is available in current Excel for Mac and Excel for the web, though menu details may differ slightly.
A list of values in a worksheet. For the clearest results, keep one type of information in each column, such as customer names in column A or invoice numbers in column B.
Optional: a copy of the workbook if you plan to reorganize or edit the source list. Highlighting duplicates only changes cell formatting, but later cleanup can change data.

Step-by-Step

Identify the range you want to check. Click the first data cell and note where the list ends. If your column has a heading such as Customer, do not include that heading in the selection unless you intentionally want Excel to compare it with the values below. For example, if the heading is in A1 and the entries run from A2 to A100, select A2:A100. A focused range keeps unrelated values elsewhere on the sheet from affecting the results.
Select the data cells. Drag over the list, or click the first cell, hold Shift, and click the last cell. You can also click the Name Box to the left of the formula bar, type a range such as A2:A100, and press Enter. Check that the selected range covers every entry you want to inspect and does not include neighboring columns by mistake.
Open the duplicate-values rule. On the Windows ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This opens a dialog for choosing whether to format duplicates or unique values. The default is usually Duplicate, which is the option you want when looking for repeated entries.
Choose the highlight style. In the dialog, leave the first list set to Duplicate. Use the format list to choose a preset, such as Light Red Fill with Dark Red Text, or choose Custom Format to specify a fill, font, or border. Select a style with enough contrast to remain readable against the worksheet’s existing colors.
Apply the rule and review the result. Select OK. Excel immediately highlights cells whose values appear more than once in the selected range. The rule marks all occurrences of a repeated value, not just the second and later appearances. For example, if the value Maple appears three times, all three Maple cells are highlighted.
Check that the rule covers the intended range. Click a formatted cell and open Home > Conditional Formatting > Manage Rules. In the rule manager, inspect the Applies to range. If the list may grow, change the range to cover likely new rows, such as =$A$2:$A$500, or use an Excel Table so new entries can be included as the table expands. Select Apply if you change the range, then close the manager.
Use a formula rule when blanks should stay unformatted. The built-in rule is quick, but a formula gives you control over blank cells and the exact comparison range. With values in A2:A8 and the rule applied to A2:A8, use this formula in a conditional-formatting rule: =AND(A2<>"",COUNTIF($A$2:$A$8,A2)>1). For example, if A2:A8 contains North, South, North, East, a blank, South, and West, the formula returns TRUE for the two North cells and the two South cells; it returns FALSE for the blank and the single-occurrence values. COUNTIF finds two matches for each repeated word, and the greater-than-one test flags those entries.
Create that custom rule if needed. Select A2:A8, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula from the previous step, choose Format to pick the appearance, and select OK. In the New Formatting Rule dialog, the cell reference A2 must match the top-left cell of the selected range. The dollar signs keep the COUNTIF search range fixed as Excel evaluates each row, while A2 changes to A3, A4, and so on.

On Excel for Mac, select the range and use the Home tab’s Conditional Formatting controls; the same Duplicate Values rule is available, although the dialog layout can differ by release. Excel for the web also supports conditional formatting, but its ribbon and rule-management options may be arranged differently. If you cannot find the exact menu wording, search the Home tab for Conditional Formatting and look for Duplicate Values or a formula-based new rule.

Common Mistakes

Including the header in the selected range. A header can be treated as another value. Usually select from the first row of data down, not from the column title.
Selecting several unrelated columns. A duplicate-values rule compares cell values throughout the selected range; it does not decide whether complete records or rows are identical. If you select names, dates, and amounts together, a repeated date or amount can be highlighted even when the records are not duplicates. Apply the basic rule to one column at a time when that is what you mean to compare.
Expecting Excel to highlight only later copies. The standard rule highlights every matching instance, including the first one. If you need to identify only the second and subsequent occurrences, use a formula rule designed for that purpose rather than assuming the built-in setting works that way.
Assuming similar-looking values are identical. A trailing space, a different punctuation mark, or a number stored as text can affect matching. For instance, “Lee” and “Lee ” may look alike but contain different characters.
Confusing highlighting with removal. Conditional formatting does not delete, merge, or correct entries. It changes how cells appear. Review the flagged values before taking any action.

Tips

Make the range easier to maintain. If you routinely add records, convert the list to an Excel Table: select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Table formatting and rules can expand with new rows, though it is still sensible to check the rule’s Applies to setting after adding data.
Use a consistent key. Decide what counts as a duplicate before applying a rule. A repeated first name may be expected, while a repeated email address or order ID may be meaningful. For records that are duplicates only when several fields match, checking a single column is not enough.
Choose a clear visual treatment. Use a fill or font color that stands out without making the text difficult to read. If the worksheet already has colored bands or status colors, choose a border or another distinct format so readers do not confuse the duplicate marker with existing meaning.
Keep the source data intact during review. Filter or sort the list after highlighting only if you understand how those actions affect the full records. Sorting one column alone can detach values from their associated rows; select the whole table or use its filter controls.
Remember that matching is generally case-insensitive. Excel’s built-in duplicate-values rule treats entries such as Maple and MAPLE as the same for this purpose. The COUNTIF formula shown above is also case-insensitive. If capitalization is significant, you need a different, case-sensitive formula approach.

Troubleshooting

Nothing is highlighted. Confirm that the selected range includes the repeated values and that the rule is set to Duplicate rather than Unique. Open Home > Conditional Formatting > Manage Rules and check that the rule is enabled and its Applies to range points to the right cells. If the data is on another sheet or the entries are not exact matches, correct the selection or inspect the values for spaces and formatting differences.
Cells you did not expect are highlighted. Check whether the selection includes other columns, a heading, or extra rows. The rule compares values within the selected range, so a value repeated in an unrelated field can be marked when multiple columns are included. Narrow the range to the specific list or column whose repeats matter.
Newly added entries are not highlighted. The conditional-formatting rule may stop at the range that was selected when you created it. Go to Manage Rules, select the rule, and extend Applies to through the new rows. Alternatively, use an Excel Table and verify that the rule follows the table’s data area.
The formula rule marks the wrong cells. Confirm that the formula’s first cell reference is the top-left cell of the Applies to range. For a range beginning at A2, the formula should start with A2, not A1 or A3. Keep dollar signs around the fixed search range, such as $A$2:$A$8, and make sure that range includes all entries being compared.
Spaces or inconsistent data prevent a match. Inspect the original cells in the formula bar and look for leading or trailing spaces, punctuation differences, or numbers stored as text. Clean a separate copy of the data before replacing source values. Avoid applying a cleanup operation to the original list until you have checked that it will not change meaningful content.
The highlight seems to disappear. Conditional formatting is dynamic: if you edit a value so it is no longer repeated, its highlight can turn off. Also check for another conditional-formatting rule that applies to the same cells and uses a competing format. Manage Rules lets you inspect overlapping rules and their ranges.

Frequently Asked Questions

Does Excel highlight every occurrence or only the extra copies?

The built-in Duplicate Values rule highlights every occurrence of each repeated value, including its first appearance. It does not remove duplicates. Use a custom formula if you need to mark only later occurrences.

Can I highlight duplicates in more than one column?

You can select multiple columns, but Excel compares values across the selected cells; it does not compare whole rows as complete records. To check whether full records repeat, define which columns form the record key and use a suitable formula or helper column.

Will blank cells be highlighted?

If blank cells should never be marked, use the formula rule with an explicit nonblank check, such as the AND condition in the guide. This also makes the intended blank-cell behavior clear when the list contains empty rows.

Can I change the highlight color later?

Yes. Open Home > Conditional Formatting > Manage Rules, select the duplicate rule, choose Edit Rule, and adjust its format. You can also delete the rule there without changing any cell values.

Does the rule update when I edit the list?

Yes. Excel recalculates conditional formatting as values change within the rule’s Applies to range. New rows outside that range will not be checked until you expand the range or include them in a table-based list.

Does Excel distinguish uppercase and lowercase?

No. The built-in duplicate rule and the COUNTIF formula shown here treat capitalization as equivalent, so Maple and MAPLE are considered a match. A case-sensitive comparison requires a different formula-based approach.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.