How to Show Duplicate Values with Conditional Formatting

Conditional formatting in Excel and Google Sheets can automatically highlight duplicate values in a range, making it easy to spot repeated entries in large datasets. This guide walks you through the steps for both applications, along with tips, common mistakes, and troubleshooting.

What You’ll Need

• Microsoft Excel 2010 or later, or Google Sheets (any recent version)
• A spreadsheet with a column or range of cells containing the data you want to check
• Basic familiarity with selecting cells and using menus

Step-by-Step

In Microsoft Excel

1. Select the range – Click and drag to highlight the cells you want to check for duplicates. Include headers if you want them excluded, but note that headers will be treated as data if included.
2. Open Conditional Formatting – On the Home tab, click Conditional Formatting in the Styles group.
3. Choose the rule – Hover over Highlight Cells Rules and click Duplicate Values.
4. Set the format – In the dialog box, choose a formatting style from the dropdown (e.g., Light Red Fill with Dark Red Text). You can also select Custom Format to pick your own fill color, font, or border.
5. Apply and review – Click OK. Duplicate values in your selected range will now be highlighted. To remove the rule, go back to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

In Google Sheets

1. Select the range – Highlight the cells you want to check.
2. Open the menu – Go to Format > Conditional formatting.
3. Set the rule – Under the Format cells if dropdown, choose Custom formula is.
4. Enter the formula – Type =COUNTIF(A:A, A1)>1 (adjust the column letter to match your data). This tells Sheets to highlight any cell that appears more than once in the column.
5. Choose a format – Click the paint roller icon to select a fill color, then click Done. The duplicates will be highlighted immediately.

For a range that spans multiple columns, use a formula like =COUNTIF($A$1:$C$100, A1)>1 to check the entire range.

Common Mistakes

• Including headers – If your header text appears elsewhere in the data, it will be flagged as a duplicate. Always exclude header rows from the selected range.
• Using absolute vs. relative references in Google Sheets – The formula =COUNTIF(A:A, A1)>1 uses a relative reference for the cell. If you apply it to a range starting at a different row, adjust the cell reference accordingly (e.g., =COUNTIF(B:B, B2)>1 for row 2).
• Case sensitivity – Excel’s default duplicate rule is case-insensitive (e.g., “apple” and “APPLE” are considered duplicates). Google Sheets’ COUNTIF is also case-insensitive. If you need case-sensitive matching, you’ll need a more complex formula.
• Overlapping rules – If you already have other conditional formatting rules, the duplicate rule might be overridden. Check the order of rules in the Conditional Formatting Rules Manager (Excel) or the list in Google Sheets.

Tips

• Use a distinct color – Choose a fill color that stands out from your data but is not too harsh. Light yellow or light red are common choices.
• Combine with filters – After highlighting, apply a filter to sort by color and review duplicates quickly.
• Count duplicates separately – If you want to know how many times each value repeats, use a helper column with =COUNTIF(A:A, A1) and filter for numbers greater than 1.
• Remove duplicates permanently – Use the Remove Duplicates feature (Excel: Data tab > Remove Duplicates; Sheets: Data > Data cleanup > Remove duplicates) after reviewing the highlighted cells.
• Apply to a table – If your data is in an Excel Table (Ctrl+T), the conditional formatting will automatically extend to new rows added to the table.

Troubleshooting

Duplicates are not highlighted at all

• Check that the range is selected correctly. If you selected a single cell, the rule applies only to that cell.
• Ensure the data is not formatted as text with extra spaces. Use =TRIM() to remove leading/trailing spaces in a helper column.
• In Excel, verify that the rule is not set to “Format only unique values” (the checkbox in the Duplicate Values dialog).
• In Google Sheets, ensure the formula range matches your data. For example, if your data is in column B, the formula should reference B:B, not A:A.

Only some duplicates are highlighted

• This often happens when the range includes blank cells. Blank cells are considered duplicates by Excel’s default rule. Exclude blank rows or use a formula that ignores blanks, like =AND(COUNTIF(A:A, A1)>1, A1<>"") in Sheets.
• Check for hidden rows or filtered data. Conditional formatting applies to all cells, but if rows are hidden, you may not see the highlight.

Highlighting appears in the wrong cells

• If you used a custom formula in Google Sheets, the formula’s relative references might be off. For a range starting at A1, use =COUNTIF(A:A, A1)>1. For a range starting at C5, use =COUNTIF(C:C, C5)>1.
• In Excel, if you used “Duplicate Values” on a multi-column selection, it treats the entire row as a duplicate only if all selected columns match. If you want to check a single column, select only that column.

Frequently Asked Questions

Can I highlight duplicates across multiple columns?

Yes. In Excel, select all columns you want to check; the rule will highlight rows where the entire selected row is duplicated. In Google Sheets, use a formula like =COUNTIF($A$1:$C$100, A1)>1 to check the whole range.

How do I highlight duplicates in a single column without affecting other columns?

Select only that column (e.g., click the column letter) before applying the rule. In Google Sheets, use a formula that references only that column, like =COUNTIF(A:A, A1)>1.

Does conditional formatting work with numbers stored as text?

Yes, but “1” (text) and 1 (number) are treated as different values. To avoid this, convert text to numbers using the VALUE function or the Text to Columns feature in Excel.

Can I highlight duplicates in a row instead of a column?

Yes. In Excel, select the row and apply the Duplicate Values rule – it will highlight cells that repeat across that row. In Google Sheets, use a formula like =COUNTIF($A$1:$Z$1, A1)>1 for a horizontal range.

Will the highlighting update if I add new data?

Yes, as long as the new data is within the range covered by the conditional formatting rule. If you add rows outside the selected range, you’ll need to extend the range or use an Excel Table.

Can I highlight duplicates with a custom color or icon?

Yes. In Excel, choose “Custom Format” in the Duplicate Values dialog to set any fill, font, or border. In Google Sheets, use the paint roller icon to select any fill color.

Related

• Green Ethernet Disconnecting Internet? Fix & Causes
• Hulu Shows to Watch in One Weekend
• Italian Sports Sedan Maintenance Costs Explained