엑셀 중복값 표시 색으로 찾고 행까지 확인하는 방법

얼마 전 부품 주문 내역을 맞춰 보는데 같은 주문번호가 두 줄에 들어가 있었습니다. 눈으로 훑을 때는 놓쳤는데, 엑셀에서 중복값에 색을 입히니 바로 보이더군요. 다만 중복을 찾는 기준을 잘못 잡으면 정상 주문까지 중복으로 표시됩니다. 먼저 무엇을 비교할지 정하는 게 중요합니다.

한 열의 중복값을 색으로 표시하는 방법

주문번호가 A열에 있고 1행은 제목, 실제 데이터는 2행부터 500행까지 있다고 가정하겠습니다. A2:A500을 선택한 뒤 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값으로 들어갑니다. 창에서 ‘중복’을 선택하고 채우기 색을 고른 다음 확인을 누르면 됩니다. 같은 주문번호가 두 번 나오면 첫 번째와 두 번째 셀 모두 색이 들어갑니다.

여기서 자주 하는 실수가 표 전체를 먼저 선택하는 겁니다. A열의 주문번호만 비교하려는데 A2:D500을 선택하면 제품명, 수량, 담당자 열의 값까지 선택 범위 안에서 함께 비교하게 됩니다. 예를 들어 여러 주문의 수량이 똑같이 ‘1’이라면 그 숫자도 중복으로 표시될 수 있습니다. 주문번호 중복을 찾는 목적이라면 주문번호 열만 선택하는 편이 정확합니다.

색은 데이터를 지우거나 값을 바꾸지 않습니다. 표시된 셀을 확인한 뒤 주문번호 옆의 날짜와 품목까지 봐야 실제 중복 입력인지 판단할 수 있습니다. 마이크로소프트의 엑셀 안내에서도 조건부 서식으로 중복을 먼저 확인한 다음 제거 여부를 판단하도록 설명합니다.

두 번째로 나온 값만 표시하려면

기본 중복 값 규칙은 같은 값이 나온 모든 셀을 칠합니다. 최초 입력은 그대로 두고 나중에 들어온 항목만 찾고 싶다면 수식 규칙이 더 편합니다. A2:A500을 선택하고 홈 → 조건부 서식 → 새 규칙에서 ‘수식을 사용하여 서식을 지정할 셀 결정’을 고릅니다. 수식에는 =AND(A2<>"",COUNTIF($A$2:A2,A2)>1)을 넣고 원하는 색을 지정합니다.

이 수식은 위에서부터 현재 행까지 같은 값이 몇 번 나왔는지 셉니다. A2에 ‘PC-104’가 처음 있으면 1회라서 색이 들어가지 않습니다. A17에 같은 번호가 다시 나오면 2회가 되어 A17만 표시됩니다. A열이 비어 있는 행은 앞부분의 빈칸 검사로 제외합니다. 데이터를 500행 넘게 추가했다면 규칙 관리에서 적용 범위와 수식의 끝 범위를 함께 늘려야 합니다.

중복된 주문의 행 전체에 색을 넣는 방법

주문번호만 칠하면 긴 표에서 날짜나 품목을 따라가기가 불편합니다. A열 주문번호를 기준으로 A열부터 D열까지 행 전체를 표시하려면 A2:D500을 선택해 새 수식 규칙을 만듭니다. 수식은 =AND($A2<>"",COUNTIF($A$2:$A$500,$A2)>1)입니다. 주문번호가 중복된 행은 A열부터 D열까지 같은 색으로 보입니다.

$A2의 달러 표시는 열 A를 고정한다는 뜻입니다. 그래서 B열이나 D열의 셀을 칠할 때도 해당 행의 주문번호를 확인합니다. 반면 행 번호 2는 고정하지 않아 규칙이 아래로 내려가면서 A3, A4를 차례로 봅니다. 이 부분을 $A$2로 고정하면 모든 행이 첫 주문번호만 검사하게 됩니다.

주문번호 하나가 아니라 ‘고객명과 주문일이 둘 다 같은 행’을 찾아야 할 때도 있습니다. 그 경우에는 비교 열이 두 개이므로 COUNTIFS로 조건을 각각 지정해야 합니다. 중복의 기준이 주문번호인지, 두 열의 조합인지 먼저 정한 뒤 규칙을 만드는 게 색을 잘못 해석하는 일을 줄여 줍니다.

색이 이상하게 나오거나 빠질 때 확인할 것

• 기대한 셀이 빠졌다면 앞뒤 공백부터 확인합니다. 겉으로는 같은 이름이어도 뒤에 공백이 붙어 있으면 다른 문자열일 수 있습니다.
• 복사해 온 주문번호는 숫자와 텍스트가 섞였는지 확인합니다. 셀 표시 형식만 바꾸는 것으로 저장된 값까지 항상 같아지는 것은 아닙니다.
• 새 행에 색이 안 들어오면 홈 → 조건부 서식 → 규칙 관리에서 적용 대상이 새 행까지 포함하는지 봅니다.
• 규칙을 여러 번 만들었다면 규칙 관리에서 중복된 규칙을 확인합니다. 색이 예상과 다르게 보이는 원인이 되기도 합니다.

특히 주문번호처럼 앞자리에 0이 들어가는 값은 처음부터 텍스트로 관리하는 편이 안전합니다. ‘00123’과 ‘123’을 같은 번호로 볼지 다른 번호로 볼지도 업무 기준에 따라 다릅니다. 서식을 적용하기 전에 원본 값을 몇 개 골라 직접 확인해 두면 색 표시 결과를 믿기 쉬워집니다.

중복값에 색이 들어왔다고 곧바로 데이터 탭의 ‘중복 제거’를 누르지는 않습니다. 그 기능은 선택한 기준에 맞는 중복 항목을 실제로 삭제합니다. 저는 먼저 색으로 후보를 찾고, 주문번호와 품목·날짜를 대조한 뒤 필요한 행만 처리합니다. 몇 줄 더 확인하는 시간이 잘못 지운 주문을 복구하는 시간보다 훨씬 짧았습니다.