기본 중복값 찾기
중복을 찾을 범위를 마우스로 드래그해 선택한다. 전체 열이 아니라 데이터가 있는 실제 범위만 선택하는 것이 좋다(빈 셀까지 포함하면 처리 속도가 느려질 수 있다).
리본의 '홈' 탭 > '스타일' 그룹 > '조건부 서식' > '셀 강조 규칙' > '중복 값'을 클릭한다. 대화상자가 뜨면 '중복'이 선택된 상태에서 원하는 서식(빨간 텍스트, 노란 채우기 등)을 고르고 확인을 누른다.
이 방법은 선택한 범위 '안에서' 같은 값이 2번 이상 나오면 전부 강조한다. 특정 값이 정확히 몇 번 나왔는지는 알려주지 않으므로, 개수까지 확인하려면 다음 블록의 COUNTIF 방식을 쓴다.
특정 열 기준으로만 중복 판단하기(수식 활용)
기본 중복 값 규칙은 선택 범위 전체를 기준으로 한다. 하지만 실무에서는 '이름'은 같아도 '부서'가 다르면 중복이 아닌 경우가 많다. 이럴 때는 수식을 직접 넣어야 한다.
조건부 서식 > 새 규칙 > '수식을 사용하여 서식을 지정할 셀 결정'을 선택한다. 수식 입력란에 예를 들어 A열 기준으로 =COUNTIF($A$2:$A$100,A2)>1 처럼 입력한다. 여기서 $A$2:$A$100은 실제 데이터 범위로, A2는 선택 영역의 첫 번째 셀 주소로 바꿔야 한다.
두 열의 조합(이름+부서)이 같을 때만 중복으로 보려면 COUNTIFS를 쓴다: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
범위를 절대참조($)로, 비교 대상 셀은 상대참조로 넣는 걸 헷갈리면 규칙이 엉뚱한 셀에 적용된다. 이 부분이 가장 자주 막히는 지점이니 수식 입력 후 몇 개 셀에서 결과가 맞는지 직접 확인하는 걸 권장한다.
숫자 이상값 찾기 - 기본 규칙
숫자 데이터에서 특별히 크거나 작은 값을 찾을 때는 조건부 서식 > '상위/하위 규칙'을 쓴다. '상위 10개 항목', '상위 10%', '평균 초과'/'평균 미만' 등의 옵션이 있다.
다만 이 방식은 '몇 등까지'를 미리 정해야 해서, 통계적으로 이상하다고 판단할 근거(표준편차 등)는 반영하지 않는다. 단순히 큰 값 몇 개를 보고 싶을 때만 쓰는 것이 적절하다.
통계 기준으로 이상값 잡기(표준편차/IQR)
평균에서 많이 벗어난 값을 찾으려면 평균과 표준편차를 이용한 수식 규칙을 쓴다. 예: 평균 ± 2표준편차를 벗어나는 값을 강조하려면 =ABS(A2-AVERAGE($A$2:$A$100))>2*STDEV($A$2:$A$100) 를 조건부 서식 수식으로 입력한다.
사분위수(IQR) 기준을 쓰고 싶으면 별도 셀에 QUARTILE 함수로 Q1, Q3을 미리 계산해 두고(예: E1에 =QUARTILE($A$2:$A$100,1), E2에 =QUARTILE($A$2:$A$100,3)), 조건부 서식 수식에서 =OR(A2<$E$1-1.5*($E$2-$E$1), A2>$E$2+1.5*($E$2-$E$1)) 형태로 참조한다.
2표준편차냐 1.5*IQR이냐는 데이터 성격과 분석 목적에 따라 달라지는 값이라 어느 쪽이 '맞다'고 단정할 수 없다. 회사 내부 기준이나 담당 부서의 분석 가이드라인이 있다면 그것을 따르고, 없다면 두 방식 결과를 비교해보고 더 합리적인 쪽을 쓰는 게 안전하다.
적용 후 막히는 문제들
규칙이 생각한 셀에 안 먹힐 때는 리본 > 조건부 서식 > '규칙 관리'를 열어 '적용 대상' 범위가 실제로 원하는 범위와 일치하는지 확인한다. 드래그하다 범위가 한 칸씩 밀리는 실수가 흔하다.
엑셀 표(Ctrl+T로 만든 표) 안에 조건부 서식을 넣으면 새로 추가되는 행에도 자동 적용되지만, 일반 범위에 넣으면 새 행은 규칙 밖에 있어 적용되지 않는다. 데이터가 계속 추가되는 표라면 표 기능으로 변환한 뒤 서식을 넣는 것이 유지보수에 편하다.
병합된 셀이 섞여 있으면 COUNTIF나 수식 기반 규칙이 예상과 다르게 동작할 수 있다. 병합 셀이 있는 범위는 병합을 해제한 뒤 작업하는 것을 권장한다.
여러 규칙을 겹쳐 넣으면 '규칙 관리'에서 위에 있는 규칙이 우선 적용된다(중지 옵션 체크 여부에 따라 다름). 강조 색이 이상하게 나온다면 규칙 순서와 'true인 경우 중지' 체크박스를 확인한다.