엑셀 조건부서식의 모든것! 기초부터 응용까지 총정리

실습 가이드

엑셀 조건부서식은 말 그대로 '셀의 값이 특정 조건을 만족할 때 적용되는 서식'을 의미합니다. 이전 '셀 서식의 모든것' 강의에서도 다뤘듯이, 기본 셀 서식에서도 세미콜론(;)이나 대괄호([])를 활용해 제한적이지만 간단한 조건부 서식을 적용할 수 있습니다.

셀 서식은 "양수 ; 음수 ; 0 ; 텍스트" 또는 "[조건] 서식 ; [조건] 서식" 형태로 조건별 서식을 지정할 수 있지만, 조건부서식에 비해 표현 범위가 제한적입니다.

셀 서식을 활용한 조건별 서식과 달리, 조건부서식은 하나의 셀에 여러 개의 서식을 동시에 지정할 수 있으며 "상위 3개 항목", "특정 텍스트 포함", "평균 미만" 등 다양한 기준의 서식을 자유롭게 적용할 수 있다는 장점이 있습니다.

엑셀 조건부서식 기초예제

조건부서식을 사용하면 범위 내 상위 n개 항목을 손쉽게 강조할 수 있습니다.

1. 예제파일 첫번째 시트의 왼쪽 표에서 합계를 제외한 부채비율 범위를 선택합니다.

   

2. 홈 – 조건부서식 – 상위/하위규칙에서 '상위 10개 항목'을 선택합니다.

   

3. "다음 상위 순위에 속하는 셀의 서식 지정"에서 숫자 '10'을 '3'으로 변경합니다.

   

4. '확인'을 누르면 상위 3개 항목에 조건부서식이 적용됩니다.

   

범위 내 값의 산술평균을 자동 계산하여 평균 미만인 값을 강조할 수도 있습니다.

1. 예제파일 첫번째 시트의 오른쪽 표에서 키(cm) 범위를 선택합니다.

   

2. 홈 – 조건부서식 – 상위/하위규칙에서 '평균 미만'을 선택합니다.

   

3. '확인'을 누르면 평균 미만인 값에 조건부서식이 적용됩니다.

   

다른 셀을 참조하여 실시간으로 값을 강조하는 엑셀 조건부서식

다른 셀을 참조하여 조건부서식을 적용하면 참조하는 셀의 값이 바뀔 때마다 조건을 만족하는 셀의 서식이 실시간으로 갱신되도록 만들 수 있습니다. 예제파일 두번째 시트로 이동한 뒤, 다른 셀 주소를 참조하여 실시간으로 적용되는 조건부서식 4가지 예제를 차례로 살펴보겠습니다.

1. 키(cm) 범위를 선택한 뒤, 홈 – 조건부서식 – 셀 강조규칙에서 '보다 작음'을 선택합니다.

   

2. '다음 값보다 작은 셀의 서식 지정'에서 값으로 $G$6 셀을 참조합니다.

   

3. 적용할 서식에서 '사용자 지정 서식..'을 선택합니다.

   

4. 글꼴 스타일을 굵게, 색상은 빨간색으로 지정합니다.

   

5. '확인'을 누르면 키가 145 미만일 때 굵은 빨간색 글씨로 서식이 적용됩니다.

   

1. 체질량지수 범위를 선택한 뒤, 홈 – 조건부서식 – 셀 강조규칙에서 '다음 값의 사이에 있음'을 선택합니다.

   

2. 두 값으로 $G$9 셀과 $H$9 셀을 참조합니다.

   

3. 적용할 서식에서 '사용자 지정 서식..'을 선택합니다.

   

4. 글꼴 스타일을 굵게, 색상은 파란색으로 지정합니다.

   

5. '확인'을 누르면 체질량지수가 20과 24 사이일 때 굵은 파란색 글씨 서식이 적용됩니다.

   

1. 이름 범위를 선택한 뒤, 홈 – 조건부서식 – 새규칙을 선택합니다.

   

2. 새 서식 규칙에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.

   

3. 엑셀 화면 좌측 상단의 이름상자를 확인해 현재 선택된 범위에서 활성화된 셀 주소를 파악합니다.

   

4. 조건부서식의 수식을 아래와 같이 입력합니다.
   =LEFT(활성화된셀주소,1) = $G$12

   

5. LEFT 함수에 입력된 셀 주소에서 숫자 앞의 $표시를 제거합니다.

   

6. 서식을 클릭한 뒤, 채우기에서 배경색을 노란색으로 선택합니다.

   

7. '확인'을 누르면 특정 성씨를 가진 학생의 이름이 노란색 배경으로 강조됩니다.

   

1. 키(cm)와 몸무게(kg) 범위를 선택합니다.

   

2. 홈 – 조건부서식 – 새규칙으로 이동한 뒤, '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.

   

3. 엑셀 화면 좌측 상단의 이름상자를 확인해 현재 선택된 범위에서 활성화된 셀 주소를 파악합니다.

   

4. 조건부서식의 수식을 아래와 같이 입력합니다.
   =AND('셀주소'<$G$15,'옆의 셀주소'<$H$15)

   

5. 셀주소와 옆의 셀주소에서 숫자 앞의 $표시를 제거합니다.

   

6. 서식을 클릭한 뒤, 채우기에서 배경색을 초록색으로 변경합니다.

   

7. '확인'을 누르면 키와 몸무게 조건을 모두 만족하는 셀이 초록색 배경으로 강조됩니다.

   

엑셀 데이터막대는 선택한 범위의 값을 '막대 형식 그래프'로 시각화하여 셀 안에 표시해주는 조건부서식입니다. 몇 번의 클릭만으로 손쉽게 적용할 수 있는 시각화 도구로, 표 안에서 값의 분포를 한눈에 파악할 때 유용합니다.

