엑셀 조건부서식 기초부터 응용까지 완벽정리 :: 직장인 필수 스킬

당신의 보고서가 200% 달라지는 마법, 엑셀 조건부서식 기초부터 응용까지 완벽정리!

홈페이지 » 엑셀 조건부서식 기초부터 응용까지 완벽정리 :: 직장인 필수

엑셀 조건부서식 기초부터 응용까지 완벽정리! :: 기초입문 9강

엑셀 조건부서식 기초 강의 목차 바로가기
영상강의

예제파일 다운로드

엑셀 조건부서식이란 무엇인가요?

엑셀 조건부서식은 말 그대로 '셀의 값이 특정 조건을 만족하는 경우에 적용하는 서식'을 뜻 합니다. 이전 '셀 서식의 모든것' 강의에서 설명을 드렸지만, 기본 셀 서식에서도 세미콜론(;)이나 대괄호([])를 사용하여 다소 제한적이지만 간단하게 조건부 서식을 적용할 수 있습니다.

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

셀 서식을 사용한 조건별 서식과 다르게, 조건부서식은 하나의 셀에 여러개의 서식을 지정할 수 있으며 "상위 3개항목", "특정 텍스트 포함", "평균 미만" 등 다양한 조건에 대한 서식을 지정할 수도 있다는 장점이 있습니다.

엑셀 조건부서식 기초예제

범위 내 상위 3개 항목 강조하기

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

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

    엑셀 조건부 서식 범위 선택
    예제파일 첫번째 시트에서 부채비율 범위를 선택합니다.
  2. 홈 - 조건부서식 - 상위/하위규칙에서 '상위 10개 항목'을 선택합니다.

    조건부서식 상위 10개 항목
    조건부서식 - 상위/하위규칙 - 상위 10개 항목을 선택합니다.
  3. "다음 상위 순위에 속하는 셀의 서식지정"에서 숫자 '10'을 '3'으로 변경합니다.

    상위 3개 항목 강조
    값을 10에서 3으로 변경합니다.
  4. '확인'을 눌러 마무리하면, 상위 3개 항목에 조건부서식이 적용되었습니다.

    상위 3개 항목 조건부범위 완료
    상위 3개 항목에 조건부서식이 적용되었습니다.
평균 미만인 항목 강조하기

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

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

    키 범위 선택
    예제파일 첫번째 시트에서 키 범위를 선택합니다.
  2. 홈 - 조건부서식 - 상위하위규칙에서 '평균 미만'을 선택합니다.

    조건부서식 평균 미만
    조건부서식 - 상위/하위규칙 - 평균 미만을 선택합니다.
  3.  '확인'을 눌러 마무리하면, 평균 미만인 값에 조건부서식이 적용되었습니다.

    평균미만 조건부범위 적용 완료
    평균 미만인 항목에 조건부 서식이 적용되었습니다.

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

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

특정 값 미만인 경우 굵은 빨간색 글씨로 강조하기 (키가 145 미만인 경우)
  1. 키(cm) 범위를 선택한 뒤, 홈 - 조건부서식 - 셀 강조규칙에서 '보다 작음'을 선택합니다.

    셀 강조규칙 보다 작음 조건
    조건부서식 - 셀 강조규칙 - 보다 작음을 선택합니다.
  2. '다음 값보다 작은 셀의 서식 지정'에서 값으로 $G$6 셀을 참조합니다.

    조건 셀 선택
    조건의 값으로 G6 셀을 참조합니다.
  3. 적용할 서식에서 '사용자 지정 서식..'을 선택합니다.

    사용자지정서식 이동
    적용할 서식에서 사용자 지정 서식을 클릭합니다.
  4. 글꼴 스타일을 굵게, 색상은 빨간색을 선택합니다.

    글꼴 스타일 지정
    글꼴 스타일을 굵은 빨간색 글씨로 변경합니다.
  5. '확인'을 눌러 마무리하면 키가 145미만인 경우에 서식이 굵은 빨간색 글씨로 적용되었습니다.

    키 범위 조건부서식 적용 완료
    키가 145 이하인 경우에 조건부서식이 적용 되었습니다.
