조건부 서식(Conditional Formatting)은 스프레드시트의 데이터 값에 따라 색상, 아이콘, 굵은 글씨 같은 서식을 자동으로 적용해 주는 Excel의 강력한 기능입니다. Excel에서는 사용하기 쉬운 기본 규칙들을 제공하지만, 수식 기반 규칙을 활용하면 훨씬 더 유연하고 정교한 서식 지정이 가능합니다. 이 글에서는 기본 제공 옵션으로는 구현하기 어려운 고급 조건부 서식 기법을 수식 기반 규칙을 통해 자세히 알아보겠습니다.
수식 기반 조건부 서식 규칙 만들기
조건부 서식은 특정 조건에 따라 작동하며, 사용자가 직접 수식을 작성해 맞춤형 규칙을 만들 수도 있습니다. 수식 기반 조건부 서식은 워크시트의 데이터를 평가하는 사용자 지정 수식으로 규칙을 정의하는 방식이며, 수식의 결과가 TRUE일 때 서식이 적용됩니다. 적용 방법은 다음과 같습니다.
- 조건부 서식을 적용할 셀 범위를 선택합니다.
- 홈 탭 >> 조건부 서식 >> 새 규칙을 선택합니다.
- 새 서식 규칙 대화 상자에서 다음을 진행합니다.
- 수식을 사용하여 서식을 지정할 셀 결정 옵션을 선택합니다.
- 다음 수식이 참인 값의 서식 지정 입력란에 사용자 지정 수식을 입력합니다.
- 서식 버튼을 클릭해 원하는 서식을 지정합니다.
- 확인을 클릭합니다.
이제 판매 데이터셋을 예제로 삼아 수식 기반 고급 조건부 서식을 하나씩 살펴보겠습니다.
1. 특정 조건을 만족하는 행 강조하기
판매액이 특정 값을 초과하는 행을 강조하고 싶다면 다음 비교 수식을 사용하면 됩니다.
수식:
=$G2>=5000
이 수식은 G2 셀(판매액)의 값이 5000 이상인지 확인합니다. 비교 기준이 모든 행에서 동일하게 유지되어야 한다면 열 부분에는 절대 참조($)를 사용하는 것이 좋습니다.
- 서식을 클릭해 원하는 서식을 지정합니다. 여기서는 녹색을 선택했습니다.
- 확인을 클릭해 규칙을 적용합니다.
2. 중복된 판매 항목 찾아내기
데이터셋에서 중복된 판매 항목을 찾아 정리하려면 다음 수식을 활용할 수 있습니다.
수식:
=COUNTIFS($A$2:$A$17, $A2, $B$2:$B$17, $B2, $C$2:$C$17, $C2, $D$2:$D$17, $D2, $E$2:$E$17, $E2, $F$2:$F$17, $F2, $G$2:$G$17, $G2) > 1
이 수식은 A열부터 G열까지 각 행의 값 조합이 지정된 범위 내에서 중복되는지 검사합니다. 즉, 각 행의 A~G열 값 조합을 세어 그 개수가 1보다 크면 해당 행을 중복으로 간주하고 강조 표시합니다.
- 서식을 클릭해 원하는 서식을 지정합니다. 여기서는 노란색 채우기를 선택했습니다.
- 확인을 클릭해 규칙을 적용합니다.
3. 한 행 건너 색상 넣기 (줄무늬 효과)
데이터 표의 가독성을 높이려면 한 행 건너 색상을 입혀 '얼룩말 무늬' 효과를 주면 좋습니다. 다음 수식을 사용하세요.
수식:
=MOD(ROW(),2)=0
ROW() 함수는 현재 행 번호를 반환하고, MOD(ROW(),2)=0은 해당 행 번호가 짝수인지 확인합니다. 결과적으로 짝수 번째 행에만 서식이 적용됩니다.
- 서식을 클릭해 원하는 서식을 지정합니다. 여기서는 연한 회색 배경 채우기를 선택했습니다.
- 확인을 클릭해 규칙을 적용합니다.
4. 지난 날짜 강조하기
마감이 지난 항목을 빠르게 파악하려면 오늘 날짜보다 이전인 날짜를 강조하면 됩니다. 이때 TODAY 함수를 활용한 수식을 사용합니다.
수식:
=AND(ISNUMBER(A2), A2 < TODAY())
이 수식은 A2 셀의 값이 숫자(날짜)이면서 오늘 날짜보다 이전인지 확인하여, 데이터셋에서 이미 지난 날짜를 모두 표시해 줍니다.
- 서식을 클릭해 원하는 서식을 지정합니다. 여기서는 빨간색 채우기를 선택했습니다.
- 확인을 클릭해 규칙을 적용합니다.
5. 상위 10% 판매액 강조하기
판매 데이터셋에서 상위 10%에 해당하는 높은 판매액을 강조할 수 있습니다. 이때 PERCENTILE 함수를 활용하면 편리합니다.
수식:
=$G2>=PERCENTILE($G$2:$G$17,0.9)
PERCENTILE($G$2:$G$17,0.9)는 해당 범위의 90백분위수를 계산합니다. 이 값보다 크거나 같은 값들이 바로 상위 10%에 속하며, G열의 값이 이 백분위수 이상이면 전체 행에 서식이 적용됩니다.
- 서식을 클릭해 원하는 서식을 지정합니다. 여기서는 녹색 채우기와 굵은 글씨를 선택했습니다.
- 확인을 클릭해 규칙을 적용합니다.
6. 여러 조건 결합하기
논리 함수를 사용해 여러 조건을 결합하면 더욱 복잡한 조건부 서식 규칙을 만들 수 있습니다. AND 또는 OR 함수를 활용해 복수 조건에 따라 셀을 강조해 보세요.
수식:
=AND($G2>=3000, $C2="East")
이 수식은 판매액이 3000 이상이고 지역이 East(동부)인 경우에만 해당 행을 강조합니다. 두 조건을 모두 만족해야 서식이 적용됩니다.
- 서식을 클릭해 원하는 서식을 지정합니다. 여기서는 이탤릭체와 피치색 채우기를 선택했습니다.
- 확인을 클릭해 규칙을 적용합니다.
조건부 서식 규칙 관리하기
작성한 조건부 서식 규칙을 관리하는 방법은 다음과 같습니다.
- 조건부 서식 탭 >> 규칙 관리를 선택합니다.
- 여기서 규칙을 확인하거나 추가, 수정, 삭제, 복제할 수 있으며 우선순위도 변경할 수 있습니다.
- '적용 대상' 필드를 사용하면 규칙이 적용되는 셀 범위를 수정할 수도 있습니다.
마무리
Excel에서 수식 기반 규칙을 활용한 고급 조건부 서식은 데이터를 눈에 잘 들어오게 만드는 유연한 시각화 도구입니다. 특정 조건에 맞는 셀 강조부터 중복 관리, 복합 조건 처리, 마감일 확인까지 다양한 상황에서 수식 기반 규칙이 큰 도움이 됩니다. 이 기능을 잘 익혀두면 스프레드시트를 더욱 통찰력 있고 보기 좋게 꾸밀 수 있습니다. 여러 가지 수식을 직접 실험해 보며 나만의 스프레드시트를 한 단계 업그레이드해 보세요.