엑셀 · 실무팁

엑셀 조건부 서식 사용법 총정리, 6가지 규칙부터 수식까지

엑셀 조건부 서식은 조건에 맞는 셀을 자동으로 찾아 색을 바꿔주는 기능이다. 매출 실적표에서 목표치 미달인 지점만 눈으로 찾아본 적 있는가. 행이 몇십 개면 어떻게든 되지만 수백 개를 넘어가면 이야기가 다르다. 색연필로 표시하듯 셀 하나하나를 눈으로 훑다 보면 놓치는 게 나오고, 다음 달에 데이터가 바뀌면 그 작업을 처음부터 다시 해야 한다. 조건부 서식을 걸어두면 이 반복 작업을 규칙 하나로 끝낼 수 있고, 데이터가 바뀌어도 서식이 따라 바뀐다.

문제는 기본 메뉴에 있는 규칙 몇 개만 눌러보고 “이 정도면 됐다” 하고 넘어가는 경우가 많다는 점이다. 실제로는 수식을 직접 넣어 조건을 세밀하게 걸 수 있고, 반대로 참조방식 하나 잘못 잡으면 규칙을 걸어놓고도 왜 안 되는지 한참 헤매게 된다. 이 글에서는 기본 제공 규칙부터 수식 응용, 실무에서 자주 막히는 지점까지 순서대로 짚어본다.

조건부 서식 기본 규칙 6가지, 뭐가 있고 언제 쓰나

홈 탭의 스타일 그룹에서 조건부 서식을 누르면 몇 가지 규칙 유형이 나온다. 이름만 보면 비슷해 보이지만 쓰임새는 꽤 갈린다. 아래처럼 정리해두면 어떤 상황에 어떤 규칙을 골라야 할지 감이 잡힌다.

여기까지는 마우스 클릭 몇 번으로 끝난다. 문제는 “완료된 행 전체를 강조하고 싶다”거나 “다른 셀 값과 비교해서 조건을 걸고 싶다”처럼 좀 더 복잡한 요구가 생길 때다. 이때부터는 수식을 직접 써야 한다.

조건부 서식 수식으로 직접 조건 걸기

조건부 서식 메뉴에서 “새 규칙”을 누르고 “수식을 사용하여 서식을 지정할 셀 결정”을 선택하면 수식 입력창이 나온다. 여기 들어가는 수식은 반드시 등호(=)로 시작해야 하고, 결과가 TRUE(참) 또는 FALSE(거짓)로 나오는 논리식이어야 한다. 셀 값 자체를 반환하는 수식은 쓸 수 없다는 뜻이다.

가장 많이 쓰이는 예시는 중복값을 찾는 수식이다.

A2셀부터 A400셀까지 범위 안에서 A2와 같은 값이 몇 번 나오는지 세고, 그 개수가 1보다 크면(즉 자기 자신 말고 또 있으면) TRUE를 반환해 서식을 적용한다. 문법 자체는 어렵지 않다. 다들 여기서 막히는 지점은 따로 있는데, 바로 참조방식이다.

조건부 서식 참조방식 하나로 결과가 완전히 달라진다

워크시트에서 셀을 마우스로 클릭해 수식에 참조를 넣으면 엑셀은 기본적으로 절대참조($A$1 같은 형태)를 붙인다. 규칙을 범위 전체에 똑같이 적용하려던 의도였다면 여기서 문제가 생긴다. 참조를 절대참조 그대로 두면 규칙이 범위 전체가 아니라 사실상 처음 지정한 그 한 셀 기준으로만 평가되기 때문이다. 규칙을 걸긴 걸었는데 원하는 셀에는 서식이 안 먹히고 엉뚱한 셀에만 적용되는 현상이 이래서 생긴다.

행 전체를 강조하는 실무 패턴을 보면 이 원리가 더 분명해진다. 완료 여부를 표시하는 열이 C열이고, 값이 “완료”인 행 전체를 초록색으로 칠하고 싶다고 해보자. 이때 쓰는 수식은 다음과 같다.

