엑셀 조건부서식 중복을 표시하는 방법

얼마 전 정비 예약표를 확인하다가 같은 차량번호가 두 번 들어간 줄 알고 한 행을 지울 뻔했습니다. 확인해 보니 한 건은 오전 입고, 다른 한 건은 오후 재방문 기록이었죠. 엑셀에서 중복을 찾을 때는 색을 칠하는 방법만큼이나 무엇을 중복으로 볼지 먼저 정하는 일이 중요합니다.

엑셀 조건부서식 중복 기능은 원본 데이터를 지우지 않고 의심스러운 값을 눈에 띄게 표시합니다. 차량번호, 부품번호, 거래처명처럼 반복 여부를 빠르게 확인해야 하는 목록에 특히 편리합니다.

같은 값이 두 번 이상 나오면 색으로 표시하기

차량번호가 A열에 있고 1행이 제목이라면 A2부터 실제 데이터의 마지막 셀까지 선택합니다. 이어서 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값을 누르고 원하는 색을 고르면 됩니다. Microsoft의 엑셀 안내에도 소개된 기본 방식으로, 선택한 범위 안에서 같은 값이 두 번 이상 나타나면 해당 셀들이 함께 강조됩니다.

예를 들어 A2와 A18에 같은 차량번호가 있다면 두 셀 모두 색이 들어옵니다. 먼저 입력된 A2를 남기고 나중에 입력된 A18만 표시하는 기능은 아닙니다. 예약표 300행을 검사한다면 제목을 제외한 A2:A301을 선택해야 하며, A열 전체를 무심코 선택하면 제목이나 아직 쓰지 않은 영역까지 규칙에 포함될 수 있습니다.

색은 판별 결과를 보여줄 뿐입니다. 두 건이 실제로 같은 예약인지, 같은 차량의 별도 방문인지는 접수 시간과 작업 내용을 보고 판단해야 합니다. 같은 값이 반복되는 것 자체가 오류인 부품 관리번호와, 여러 번 등장할 수 있는 차량번호는 기준이 다르기 때문입니다.

두 번째 입력부터 표시하려면 수식을 쓰기

새로 들어온 중복만 찾고 싶다면 A2:A301을 선택한 뒤 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정으로 들어갑니다. 수식 칸에 =AND(A2<>"",COUNTIF($A$2:A2,A2)>1)을 입력하고 채우기 색을 지정합니다.

이 수식은 현재 행까지 같은 차량번호가 몇 번 나왔는지 셉니다. A2에서는 범위가 A2까지라 첫 기록은 색이 없고, 같은 번호가 A18에서 다시 나오면 그때부터 색이 들어옵니다. $A$2는 검사 시작점을 고정하고, 뒤의 A2는 행을 따라 내려갑니다. 앞의 A2<>"" 조건은 빈 셀을 제외하려는 장치입니다.

목록의 끝 행이 500이라면 선택 범위도 A2:A500으로 바꾸면 됩니다. 규칙을 만든 뒤에는 조건부 서식 → 규칙 관리에서 적용 대상이 의도한 범위인지 확인하세요. 특히 규칙을 다른 열로 복사했을 때 참조 위치가 달라지면 예상하지 못한 셀에 색이 들어올 수 있습니다.

차량번호가 같은 행 전체를 강조하기

차량번호만 빨갛게 보이면 입고일이나 고객명을 확인하려고 시선을 옮겨야 합니다. A열이 차량번호, B열이 입고일, C열이 고객명, D열이 작업 내용이라면 A2:D301 전체를 선택해 새 규칙을 만들고 =AND($A2<>"",COUNTIF($A$2:$A$301,$A2)>1)을 입력합니다.

여기서는 $A2가 중요합니다. 규칙이 B열이나 D열의 셀에 적용될 때도 같은 행의 A열 차량번호를 기준으로 검사하게 해줍니다. 반면 $A$2:$A$301은 비교할 전체 목록을 고정합니다. 이 규칙은 첫 번째 기록을 포함해 같은 번호가 있는 모든 행에 색을 칠하므로, 두 기록의 날짜와 작업 내용을 나란히 비교하기 좋습니다.

같은 차량번호와 같은 입고일이 겹칠 때만 확인하고 싶다면 A2:D301에 =AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A$301,$A2,$B$2:$B$301,$B2)>1)을 적용할 수 있습니다. COUNTIFS는 두 조건을 동시에 만족하는 행을 셉니다. 다만 하루에 같은 차량이 두 번 입고될 수 있는 업무라면 날짜만으로 중복 접수를 확정하지 말고 시간이나 접수번호도 함께 확인해야 합니다.

색이 이상하게 들어올 때 확인할 것

먼저 선택 범위를 봅니다. A2:A301을 검사할 생각이었는데 A2:A30에만 규칙이 적용됐다면 아래쪽 기록은 아무리 같아도 표시되지 않습니다. 반대로 수식의 첫 행을 A2로 적어 놓고 적용 범위를 A3부터 시작하면 검사 기준이 한 행씩 어긋날 수 있습니다. 규칙 관리 화면의 적용 대상과 수식에 적힌 첫 행을 맞춰야 합니다.

겉보기에는 같은 차량번호인데 색이 들어오지 않는다면 입력값도 확인하세요. 복사해 온 데이터에는 앞뒤 공백이 붙어 있을 수 있습니다. 이런 경우에는 보조 열에 =TRIM(A2)을 넣어 값을 확인한 뒤, 다듬은 열을 기준으로 중복 규칙을 적용하는 편이 안전합니다. 차량번호의 띄어쓰기나 표기 방식 자체가 다르면 별도의 입력 기준도 필요합니다.

조건부 서식은 중복을 표시해 줄 뿐 기록을 삭제하지 않습니다. 중복 제거 명령을 누르기 전에는 재방문, 예약 변경, 부품 재주문처럼 반복될 이유가 있는지 살펴보는 게 좋습니다. 저는 접수표에서 의심되는 행에 먼저 색을 입히고, 접수번호와 시간을 확인한 다음 수정 여부를 결정하는 방식이 가장 실수를 줄여 준다고 봅니다.