How to Find Duplicates in Excel

1. Select the range A1:C10.

Find Duplicates in Excel

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Duplicate Values…

Click Highlight Cells Rules, Duplicate Values

3. Select a formatting style and click OK.

Select a Formatting Style

Result. Excel highlights the duplicate names.

Duplicates

Note: select Unique from the first drop-down list to highlight the unique names.

As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), quadruplicates (if we have any), etc. Execute the following steps to highlight triplicates only.

4. First, clear the previous conditional formatting rule.

5. Select the range A1:C10.

6. On the Home tab, click Conditional Formatting, New Rule…

New Rule

7. Select “Use a formula to determine which cells to format”.

8. Enter the formula =COUNTIF($A$1:$C$10,A1)=3

9. Select a formatting style and click OK.

New Formatting Rule

Result. Excel highlights the triplicate names.

Triplicates

Explanation: =COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3, Excel formats the cell. Because we selected the range A1:C10 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF($A$1:$C$10,A2)=3, cell A3 =COUNTIF($A$1:$C$10,A3)=3, etc. Notice how we created an absolute reference ($A$1:$C$10) to fix this reference.

Note: you can use any formula you like. For example, use this formula =COUNTIF($A$1:$C$10,A1)>3 to highlight the names that occur more than 3 times.

This entry was posted in MS Excel. Bookmark the permalink.

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s