HIGHLIGHT DUPLICATES IN EXCEL

Finding duplicate values in the same column is easy; you can sort or apply a filter depending on the circumstances. Finding duplicates that span multiple columns is a tad more difficult. A sort can work, but then you have to find the duplicate values. So while it’s better than no solution at all, it’s not a good solution. You probably want a solution that kind of screams out at you Here I am! I’m a duplicate!

Let’s take a quick look at a simple example. The sheet below contains a column of dates and a column of initials. A few dates are repeated and a few initial sets are repeated; they represent duplicates within those columns. However, we’re interested in records that repeat the same date and the same initials. That’s what I mean by a multi-column duplicate. It’s fairly easy to spot the duplicates, rows 8 and 9, in such a simple sheet, but what if you had hundreds or thousands of rows to check?

An AutoFilter will work, but it’s a vulnerable solution. In this case, there are five distinct dates. That means a user has to review at least five sets of records to find duplicates. Even then, you have to trust your user to actually spot them. It’s not a good solution, especially if you have lots of data.

You might try an advanced filter or even conditional formatting, but both would require so much hoop-jumping that I’m not sure the end result would be worth the fuss, when compared to the easy-to-implement solution that follows:

  • Concatenate the columns you’re checking.

  • Use CountIf() to count the number of combined values.

Let’s enhance the sheet above to see how this works:

  1. In cell C2 enter the formula =A2&B2. (You can combine more columns.)

  2. Copy the formula to C3:C9. Excel uses each date’s serial value, but that won’t interfere with the technique. However,  if your values contain times, it might, depending on how the time values were entered.

  1. In cell D2 enter the following formula:

    =IF(COUNTIF($C$2:C2,C2)>1, “Here I am! I’m a duplicate!”,”Original”)

  2. Copy the formula to D3:D9. At this point, finding multi-column duplicates is as easy as sorting by column D (although this example doesn’t require any sorting).

The IfCount() function counts the number of times the concatenated values occur within the extending range. If the count is greater than 1, the formula returns the string “Here I am! I’m a duplicate!”; when the count isn’t greater than 1, the formula returns the string “Original.” Only the first occurrence will be identified as Original.

Source: Website                                                                     

Replies (1)

THIS PAINFULL EXCERCISE NEEDS ONLY FOR MS OFFICE 2003 VERSIONS OR PRIOR . IN EXCEL 2007 THIS CAN BE DONE JUST BY CLICKING 1 BUTTON AFTER SELECTING CLOUMN AND CLICKING ON CONDITIONAL FORMATTING HIGHLITING AND ON DUPLICATES.yOU CAN ALSO DELETE THEM .

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
ARTICLESHIP 17 November 2026
Vacancy for Articleship

Promptitude Accounting Services Private limited

Bengaluru

B.Com

View Details
Company
ARTICLESHIP 29 October 2026
Article Assistant (CA Inter / CMA Inter) - Thrissur

Varma Raja & Associates

Thrissur

CA Inter

View Details
Company
Featured ARTICLESHIP 31 October 2026
CA Articleship

apricus india

Navi Mumbai

CA Inter

View Details
Company
ARTICLESHIP 25 October 2026
Article Intern (CA Articleship)

Arpan Bohra & Co

Hyderabad

CA Inter

View Details
Company
ARTICLESHIP 20 November 2026
Article clerk

Jain Sanjay Kumar & Co

Jaipur

CA Inter

View Details
Company
20 November 2026
Senior Accountant

Compass Tex India Pvt Ltd

Coimbatore

CA Inter

View Details
Company
ARTICLESHIP 23 October 2026
CA Articleship / Article Assistant

M N Rao & Associates LLP

Vijayawada

CA Inter

View Details
Company
12 November 2026
Auditor

Oz Taxation

Vijayawada

Graduate (Any)

View Details