두 값의 사이값인 경우 굵은 파란색 글씨로 강조하기(체질량지수가 20~24인 경우)
  1. 체질량지수 범위를 선택한 뒤, 홈 - 조건부서식 - 셀 강조규칙에서 '다음 값의 사이에 있음'을 선택합니다.

    엑셀 조건부서식 다음 값 사이에 있음
    조건부서식 - 셀 강조규칙 - 다음 값의 사이에 있음을 선택합니다.
  2. 두 값으로 $G$9셀과 $H$9셀을 참조합니다.

    다음 값 사이 두 셀 선택
    각 값으로 G10셀과 H9 셀을 참조합니다.
  3. 적용할 서식에서 '사용자 지정 서식..'을 선택합니다.

    사용자 지정 서식으로 이동
    적용할 서식에서 사용자 지정 서식을 선택합니다.
  4. 글꼴 스타일을 굵게, 색상은 파란색을 선택합니다.

    굵은 파란글씨 서식 적용
    글꼴 스타일을 굵은 파란색 글씨로 변경합니다.
  5. '확인'을 눌러 마무리하면 체질량지수가 20과 24사이인 경우에 굵은 파란색 글씨로 서식이 적용됩니다.

    조건부서식 다음값 사이 완료
    체질량지수가 20과 24 사이인 경우에 조건부서식이 적용되었습니다.
특정 단어로 시작하는 경우 셀 배경을 노란색으로 강조하기(성이 이씨인 경우)
  1. 이름 범위를 선택한 뒤, 홈 - 조건부서식 - 새규칙을 선택합니다.

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

    수식을 사용하여 지정할 셀 결정
    수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.
  3. 엑셀 화면 좌측상단의 이름상자를 확인하여, 현재 선택된 범위에서 활성화 된 셀 주소를 확인합니다.

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

    새 규칙 추가
    조건부서식으로 적용 될 수식을 입력합니다.
  5. LEFT 함수에 입력된 셀주소에서, 숫자 앞의 $표시를 지워줍니다.

    혼합참조 변경
    B6셀에서 숫자 앞의 달러($) 표시를 지워줍니다.
  6. 서식을 클릭한 뒤, 채우기에서 배경색을 노란색으로 선택합니다.

    채우기 색상 변경
    서식으로 이동한 뒤, 채우기 색상을 노란색으로 변경합니다.
  7. '확인'을 눌러 마무리하면 특정 성씨를 가진 학생의 이름이 노란색 배경으로 강조되었습니다.

    엑셀 성을 만족하는 조건부서식 완료
    특정 성씨를 가진 학생의 조건부서식이 적용되었습니다.
여러개의 조건을 만족하는 조건부서식 적용하기(키가 145 미만이고 몸무게가 50 미만인 경우)
  1. 키(cm)와 몸무게(kg)범위를 선택합니다.

    엑셀 키 몸무게 조건부서식
    키와 몸무게 범위를 선택합니다.
  2. 홈 - 조건부서식 - 새규칙으로 이동한 뒤, '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.

    수식 사용 서식 지정 셀 결정
    수식을 사용하여 지정할 셀 결정을 선택합니다.
  3. 엑셀 화면 좌측상단의 이름상자를 확인하여, 현재 선택된 범위에서 활성화 된 셀 주소를 확인합니다.

    활성화 셀 확인
    화면 좌측상단 이름상자에서 활성화 된 셀 주소를 확인합니다.
  4. 조건부서식의 수식을 아래와 같이 입력합니다.
    =AND('셀주소'<$G$15,'옆의 셀주소'<$H$15)

    조건부서식 수식 입력
    조건부서식으로 적용 될 수식을 입력합니다.
  5. 셀주소와 옆의 셀주소에서 숫자 앞의 $표시를 지워줍니다.

    숫자 앞 $표시 제거
    C6셀, D6셀의 숫자 앞 달러($) 표시를 지워줍니다.
  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개인 신호등 모양의 아이콘집합을 선택합니다.

    아이콘 집합 4개 아이콘 선택
    조건부서식 - 아이콘집합에서 4개의 조건이 있는 도형을 선택합니다.
  3. 아이콘집합이 적용된 범위를 선택한 뒤, 조건부서식 - 규칙관리 - 규칙편집으로 이동합니다.

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

    아이콘집합 값 설정
    종류를 모두 숫자로 변경합니다. 이후 값을 그림과 같이 설정합니다.
  5. '확인'을 눌러 마무리하면 선택된 범위에 아이콘 집합이 적용되었습니다.

    아이콘집합 적용 완료
    아이콘집합 적용이 완료되었습니다.

조건부서식 사용시 주의사항

조건부서식은 셀의 값에 영향을 주지 않습니다. 또한 파일에 치명적인 영향을 미치는 것 또한 아니여서 사용시 크게 주의해야 할 점은 없습니다.

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

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

