The conditional format works great for a single column. If you want Excel to highlight only the copies, leaving the first occurrence of the value unaltered, enter the formula =COUNTIF($A$2:$A2, A2)>1 in step 4. The conditional format will highlight any value in column A thatвЂ™s repeated.

Select the remaining cells in the list (cells A3:A5 in the example worksheet).

With cell A2 still selected, click Format Painter.

At this point, the Conditional Formatting dialog box should resemble the following figure: For instance, click the Font tab and choose Red from the Color control and click OK.

Click the Format button and specify the appropriate format.

In the formula control, enter =COUNTIF(A:A,A2)>1.

Choose Formula Is from the first controlвЂ™s drop-down list.

Choose Conditional Formatting from the Format menu.

When applying this to your own worksheet, select the first data cell in the list (column).

Using the example worksheet, select cell A2.

Use conditional formatting to highlight duplicates in a single column as follows: At this point, you can replace the original recordset with the filtered list (the copied list) if you want to delete the duplicates.ДЇinding duplicates in a single column or across multiple columns is a bit more difficult.

Check Unique Records Only and click OK.Д®xcel will copy a filtered list of unique records to the range you specified in Copy To.

Enter a copy range in the Copy To control.

Select Copy To Another Location in the Action section.

From the Data menu, choose Filter and then select Advanced Filter to open the Advanced Filter dialog box.

To find duplicate records, use ExcelвЂ™s easy-to-use Filter feature as follows:

LEARN MORE: Office 365 Consumer pricing and features ThereвЂ™s no one feature or technique that will find duplicates in every case. Duplicates can occur within a single column, across multiple columns, or complete records. ThatвЂ™s because a duplicate is subjective to the context of its related data. In the duplicate world, definition means everything.

For more info, visit our Terms of Use page. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. You'll need more than one trick up your sleeve to find duplicates in Microsoft Excel.

Excel find duplicate values in range