엑셀 조건부서식 규칙 만들고 고치는 방법

얼마 전 작업 현황표에서 ‘지연’된 건만 색으로 표시했는데, 새로 입력한 행에는 색이 안 들어왔습니다. 엑셀 조건부서식 규칙을 열어 보니 적용 범위가 기존 50행에서 끝나 있더군요. 조건부 서식은 한 번 만드는 것보다 범위와 기준을 제대로 관리하는 게 더 중요할 때가 많습니다.

먼저 색을 바꿀 셀과 기준을 정하세요

조건부 서식은 셀 값이 정해 둔 조건을 만족하면 색이나 글꼴을 자동으로 바꿉니다. 예를 들어 금액이 10만 원을 넘는 셀만 표시하려면 금액 범위를 선택하고 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 보다 큼을 누릅니다. 기준값에 100000을 입력하고 원하는 서식을 고르면 됩니다. 값이 바뀌면 표시도 따라 바뀝니다.

여기서 먼저 결정할 것은 ‘금액 셀만 칠할지’, ‘그 금액이 있는 행 전체를 칠할지’입니다. 앞의 경우에는 기본 메뉴만으로 충분합니다. 행 전체를 표시하려면 수식 규칙이 편합니다. 처음부터 시트 전체를 선택하기보다 실제 데이터가 들어 있는 범위를 잡으면 적용 대상을 확인하기도 쉽습니다.

행 전체를 칠할 때는 수식의 기준 셀을 맞추세요

A열부터 E열까지 작업표가 있고, E열에 ‘지연’, ‘진행’, ‘완료’가 적혀 있다고 가정해 볼게요. 제목 행을 제외한 A2:E101을 선택한 뒤 홈 → 조건부 서식 → 새 규칙으로 들어갑니다. ‘수식을 사용하여 서식을 지정할 셀 결정’을 선택하고 수식 칸에 =$E2="지연"을 입력합니다. 서식에서 채우기 색을 정한 다음 확인을 누르면 E열 값이 ‘지연’인 행에 색이 들어갑니다.

이 수식에서 $E는 판단 기준을 E열에 고정한다는 뜻입니다. 뒤의 2에는 달러 표시가 없으므로 3행에서는 E3, 4행에서는 E4를 확인합니다. 적용 범위의 첫 데이터 행이 2행인데 수식을 =$E3="지연"으로 쓰면 한 행씩 밀린 결과가 나옵니다. 색이 엉뚱한 곳에 나타날 때 가장 먼저 확인할 부분입니다.

마감일이 D열이고 완료 여부가 E열이라면 =AND($D2<TODAY(),$D2<>"",$E2<>"완료") 같은 규칙도 쓸 수 있습니다. 오늘보다 마감일이 앞서 있고, 날짜가 비어 있지 않으며, 완료되지 않은 행만 표시합니다. 다만 D열 값이 실제 날짜인지 먼저 확인하세요. 날짜처럼 보이는 텍스트는 비교 결과가 기대와 다를 수 있습니다.

색이 안 바뀌면 적용 대상과 규칙 순서를 보세요

홈 → 조건부 서식 → 규칙 관리를 열면 각 규칙의 조건, 적용 대상, 표시 형식을 볼 수 있습니다. 목록에 규칙이 없다면 ‘서식 규칙 표시’가 현재 선택 영역으로 되어 있는지 확인하고, 해당 워크시트로 바꿔 보세요. 규칙 자체가 없어진 게 아니라 선택한 셀에만 해당하는 규칙을 보고 있을 수 있습니다.

새 행에서만 색이 빠진다면 ‘적용 대상’을 확인합니다. 범위가 =$A$2:$E$50이면 51행부터는 규칙 밖입니다. 필요한 행까지 범위를 넓혀 주세요. 반대로 다른 열까지 색이 번졌다면 적용 범위를 너무 넓게 잡았을 가능성이 큽니다.

한 셀에 규칙이 여러 개 걸리면 규칙 관리 목록의 위쪽 규칙이 더 높은 우선순위를 가집니다. 같은 채우기 색을 서로 다르게 지정한 규칙이 겹칠 때는 순서를 조정해 보세요. ‘True일 경우 중지’를 켜면 그 규칙이 참인 셀에서는 아래 규칙의 평가를 멈춥니다. 중복된 색을 무조건 막으려고 켜기보다, 아래 규칙을 적용하지 않아야 할 때 사용하면 뜻이 분명합니다.

복사한 뒤에는 규칙이 늘어났는지도 확인하세요

서식이 들어 있는 셀을 여러 곳에 복사하면 비슷한 규칙이 여러 개 생길 수 있습니다. 이때 한 규칙만 고쳤는데 일부 셀의 색이 그대로라면 규칙 관리에서 적용 대상이 나뉘어 있는지 보세요. 같은 조건의 규칙을 계속 추가하기보다 기존 규칙의 범위를 고치는 편이 관리하기 편합니다. 수식 규칙을 다른 위치로 복사했다면 상대 참조가 옮겨진 위치에 맞게 바뀌었는지도 확인해야 합니다.

규칙이 필요 없어진 범위는 셀을 선택한 뒤 홈 → 조건부 서식 → 규칙 지우기에서 선택한 셀의 규칙을 지울 수 있습니다. 시트 전체의 규칙을 지우는 항목도 있으니 범위를 먼저 확인하는 게 좋습니다. 저는 색이 예상과 다르면 새 규칙부터 만들지 않고, 기존 규칙의 수식 첫 행과 적용 대상부터 봅니다. 대부분의 어긋남은 그 두 곳에서 금방 드러납니다.