이번 강의에서는 부서명을 선택했을 때, 선택된 부서의 모든 직원이 강조되는 표를 만들어 보겠습니다.

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

    조건부서식 범위 선택
    B4 셀을 시작으로 부서명부터 연봉까지 범위를 선택합니다.
  2. 홈 - 조건부서식 - 새규칙에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.

    수식 사용하여 서식 지정
    수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.
  3. 화면 좌측의 이름상자를 확인하여, 선택된 범위에서 활성화 된 셀의 주소를 확인합니다.
    (B4셀이 활성화 된 것을 확인할 수 있습니다.)

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

    조건부서식 수식 입력 참조방식
    조건부서식으로 적용 될 수식을 입력합니다.
  5. 서식으로 이동한 뒤 채우기 색상을 옅은 노란색으로 변경합니다.

    채우기색상 변경
    서식을 클릭한 뒤, 채우기색상을 옅은 노란색으로 변경합니다.
  6. 확인을 눌러 마무리한 뒤, 부서명을 변경하면 표에서 해당 부서의 모든 직원이 실시간으로 강조됩니다.

    부서명 선택 시 셀 자동 강조
    부서를 선택하면 해당 부서의 직원이 강조됩니다.

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

4.9 14 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
21 Comments
Inline Feedbacks
View all comments
spring breeze
spring breeze
2020년 6월 20일 4:37 오후
게시글평점 :
     

완전 총정리네요 ㅎㅎ
항상 너무 많은것을 배우고 갑니다!!

포동뽀동
포동뽀동
2020년 6월 21일 3:48 오후
게시글평점 :
     

총정리 감사드려요^^ 기초를 탄탄하게 다지기에 정말 좋은것 같습니다!

지뉘
지뉘
2020년 6월 22일 3:28 오후
게시글평점 :
     

멋쪄요..

hinde****
hinde****
2020년 6월 22일 11:01 오후
게시글평점 :
     

잘봤습니다.감사합니다.

m****
m****
2020년 6월 30일 5:07 오후
게시글평점 :
     

좋은 강의 감사합니다.

뿌까뿌가
뿌까뿌가
2020년 6월 30일 5:43 오후
게시글평점 :
     

감사합니다. 많이 배우고 갑니다.

ssn****
ssn****
2020년 7월 5일 9:33 오후
게시글평점 :
     

잘 봣습니다

처소
처소
2020년 7월 7일 4:16 오후
게시글평점 :
     

감사합니다~~

엠니아
엠니아
2020년 7월 10일 11:47 오전
게시글평점 :
     

감사합니다.

홍비맘
홍비맘
2020년 7월 22일 4:21 오후
게시글평점 :
     

저는 filter함수가 없어요..... 2016 MSO 32비트
이번 강의는 pass 해야하나요....

달그림자
달그림자
2020년 9월 6일 5:07 오후
게시글평점 :
     

엑셀2016으로 P.3 어떻게 해야 할지 모르겠어요 ㅠ

앙리
앙리
2020년 9월 15일 3:31 오후
게시글평점 :
     

3.데이터 막대
fiter함수 및 sort 함수
오피스365 미사용자일 경우 공식 및 활용
따로 짧게 강의해주시면 좋을 것 같아요.
회사에서 매우 유용하게 사용할 수 있을 것 같습니다. ㅎ

앙리
앙리
2020년 9월 16일 6:36 오후
게시글평점 :
     

질문 있습니다. 만약 앞 이름 성씨 만을 합계를 낼수있는 countif 방법은 없을까요? (예를 들어, "김" 씨 성은 총 몇명일까)

앙리
앙리
2020년 9월 16일 11:42 오후

감사합니다. 완벽하게 됐네요~
위의 filter 및 sort 함수도 여러번 try 이후 성공 했습니다~

댄싱곰
댄싱곰
2020년 9월 28일 3:30 오후
게시글평점 :
     

안녕하세요, 강의 보면서 조건부 서식, 새 규칙에서 수식을 입력할 때
화살표를 누르면 셀이 선택이 되어서 셀주소가 자동으로 입력되는데
자동으로 입력되지 않게 하는 법이 있나요

Last edited 1 월 전 by 댄싱곰
명품깽여사
명품깽여사
2020년 10월 5일 2:01 오후
게시글평점 :
     

오늘도 1강 수강완료~~ 감사합니다.

kamt****
kamt****
2020년 10월 8일 9:18 오후
게시글평점 :
     

잘봤습니다. 활용해보고 싶어지네요.

미성
미성
2020년 10월 14일 1:46 오후
게시글평점 :
     

전 다시 봐야 겠네요
이해가 될때까지 봐야 할 거 같습니다
좋은 영상 감사합니다

알콩달콩이
알콩달콩이
2020년 10월 23일 8:40 오후
게시글평점 :
     

감사합니다.

서로해
서로해
2020년 10월 28일 11:25 오후
게시글평점 :
     

감사합니다. 너무나 자세히 설명 해 주셔서 이해가 쉽네요. 중년이 지나 배우려니 이해가 안되는 부분도 있네요? 이곳에서 =left($B6,1)=$G$12 ($B$6,1) 이곳만 ( )를 쓰는 이유는 무엇인지 이해가안됩니다. ( )의 규칙이나 활용법 좀… 더보기 »

21
0
여러분의 생각을 댓글로 남겨주세요.x