Highlight Duplicate Values: How to Find Duplicates

Highlighting duplicate values means applying a visual marker (like a fill color or bold font) to cells or rows that appear more than once in a dataset. You can do this in Excel, Google Sheets, or other spreadsheet programs using built-in conditional formatting or a simple formula. This article explains the exact steps, the underlying logic, and common pitfalls so you can confidently identify and manage duplicates in your own data.

What Does “Highlight Duplicate Values” Mean?

When you highlight duplicates, you are essentially asking the spreadsheet to compare each cell (or row) against every other cell in a selected range and flag any that occur more than once. The result is a visual cue that immediately draws your eye to repeated entries. This is different from removing duplicates, which deletes the extra copies; highlighting keeps all data intact while making the repeats obvious.

For example, a list of customer email addresses might have the same address entered twice. Highlighting those cells lets you see both occurrences, so you can decide whether to merge, correct, or delete one of them. The same technique works for product IDs, invoice numbers, or any other field where duplicates cause problems.

Why You Would Want to Highlight Duplicates

Duplicate data can silently corrupt analysis, reporting, and mail merges. If you are counting unique customers, a duplicated row inflates the total. If you are sending a promotional email, a duplicate address means someone gets the message twice, which can hurt engagement. Highlighting duplicates is often the first step in data cleaning because it shows you exactly where the problem is without altering the original data.

Common use cases include:

• Checking that a list of transaction IDs has no repeats before reconciling accounts.
• Finding duplicate patient records in a healthcare database.
• Identifying repeated survey responses from the same IP address.
• Spotting duplicate SKUs in an inventory spreadsheet.

How to Highlight Duplicates in Excel

Microsoft Excel offers a built-in conditional formatting rule that does exactly this. Here are the steps for Excel 2016 and later (including Microsoft 365):

1. Select the range of cells you want to check. This can be a single column, multiple columns, or an entire table.
2. Go to the Home tab, click on Conditional Formatting, then choose Highlight Cells Rules and finally Duplicate Values.
3. In the dialog box, choose the formatting you want (for example, light red fill with dark red text). Click OK.

Excel will immediately apply the formatting to every cell that appears more than once in the selected range. If you select multiple columns, Excel checks each row as a whole—meaning a row is highlighted only if every cell in that row matches another row’s entire set of values. If you want to highlight duplicates based on a single column while including other columns in the selection, you need to use a formula-based rule (explained below).

How to Highlight Duplicates in Google Sheets

Google Sheets has a similar built-in option, though the menu path is slightly different:

1. Select the range of cells you want to check.
2. Click Format → Conditional formatting.
3. Under “Format cells if,” choose Custom formula is.
4. Enter the formula: =COUNTIF(A:A, A1)>1 (adjust the range to match your data).
5. Set the formatting style (e.g., yellow fill) and click Done.

The COUNTIF formula counts how many times the value in cell A1 appears in column A. If that count is greater than 1, the cell is highlighted. This method gives you more control than the built-in option because you can adjust the formula to look at a different column, ignore blanks, or apply to entire rows.

Using Formulas for More Control

Sometimes the built-in “Duplicate Values” rule is too blunt. For instance, you might want to highlight only the second and subsequent occurrences, leaving the first one untouched. Or you might want to highlight entire rows based on a duplicate in one column. In both cases, a custom formula is the answer.

To highlight the second and later occurrences in Excel, use this formula in a conditional formatting rule:

=COUNTIF($A$2:$A2, $A2)>1

Notice the range starts at $A$2 (the first data row) and ends at the current row (relative reference). This counts only the occurrences up to the current row, so the first occurrence gets a count of 1 and is not highlighted; the second gets a count of 2 and is highlighted, and so on.

To highlight entire rows based on a duplicate in column A, select the whole range (e.g., A2:C100) and use this formula:

=COUNTIF($A$2:$A$100, $A2)>1

This checks column A for each row and highlights the entire row if the value appears more than once.

Common Mistakes and How to Avoid Them

Even with a simple feature, there are pitfalls that can lead to incorrect results:

• Ignoring case sensitivity: Excel’s built-in rule treats “apple” and “Apple” as different. If you need case-insensitive matching, use a formula like =SUMPRODUCT(--(EXACT($A$2:$A$100, A2)))>1 for case-sensitive, or =COUNTIF($A$2:$A$100, A2)>1 for case-insensitive (COUNTIF is case-insensitive).
• Including header rows: If your data has a header, make sure it is not included in the selected range, or the header itself might be highlighted if it repeats.
• Blank cells: Blank cells are often counted as duplicates. If you have empty cells in your range, they will all be highlighted. Use a formula that ignores blanks, such as =AND(A2<>"", COUNTIF($A$2:$A$100, A2)>1).
• Leading or trailing spaces: “John ” and “John” look similar but are different to the spreadsheet. Use the TRIM function in a helper column to clean data first.

Alternatives to Highlighting

Highlighting is not the only way to deal with duplicates. Depending on your goal, you might consider:

• Removing duplicates: Excel has a “Remove Duplicates” button under the Data tab. This permanently deletes extra rows, which is useful when you are certain you want to keep only unique records.
• Counting duplicates: Use COUNTIF to create a helper column that shows how many times each value appears, then filter or sort by that column.
• Using PivotTables: A PivotTable can summarize data and show counts, making duplicates visible without any formatting.
• Power Query (Excel): For large datasets, Power Query can group by columns and count rows, giving you a clean table of duplicates.

Frequently Asked Questions

Why does my duplicate highlighting not work on numbers?

If your numbers are stored as text (often indicated by a green triangle in the corner of the cell), Excel may not recognize them as equal to numeric values. Convert text to numbers by selecting the column, clicking the warning icon, and choosing “Convert to Number.” Then reapply the highlighting.

How do I highlight duplicates across two columns?

To find duplicates between two separate columns (e.g., column A and column B), use a formula like =COUNTIF($B$2:$B$100, A2)>0 in a conditional formatting rule applied to column A. This highlights any cell in column A that appears anywhere in column B. For Google Sheets, the same formula works.

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

Yes. Select the row range and use a formula that counts across the row, such as =COUNTIF($A$1:$F$1, A$1)>1 for row 1. This highlights cells that repeat horizontally.

Does highlighting duplicates affect the data itself?

No. Conditional formatting only changes the visual appearance of cells. The underlying values remain unchanged. If you want to physically remove duplicates, use the “Remove Duplicates” feature or filter and delete rows manually.

What if I want to highlight unique values instead?

In Excel, use the same “Duplicate Values” rule but choose “Unique” from the dropdown in the dialog box. In Google Sheets, use the formula =COUNTIF(A:A, A1)=1 to highlight values that appear only once.

Why are all my blank cells being highlighted as duplicates?

Blank cells are treated as empty text, and all blanks are considered duplicates of each other. To exclude blanks, use a formula like =AND(A2<>"", COUNTIF($A$2:$A$100, A2)>1) in the conditional formatting rule.

Related

• Incogni vs DeleteMe: Which Data Removal Service Wins?
• Samsung SD Express MicroSD Cards Explained
• JBL Tune Live Headphones CES: What You Need to Know

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2