1. 세번째 시트로 이동한 뒤, 체질량지수 범위를 선택합니다.

   

2. 홈 – 조건부서식 – 데이터막대에서 원하는 형식의 데이터 막대를 적용합니다.

   

3. 데이터막대 조건부서식 적용이 완료됩니다.

   

상황에 따라 데이터막대의 최소값과 최대값을 직접 조절하여 막대의 길이를 더 정확하게 표현할 수도 있습니다. 설정 방법은 다음과 같습니다.

1. 데이터막대가 적용된 범위를 선택한 뒤, 홈 – 조건부서식 – 규칙관리로 이동합니다.

   

2. 데이터 막대를 선택한 뒤, 규칙편집을 클릭합니다.

   

3. 최소값과 최대값을 모두 '숫자'로 변경한 뒤, 원하는 값을 입력합니다.

   

4. '확인'을 누르면 데이터막대의 최소값과 최대값 설정이 완료됩니다.

   

엑셀 스파크라인은 시간대별로 나열된 값을 꺾은선 또는 막대그래프 형식으로 시각화하여 셀 안에 표시해주는 시각화 도구입니다. 엄밀히 말해 조건부서식 자체는 아니지만, 데이터막대와 함께 활용하면 보고서 작성 시 효과적으로 사용할 수 있는 기능 중 하나입니다.

1. 예제파일의 네번째 시트로 이동한 뒤, 스파크라인을 출력할 범위(J6:J14)를 선택합니다.

   

2. 삽입 – 스파크라인에서 '열'을 선택합니다.

   

3. 스파크라인의 데이터로 입력될 범위(C6:H14)를 선택합니다.

   

4. '확인'을 누르면 스파크라인이 삽입됩니다.

   

5. 스파크라인은 값의 최대값과 최소값을 자동으로 강조할 수 있습니다. 상단의 스파크라인 옵션으로 이동한 뒤, 표시에서 '높은 점'을 선택합니다.

   

6. 최대값의 색상이 빨간색으로 변경됩니다.

   

7. 스파크라인 옵션에서 '스파크라인 색'을 변경하면 그래프의 기본 색상을 변경할 수 있습니다.

   

8. 스파크라인 옵션의 '표식 색'을 변경하면 최대값과 최소값 지점의 표시 색상을 변경할 수 있습니다.

   

9. 스파크라인 옵션에서 '지우기'를 선택하면 선택된 범위에 적용된 스파크라인을 제거할 수 있습니다.

   

엑셀의 아이콘집합은 각 조건을 만족할 때 출력할 아이콘을 지정해 셀 안에 표시하는 기능이며, 최대 4개까지 조건을 지정할 수 있습니다. 아이콘집합의 자세한 설명과 실전 사용법은 이전에 공유한 '아이콘집합 실전사용법' 강의에서 다뤘으니, 자세한 내용이 궁금한 경우 아래 관련 링크를 참고하시기 바랍니다.

이번 강의에서는 아이콘집합의 사용법만 간단히 정리합니다.

1. 예제파일 다섯번째 시트로 이동한 뒤, 체질량지수 범위를 선택합니다.

   

2. 홈 – 조건부서식 – 아이콘집합 – 도형에서 아이콘이 4개인 신호등 모양의 아이콘집합을 선택합니다.

   

3. 아이콘집합이 적용된 범위를 선택한 뒤, 조건부서식 – 규칙관리 – 규칙편집으로 이동합니다.

   

4. 규칙 설명 편집의 값을 아래 그림과 같이 변경합니다.
   주의사항 : 종류를 모두 '숫자'로 선택한 뒤 값을 입력합니다.

   

5. '확인'을 누르면 선택된 범위에 아이콘 집합이 적용됩니다.

   

조건부서식은 셀의 값에 영향을 주지 않습니다. 또한 파일에 치명적인 영향을 주는 기능이 아니어서 사용 시 특별히 주의해야 할 점은 많지 않습니다.

다만 넓은 범위에 조건부서식을 적용할 때 알아두면 좋은 점이 한 가지 있습니다. 바로 '셀 참조방식을 적절히 활용하는 것'입니다.

조건부서식과 셀 참조방식을 함께 활용하면, 이전 강의에서 소개한 '버튼 클릭으로 표를 강조하는 방법'처럼 표에서 원하는 부분을 실시간으로 강조하는 보고서 양식을 만들 수 있습니다.

이번 예제에서는 부서명을 선택하면 해당 부서의 모든 직원이 자동으로 강조되는 표를 만들어 보겠습니다.

1. 예제파일 여섯번째 시트로 이동한 뒤, B4셀을 시작으로 부서명~연봉까지 범위를 선택합니다.

   

2. 홈 – 조건부서식 – 새규칙에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.

   

3. 화면 좌측의 이름상자를 확인해 선택된 범위에서 활성화된 셀의 주소를 파악합니다.
   (B4셀이 활성화된 것을 확인할 수 있습니다.)

   

4. 조건부서식의 수식을 아래와 같이 입력합니다.
   =$B4=$H$4
   '$B4셀에서 $B가 고정되어 있으므로, C4:F4 범위는 $B4=$H$4일 때 조건부서식이 동시에 적용됩니다.

   

5. 서식으로 이동한 뒤 채우기 색상을 옅은 노란색으로 변경합니다.

   

6. '확인'을 누른 뒤 부서명을 변경하면 표에서 해당 부서의 모든 직원이 실시간으로 강조됩니다.

   

관련링크 : MS 홈페이지 조건부서식 사용법 설명