열(C)은 $를 붙여 고정하고, 행(3)은 그대로 둔 혼합참조다. 이 규칙을 A3:F3, 나아가 A3:F100처럼 행 전체 범위에 적용하면 각 행에서 C열 값을 확인해 “완료”인 행만 통째로 색이 바뀐다. 만약 여기서 열과 행을 둘 다 고정한 완전 절대참조 =$C$3=”완료”로 잘못 입력하면, 모든 행이 오직 C3 셀 하나의 값만 보고 서식 여부를 판단하게 된다. 결과적으로 표 전체가 한꺼번에 켜지거나 꺼지는, 의도와 전혀 다른 화면이 나온다.

조건부 서식 규칙 두 개가 부딪히면 무엇이 이길까

같은 셀에 규칙을 여러 개 걸어두면 언젠가 서로 충돌하는 순간이 온다. 이럴 때 엑셀은 조건부 서식 규칙 관리자 대화상자에 나열된 순서, 즉 위에서 아래로 내려가는 목록 순서를 우선순위로 삼는다. 목록 맨 위에 있는 규칙이 적용되고, 그 아래 규칙은 조건을 만족해도 무시된다.

이게 은근히 헷갈리는 지점이다. 예를 들어 “셀 강조 규칙”으로 1000 이상 값을 빨간색으로 표시해두고, 그 아래에 수식 기반 규칙으로 같은 셀을 파란색으로 표시하도록 걸어놨다고 하자. 두 조건을 동시에 만족하는 셀이 있다면 규칙 관리자 목록에서 더 위에 있는 규칙 색이 화면에 남는다. 순서를 바꾸고 싶다면 대화상자 안에서 규칙을 선택해 위/아래 화살표로 옮기면 된다. 규칙 유형이 다르다고 우선순위가 알아서 정해지는 게 아니라, 어디까지나 목록에 나열된 순서가 기준이라는 점을 놓치면 왜 원하는 색이 안 나오는지 계속 헷갈리게 된다.

조건부 서식이 안 먹힐 때 확인할 것들

규칙을 분명히 걸었는데 화면에 아무 변화가 없을 때가 있다. 원인은 대개 아래 세 가지 중 하나다.

세 가지를 다 확인했는데도 안 된다면 규칙 우선순위 문제일 가능성도 열어둬야 한다. 앞서 나온 것처럼 다른 규칙이 목록 위에서 먼저 적용되고 있을 수 있다.

조건부 서식 파일 형식별 제한과 복사할 때 주의점

조건부 서식은 파일 형식에 따라 지원 범위가 달라진다. Microsoft 공식 문서 기준으로 xlsx(Excel 2007 이후) 형식은 셀 하나에 최대 64개 조건까지 지원하지만, xls(97-2003) 형식으로 저장하면 처음 3개 조건만 표시되고 나머지는 적용되지 않는다. 97-2003 형식은 조건부 서식 범위가 서로 겹치는 경우도 지원하지 않아서, 오래된 형식으로 파일을 주고받는 환경이라면 서식이 예상과 다르게 나타날 수 있다.

다만 실무 문서 대부분은 규칙 수가 많아야 십수 개 수준이라 64개 제한에 실제로 걸리는 경우는 드물 것으로 보인다(추정). 이 부분은 이번 조사에서 정확한 발생 빈도까지 확인된 사실은 아니고, 오래된 형식을 계속 쓰는 특수한 환경이 아니라면 크게 신경 쓰지 않아도 되는 문제라고 봐도 무리는 없다.

조건부 서식이 걸린 셀을 다른 시트로 복사할 때도 종종 예상과 다른 결과가 나온다는 사례가 실무에서 보고된다(추정). 엑셀은 일반 붙여넣기와 “조건부 서식까지 병합해서 붙여넣기”를 구분해서 다루는데, 붙여넣기 옵션 중 “모든 병합 조건부 서식”을 고르면 셀 내용과 조건부 서식 옵션이 함께 붙여넣기 된다. 반대로 이 옵션을 쓰지 않고 그냥 Ctrl+V로 붙여넣으면 서식이 의도한 대로 따라오지 않는 경우가 생길 수 있다. 정확히 어떤 상황에서 어떻게 깨지는지까지는 공식 문서에 상세히 나와 있지 않아서, “다른 시트에 옮길 땐 붙여넣기 옵션을 한 번 확인해보는 습관” 정도로 기억해두는 편이 안전하다.