이 글에서는 엑셀에서 다른 셀의 텍스트를 기준으로 조건부 서식(Conditional Formatting)을 적용하는 흥미로운 주제를 다룹니다. 조건부 서식을 활용하면 워크시트의 데이터를 손쉽게 강조할 수 있어 가독성과 분석 효율이 크게 향상됩니다. 이번 포스팅에서는 다른 텍스트 셀의 값을 참조해 조건부 서식을 설정하는 4가지 방법을 단계별로 자세히 살펴보겠습니다.
예제 데이터 소개
설명에 앞서 예제로 사용할 데이터셋을 준비했습니다. 아래 데이터는 학생 7명의 연간 성적표로, 이름(Name), 취득 점수(Obtained Marks), 합격 여부(Status) 정보를 담고 있습니다.
이어서 C13 셀에 조건값인 'Pass'를 입력했습니다.
이제 이 조건을 기준으로 데이터셋에 조건부 서식을 적용하는 방법을 하나씩 알아보겠습니다.
1. 수식을 활용한 조건부 서식 적용 (SEARCH 함수)
첫 번째 방법은 SEARCH 함수를 사용해 원하는 텍스트를 찾고, 해당 위치에 조건부 서식을 적용하는 것입니다. 아래 순서대로 진행해 보세요.
- 먼저 조건부 서식을 적용할 B4:D11 셀 범위를 선택합니다.
- 홈(Home) 탭으로 이동한 뒤 조건부 서식(Conditional Formatting)을 클릭합니다.
- 드롭다운 메뉴에서 새 규칙(New Rule)을 선택합니다.
- 새 서식 규칙(New Formatting Rule) 대화상자에서 '수식을 사용하여 서식을 지정할 셀 결정' 옵션을 선택합니다.
- 그리고 '아래 수식이 참인 값의 서식 지정' 입력란에 다음 수식을 입력합니다.
=SEARCH($C$13, B4)>0
- 이후 서식(Format) 버튼을 클릭해 셀 서식(Format Cells) 대화상자를 엽니다.
- 채우기(Fill) 탭에서 원하는 색상을 고릅니다. 미리 보기(Sample) 영역에서 색상을 확인할 수 있습니다.
- 마지막으로 확인(OK) 버튼을 두 번 눌러 모든 대화상자를 닫으면 완료됩니다.
여기서는 SEARCH 함수를 사용해 C13 셀의 텍스트가 B4:D11 범위 내에 있는지 확인하고, 해당 셀을 강조 표시했습니다.
참고: =SEARCH($C$13, B4)>1 수식을 사용하면 'Pass'로 시작하는 셀만 강조할 수 있습니다. 예를 들어 'Pass with distinction', 'Pass with conditions'처럼 특정 단어로 시작하는 항목만 골라내고 싶을 때 유용합니다.
2. 다른 셀 기준으로 전체 행 강조하기
이번에는 학생 이름과 시험 결과를 함께 강조하고 싶은 경우를 생각해 볼 수 있습니다. 예를 들어 합격한 학생의 행 전체를 표시하는 것이죠. 여기서는 세 가지 수식을 활용해 전체 행을 강조하는 방법을 소개합니다.
2.1. SEARCH 함수 활용
전체 행을 강조하는 첫 번째 방법은 SEARCH 함수를 사용하는 것입니다. 아래 과정을 따라 해 보세요.
- 먼저 데이터 전체 범위를 선택합니다.
- 첫 번째 방법에서와 동일하게 새 서식 규칙 대화상자를 엽니다.
- 그리고 다음 수식을 입력합니다.
=SEARCH($C$13,$D4)>0
- 서식 > 채우기 > 확인 경로로 원하는 배경색을 지정합니다.
- 마지막으로 확인을 누르면 결과를 확인할 수 있습니다.
여기서 D4 셀 참조 앞에 $를 붙여 행은 상대적으로 변하지만 열(D)은 고정되도록 했습니다. 덕분에 수식이 각 행마다 반복 적용되면서 조건에 맞는 행 전체가 강조됩니다.
2.2. AND 함수 활용
전체 행을 강조하는 또 다른 유용한 방법은 AND 함수를 활용하는 것입니다. 아래 순서대로 진행하세요.
- 앞선 방법과 마찬가지로 새 서식 규칙 대화상자에 다음 수식을 입력합니다.
=AND($D5="Pass",$C5>40)
- 이후 위에서 설명한 것처럼 서식 > 채우기 탭에서 색상을 변경합니다.
- 마지막으로 확인을 누르면 최종 결과를 얻을 수 있습니다.
여기서는 AND 함수를 사용해 선택한 B4:D11 범위에서 두 개 이상의 조건(합격 여부와 점수)을 동시에 만족하는 행만 강조했습니다.
2.3. OR 함수 활용
OR 함수 역시 셀 텍스트를 기준으로 전체 행을 강조하는 데 활용할 수 있습니다.
- 먼저 B4:D11 셀 범위를 선택합니다.
- 홈 > 조건부 서식 > 새 규칙으로 이동합니다.
- 새 서식 규칙 대화상자에 다음 수식을 입력합니다.
=OR($D5="Pass",$C5>40)
- 색상을 변경한 뒤 확인을 누릅니다.
- 이제 조건에 맞는 행 전체가 강조된 것을 확인할 수 있습니다.
OR 함수는 여러 조건 중 최소 하나만 충족되어도 해당 행을 강조하므로, AND 함수보다 넓은 범위의 결과를 얻고 싶을 때 적합합니다.
3. 데이터 유효성 검사와 조건부 서식 함께 사용하기
데이터 유효성 검사(Data Validation)를 조합하면 드롭다운 목록에서 조건을 선택하는 즉시 서식이 바뀌는 동적인 시트를 만들 수 있습니다. 과정을 자세히 살펴보겠습니다.
- 먼저 조건을 입력할 C13 셀을 선택합니다.
- 데이터(Data) 탭에서 데이터 도구(Data Tools) 그룹의 데이터 유효성 검사(Data Validation)를 클릭합니다.
- 데이터 유효성 검사 대화상자에서 제한 대상으로 목록(List)을 선택합니다.
- 원본(Source) 입력란에 조건값인 Pass와 Fail을 입력합니다.
- 확인을 누릅니다.
- 이제 C13 셀을 클릭하면 조건 목록이 드롭다운으로 나타납니다.
- 다음으로 D5:D11 셀 범위를 선택합니다.
- 앞선 방법들과 동일하게 새 서식 규칙 대화상자에 다음 수식을 입력합니다.
=D5=$C$13
- 서식 > 채우기 탭에서 색상을 고른 뒤 확인 > 확인을 누릅니다.
- 이제 원하는 결과가 화면에 표시됩니다.
- 마지막으로 제대로 작동하는지 검증하기 위해 조건을 Fail로 바꿔 보세요. 강조된 셀이 자동으로 변경되는 것을 확인할 수 있습니다.
4. 특정 텍스트 옵션으로 조건부 서식 적용하기
마지막 섹션에서는 특정 텍스트(Specific Text) 옵션을 활용한 조건부 서식 적용 방법을 알아보겠습니다. 두 가지 방식이 있습니다.
4.1. 새 서식 규칙 직접 적용
첫 번째 방법은 조건부 서식 메뉴에서 바로 적용하는 방식입니다.
- 먼저 홈 탭에서 조건부 서식을 선택합니다.
- 이어서 셀 규칙 강조(Highlight Cell Rules) 섹션에서 '텍스트가 포함된(Text that Contains)'을 선택합니다.
- 그런 다음 셀 텍스트(Cell Text) 입력란에 C13 셀 참조를 입력합니다.
- 아래 이미지처럼 강조 색상을 지정합니다.
- 마지막으로 확인을 누르면 최종 결과를 확인할 수 있습니다.
4.2. 셀 규칙 강조 활용
두 번째 방법은 새 서식 규칙 대화상자에서 셀 강조 규칙을 설정하는 방식입니다.
- 먼저 규칙 유형(Rule Type)에서 '포함하는 셀만 서식 지정'을 선택합니다.
- 그런 다음 '다음 값을 포함하는 셀만 서식 지정' 항목에서 특정 텍스트(Specific Text)를 고릅니다.
- 이후 아래 이미지와 같이 셀 참조를 입력합니다.
- 마지막으로 서식 > 채우기 탭에서 강조 색상을 지정하고 확인을 누릅니다.
- 확인을 누르면 다른 셀의 텍스트를 기준으로 셀이 강조된 결과를 볼 수 있습니다.
알아두면 좋은 팁
- 조건부 서식을 적용하기 전에는 반드시 조건을 적용할 셀 범위를 먼저 선택해야 합니다.
- 조건부 서식 탭의 규칙 지우기(Clear Rules) 명령을 사용하면 단일 시트 또는 통합 문서 전체에서 조건부 서식을 한 번에 삭제할 수 있습니다.
마무리
지금까지 엑셀에서 다른 텍스트 셀을 기준으로 조건부 서식을 적용하는 4가지 방법을 살펴봤습니다. SEARCH 함수, AND/OR 함수, 데이터 유효성 검사, 특정 텍스트 옵션 등 상황에 맞는 방법을 골라 활용해 보세요. 더 좋은 방법을 알고 계시다면 의견을 공유해 주세요. 앞으로도 실무에 도움이 되는 엑셀 활용 팁을 계속 소개하겠습니다.