엑셀에서 작업하다 보면 조건부 서식으로 지정된 셀 색상을 기준으로 데이터를 필터링해야 하는 경우가 자주 있습니다. 엑셀의 조건부 서식 기능을 활용하면 다양한 조건에 따라 셀의 모양을 바꿀 수 있고, 이렇게 적용된 색상을 기준으로 원하는 데이터만 손쉽게 추출할 수 있습니다.
데이터 양이 적다면 수작업으로 걸러낼 수 있지만, 대용량 데이터셋에서는 일일이 확인하는 것이 매우 번거롭고 시간 낭비가 됩니다. 다행히 엑셀에는 거의 모든 스프레드시트 문제에 대한 해결책이 마련되어 있는데요. 몇 가지 간단한 단계만 거치면 조건부 서식으로 칠해진 색상별로 데이터를 필터링할 수 있습니다.
엑셀에서 조건부 서식 색상으로 필터링하는 3단계
이번 섹션에서는 조건부 서식으로 지정된 색상을 기준으로 데이터를 필터링하는 3가지 간단한 단계를 알아보겠습니다. 예시로 한 학교의 중학교 3학년 학생들의 성적표가 있다고 가정하고, 총점(Total) 열을 아래 두 가지 기준에 따라 필터링해 보겠습니다.
- 총점이 299보다 큰 경우 → 초록색 채우기로 서식 지정
- 총점이 300 미만인 경우 → 빨간색 채우기로 서식 지정
그럼 아래 단계를 따라 해 보겠습니다.
본 글은 Microsoft Excel 365 버전을 기준으로 작성되었지만, 사용 중인 다른 버전에서도 동일하게 따라 할 수 있습니다.
1단계: 조건부 서식 먼저 적용하기
첫 번째 단계는 데이터에 조건부 서식을 적용하는 것입니다. 앞서 언급한 조건에 따라 총점 열의 셀에 서로 다른 색상 채우기를 추가할 수 있습니다.
- 먼저 아래 이미지와 같이 총점 열의 셀 범위를 선택합니다.
- 리본 메뉴에서 홈(Home) 탭으로 이동합니다.
- 스타일(Styles) 그룹에서 조건부 서식(Conditional Formatting) 옵션을 클릭합니다.
- 드롭다운 목록에서 셀 강조 규칙(Highlight Cells Rules)을 선택합니다.
- 다음으로 아래 이미지와 같이 보다 큼(Greater Than) 옵션을 선택합니다.
그러면 보다 큼 대화 상자가 열립니다.
- 보다 큼 대화 상자의 입력란에 299를 입력합니다.
- 드롭다운 화살표를 클릭하고 사용자 지정 서식(Custom Format) 옵션을 선택합니다.
그러면 워크시트에 셀 서식(Format Cells) 대화 상자가 표시됩니다.
- 셀 서식 대화 상자에서 채우기(Fill) 탭으로 이동합니다.
- 원하는 색상을 선택한 뒤 확인(OK)을 클릭합니다.
- 다시 보다 큼 대화 상자로 돌아오면 확인을 클릭합니다.
이제 아래 그림처럼 선택한 색상으로 셀이 서식 지정됩니다.
같은 방식으로 두 번째 조건도 적용해 보겠습니다.
- 총점 열의 셀을 다시 선택합니다.
- 리본 메뉴의 홈 탭 → 스타일 그룹에서 조건부 서식을 클릭합니다.
- 셀 강조 규칙에서 보다 작음(Less Than) 옵션을 선택합니다.
그러면 보다 작음 대화 상자가 나타납니다.
- 보다 작음 대화 상자의 입력란에 300을 입력합니다.
- 드롭다운 화살표를 클릭하고 사용자 지정 서식을 선택합니다.
- 셀 서식 대화 상자에서 채우기 탭으로 이동합니다.
- 원하는 색상을 고른 후 확인을 누릅니다.
- 보다 작음 대화 상자로 돌아와 확인을 클릭합니다.
이제 총점 열이 설정한 색상대로 서식 지정된 것을 확인할 수 있습니다.
2단계: 필터 옵션 활성화하기
이번에는 필터(Filter) 옵션을 활성화하겠습니다. 아래 단계를 따라 해 보세요.
- 먼저 제목 행을 포함하여 전체 데이터 범위를 선택합니다.
- 리본 메뉴에서 데이터(Data) 탭으로 이동합니다.
- 정렬 및 필터(Sort & Filter) 그룹에서 필터 옵션을 선택합니다.
그러면 아래 이미지처럼 각 열 제목에 필터 드롭다운 버튼이 활성화됩니다.
참고: 데이터를 선택한 상태에서 키보드 단축키 Ctrl + Shift + L을 눌러서도 필터 옵션을 빠르게 활성화할 수 있습니다.
3단계: 색상별로 필터링하기
이제 셀 색상을 기준으로 데이터를 필터링해 보겠습니다. 아래 절차를 따르세요.
- 먼저 아래 그림과 같이 드롭다운 아이콘을 클릭합니다.
- 메뉴에서 색상별 필터(Filter by Color) 옵션을 선택합니다.
- 원하는 색상을 고릅니다. 여기서는 초록색을 선택했습니다.
그러면 워크시트에 다음과 같은 결과가 표시됩니다.
마찬가지로 빨간색을 선택하면 아래 이미지처럼 해당 색상의 데이터만 필터링되어 나타납니다.
고급 필터 기능으로 조건에 맞는 데이터 추출하기
고급 필터(Advanced Filter)는 매우 유용한 엑셀 기능입니다. 이 기능을 사용하면 지정한 조건에 맞는 데이터를 같은 워크시트 내 다른 위치로 추출할 수 있습니다. 예를 들어 아래 두 가지 기준으로 데이터를 걸러내고 싶다고 가정해 보겠습니다.
- 총점이 299점 초과인 학생
- 총점이 300점 미만인 학생
고급 필터를 활용해 데이터를 추출하는 절차를 살펴보겠습니다.
단계:
- 먼저 Total이라는 이름의 새 열을 만들고, 그 아래 셀에 >299라고 입력합니다.
참고: 새로 만든 열의 이름은 반드시 데이터셋의 총점(Total) 열 이름과 정확히 일치해야 합니다.
- 리본 메뉴에서 데이터 탭으로 이동합니다.
- 정렬 및 필터 그룹에서 고급(Advanced) 옵션을 선택합니다.
그러면 워크시트에 고급 필터 대화 상자가 열립니다.
- 고급 필터 대화 상자의 작업(Action) 항목에서 다른 위치에 복사(Copy to another location) 옵션을 선택합니다.
- 목록 범위(List range) 입력란을 클릭하고 아래 그림처럼 제목 행을 포함한 전체 데이터 범위를 선택합니다.
- 조건 범위(Criteria range) 입력란을 클릭하고 앞서 만든 새 열을 선택합니다.
- 복사 위치(Copy to) 입력란을 클릭하고 B18 셀을 지정합니다. 이곳에 필터링된 결과가 표시됩니다.
- 마지막으로 확인을 클릭합니다.
그러면 총점이 299점을 초과하는 학생 목록이 추출됩니다.
조건을 <300으로 변경한 후 동일한 단계를 수행하면, 새 조건에 맞는 결과도 아래와 같이 얻을 수 있습니다.
엑셀에서 '색상별 필터' 옵션이 나타나지 않을 때 해결 방법
엑셀 작업 중 색상별 필터 옵션이 비활성화되어 있는 경우가 종종 있습니다. 대부분의 원인은 모든 셀이 같은 색상으로 서식 지정되어 있기 때문입니다. 예를 들어 중학교 3학년 성적표의 총점 열 전체가 노란색으로 채워져 있다고 가정해 보겠습니다.
아래 단계를 통해 문제를 해결할 수 있습니다.
단계:
- 먼저 제목 행을 포함한 데이터셋을 선택하고 리본 메뉴의 데이터 탭으로 이동합니다.
- 정렬 및 필터 그룹에서 필터 옵션을 선택합니다.
그러면 아래 이미지처럼 필터 옵션이 추가됩니다.
- 총점 열 옆의 드롭다운 아이콘을 클릭합니다.
색상별 필터 옵션이 회색으로 표시되어 선택할 수 없는 것을 확인할 수 있습니다. 지금부터 이 문제를 해결해 보겠습니다.
- 앞서 1단계에서 설명한 방법대로 데이터셋에 조건부 서식을 적용하여 아래와 같은 결과를 만듭니다.
- 이후 총점 열 옆의 드롭다운 아이콘을 다시 클릭합니다.
- 색상별 필터 옵션을 선택합니다.
- 원하는 색상을 고릅니다. 여기서는 초록색을 선택했습니다.
그러면 아래 이미지처럼 색상을 기준으로 필터링된 결과를 얻을 수 있습니다.
핵심은 셀 색상이 두 가지 이상으로 구분되어야 색상별 필터 메뉴가 활성화된다는 점입니다. 조건부 서식으로 색상을 다양하게 지정하면 문제가 자연스럽게 해결됩니다.
마치며
지금까지 엑셀에서 조건부 서식으로 지정된 셀 색상을 기준으로 데이터를 필터링하는 방법을 알아보았습니다. 3단계 필터링 과정부터 고급 필터 활용법, 그리고 색상별 필터 옵션이 비활성화될 때의 해결책까지 모두 다루었으니 실무에 바로 적용해 보시기 바랍니다. 궁금한 점이나 개선 의견이 있다면 언제든지 댓글로 남겨 주세요.
함께 읽으면 좋은 글
- 엑셀에서 여러 열을 색상별로 필터링하는 방법 (2가지 방법)
- 엑셀에서 여러 색상을 한 번에 필터링하는 방법 (2가지 쉬운 방법)