Highlight Duplicate Cells in Excel: A Simple Guide

Finding and highlighting duplicate entries in large Excel spreadsheets is crucial for efficient data management. For instance, if you’re managing a customer list for truck purchases at XE TẢI HÀ NỘI, identifying duplicate customers helps prevent errors, saves time, and improves overall productivity. This guide by XE TẢI HÀ NỘI provides clear, step-by-step instructions on how to effectively highlight duplicate cells in Excel.

How to Highlight Duplicate Cells in Excel

1. Using Conditional Formatting

This is the easiest and most common method.

Step 1: Select the data range you want to check for duplicates.

Step 2: Go to the Home tab, and in the Styles group, click Conditional Formatting.

Step 3: Select Highlight Cells Rules > Duplicate Values.

Step 4: In the Duplicate Values dialog box, choose the color or format you want to apply to the duplicate cells. You can choose a background color, font color, or border style.

Step 5: Click OK to finish.

2. Using a Formula

For more control over how duplicate cells are highlighted, use a formula.

Step 1: Select the first cell in the data range you want to check.

Step 2: Enter the following formula into the formula bar:

excel =COUNTIF($A$1:$A$10,A1)>1

Explanation:

• $A$1:$A$10: Represents the data range you want to check.
• A1: Represents the current cell.
• COUNTIF($A$1:$A$10,A1): Counts the number of times the value in cell A1 appears in the range $A$1:$A$10.
• >1: If the count is greater than 1, it indicates a duplicate.

Step 3: Drag the formula down to apply it to the entire data range.

Step 4: Go to the Home tab, and in the Styles group, click Conditional Formatting.

Step 5: Select New Rule.

Step 6: Select Use a formula to determine which cells to format.

Step 7: Enter the formula =$B1=TRUE in the Format values where this formula is true box (assuming you entered the formula from Step 2 in column B).

Step 8: Choose the format you want to apply to the duplicate cells.

Step 9: Click OK to finish.

Highlighting duplicate values in Excel using conditional formattingHighlighting duplicate values in Excel using a formula