How to Highlight Duplicate in Excel

Highlighting duplicates in Excel is a useful feature that allows you to identify and visually distinguish duplicate values or data entries in your spreadsheet. 

Whether you are working with a list of names, numbers, or any other type of data, Excel provides several methods to highlight duplicates. 

In this comprehensive guide, we will cover various techniques to highlight duplicates in Excel in detail, including conditional formatting and formula-based approaches

See also: How to Enable Macros in Excel

Method 1: Highlighting Duplicates with Conditional Formatting 

Conditional Formatting is a built-in feature in Excel that allows you to apply formatting to cells based on specified conditions. You can use this feature to highlight duplicates by following these steps:

Step 1: Select the range of cells in which you want to identify duplicates. For example, if you have a list of names in column A from A1 to A100, select that range.

Step 2: Go to the Home tab on the Excel ribbon, and in the Styles group, click on Conditional Formatting. A drop-down menu will appear with various options.

Step 3: In the Conditional Formatting menu, hover over the “Highlight Cells Rules” option to expand the sub-menu, and then select “Duplicate Values.”

Step 4: In the Duplicate Values dialog box that appears, you can customize the settings for highlighting duplicates:

• Duplicate: By default, “Duplicate” is selected, which means Excel will highlight all duplicates in the selected range.

• Unique: If you want to highlight unique values instead of duplicates, you can choose the “Unique” option.

• Format: You can choose the format for highlighting duplicates by clicking on the Format button. This will open the Format Cells dialog box, where you can choose the desired formatting, such as font color, fill color, etc.

• Apply Formatting To: You can choose whether to highlight duplicates in the cell or in the entire row by selecting the appropriate option from the drop-down menu.

Step 5: Click OK Once you have configured the settings, click OK in the Duplicate Values dialog box to apply the conditional formatting rule. Excel will now highlight all the duplicate values in the selected range based on the formatting settings you chose.

See also: How to Add Check Boxes to Word Documents

Method 2: Highlighting Duplicates with Formula-Based Approach 

You can also use Excel formulas to identify duplicates and apply formatting based on the results. Here’s how you can do it:

Step 1: Insert a new column next to the column that contains the data in which you want to identify duplicates. For example, if you have a list of numbers in column A, you can insert a new column B for the formula-based approach.

Step 2: In the first cell of the new column, enter a formula to compare the value in the corresponding cell of the original column with the values in the other cells of the same column. 

For example, if you want to compare the value in cell A2 with the values in the range A1:A100, you can use the following formula in cell B2: =COUNTIF($A$1:$A$100, A2)>1

This formula uses the COUNTIF function to count the occurrences of the value in cell A2 in the range A1:A100. 

If the count is greater than 1, it means the value is a duplicate, and the formula will return TRUE, otherwise, it will return FALSE.

Step 3: Select the range of cells in the Helper Column that contain the formulas, and then go to the “Conditional Formatting” button on the “Home” tab of the Excel ribbon. Choose “Highlight Cells Rules” and then “Duplicate Values.”

Step 4: In the “Duplicate Values” dialog box, you can choose the formatting options you want to apply to the duplicates. For example, you can select a fill color, font color, or apply a predefined format. You can also choose whether to highlight only the first occurrence of the duplicate or all occurrences.

Step 5: Once you have configured the duplicate values rule, click on the “OK” button. Excel will automatically highlight the duplicate values in the Helper Column according to the formatting options you have chosen.

Step 6: After you have highlighted the duplicates, you may choose to clean up the Helper Column by hiding it or deleting the formulas. Keep in mind that deleting the formulas will remove the duplicate identification, so only do this if you no longer need the Helper Column.

That’s it! Using this formula-based approach, you can easily highlight duplicates in Excel and visually identify them within your data set. This can be helpful for data analysis, data cleaning, and identifying inconsistencies in your data.

See also: How to Track Changes in Google Doc

Originally posted on April 14, 2023 @ 2:24 pm