엑셀 조건부 서식 사용법

조건부 서식(Conditional Formatting)은 규칙에 따라 셀의 모양을 바꿔 줘요. 숫자를 하나하나 읽는 대신, 예산을 넘은 송장, 두 번 입력된 송장 번호, 이달의 가장 큰 금액처럼 살펴봐야 할 곳을 색이 짚어 주게 하는 거죠. 값이 바뀌면 서식도 저절로 바뀌어요.

예제는 송장 일곱 건의 목록이에요. 송장마다 금액과 예산이 있어요.

fx

	A	B	C	D
1	송장 번호	거래처	금액	예산
2	INV-1001	강변상사	2,400	2,000
3	INV-1002	한빛제조	1,800	2,200
4	INV-1003	강변상사	3,100	3,000
5	INV-1004	미래유통	1,200	1,500
6	INV-1005	바다식품	2,750	2,500
7	INV-1006	미래유통	950	1,000
8	INV-1002	한빛제조	1,800	2,200

이 글의 모든 규칙은 같은 곳에서 시작해요. 홈› then 스타일› then 조건부 서식이에요. 웹용 Excel에도 홈 탭의 조건부 서식에 같은 규칙이 있지만, 대화 상자 대신 오른쪽 창에서 규칙을 만들고 관리해요.

기준을 넘는 값 강조하기

셀 강조 규칙(Highlight Cells Rules)은 각 셀을 입력한 값과 비교해요. 금액이 2,500을 넘는 송장을 모두 표시해 볼게요.

1. 서식을 지정할 셀을 선택하세요

   금액이 있는 C2:C8을 선택하세요. 데이터만 선택하세요. 머리글까지 선택하면 규칙이 머리글에도 적용돼요.

2. 규칙을 고르세요

   홈› then 스타일› then 조건부 서식› then 셀 강조 규칙› then 보다 큼을 클릭하세요.

3. 값을 입력하고 서식을 고르세요

   보다 큼 대화 상자의 왼쪽 상자에 2500을 입력하세요. 오른쪽 서식은 진한 빨강 텍스트가 있는 연한 빨강 채우기 그대로 두거나 다른 서식을 고르고 확인을 클릭하세요.

   fx

   	A	B	C	D
   1	송장 번호	거래처	금액	예산
   2	INV-1001	강변상사	2,400	2,000
   3	INV-1002	한빛제조	1,800	2,200
   4	INV-1003	강변상사	3,100	3,000
   5	INV-1004	미래유통	1,200	1,500
   6	INV-1005	바다식품	2,750	2,500
   7	INV-1006	미래유통	950	1,000
   8	INV-1002	한빛제조	1,800	2,200

C2를 2,600으로 바꾸면 바로 빨간색으로 바뀌어요. 다시 되돌리면 색도 사라져요.

같은 메뉴에는 보다 작음, 다음 값의 사이에 있음, 같음, 텍스트 포함, 발생 날짜도 있어요. 숫자를 입력하는 대신 G1처럼 목표값이 있는 셀을 클릭해도 돼요. 엑셀이 =$G$1을 써 넣고, 규칙은 G1에 입력하는 값을 따라가요.

중복 값 찾기

송장 번호가 반복된다면 보통 같은 송장을 두 번 입력했다는 뜻이에요.

1. 확인할 열을 선택하세요

   송장 번호가 있는 A2:A8을 선택하세요.

2. 중복 값 규칙을 적용하세요

   홈› then 스타일› then 조건부 서식› then 셀 강조 규칙› then 중복 값을 클릭하세요. 중복 값(Duplicate Values) 대화 상자에서 중복이 선택된 그대로 두고 확인을 클릭하세요.

   fx

   	A	B	C	D
   1	송장 번호	거래처	금액	예산
   2	INV-1001	강변상사	2,400	2,000
   3	INV-1002	한빛제조	1,800	2,200
   4	INV-1003	강변상사	3,100	3,000
   5	INV-1004	미래유통	1,200	1,500
   6	INV-1005	바다식품	2,750	2,500
   7	INV-1006	미래유통	950	1,000
   8	INV-1002	한빛제조	1,800	2,200

이 규칙은 첫 번째 것까지 포함해 모든 사본을 강조해요. 그래서 두 행을 모두 보고 어느 쪽을 지울지 정할 수 있어요. 대화 상자의 첫 번째 상자를 고유로 바꾸면 반대로 한 번만 나오는 값을 강조해요.

조회와 마찬가지로 중복 검사는 영문 대소문자는 무시하지만 공백은 무시하지 않아요. 끝에 공백이 붙은 INV-1002 는 INV-1002의 중복으로 보지 않아요.

상위 값이나 평균 초과 값 강조하기

상위/하위 규칙(Top/Bottom Rules)은 고정된 숫자를 쓰지 않아요. 각 값을 범위의 나머지 값과 비교하기 때문에 데이터가 바뀌면 강조되는 셀도 달라져요.

1. 금액을 선택하세요

   다시 C2:C8을 선택하세요.

2. 평균 초과를 고르세요

   홈› then 스타일› then 조건부 서식› then 상위/하위 규칙› then 평균 초과를 클릭하고, 서식을 고른 다음 확인을 클릭하세요.

   fx

   	A	B	C	D
   1	송장 번호	거래처	금액	예산
   2	INV-1001	강변상사	2,400	2,000
   3	INV-1002	한빛제조	1,800	2,200
   4	INV-1003	강변상사	3,100	3,000
   5	INV-1004	미래유통	1,200	1,500
   6	INV-1005	바다식품	2,750	2,500
   7	INV-1006	미래유통	950	1,000
   8	INV-1002	한빛제조	1,800	2,200

일곱 금액의 합계는 14,000이고 평균은 2,000이에요. 평균보다 큰 송장이 세 건이에요.

같은 메뉴에는 상위 10개 항목, 상위 10%, 하위 10개 항목, 하위 10%, 평균 미만도 있어요. 이름과 달리 상위 10개 항목은 개수를 고를 수 있어요. 대화 상자의 10을 2로 바꾸면 가장 큰 두 금액인 3,100과 2,750만 강조돼요.

데이터 막대, 색조, 아이콘 집합으로 크기 보여 주기

이 세 가지 서식은 일부 셀만 골라 강조하지 않아요. 모든 셀에 그 값이 범위에서 어느 정도인지를 표시해요.

• 데이터 막대(Data Bars)는 셀마다 막대를 그려요. 가장 긴 막대는 가장 큰 값(여기서는 3,100)의 막대이고, 다른 막대는 그 길이를 기준으로 정해져요.
• 색조(Color Scales)는 셀을 그라데이션으로 칠해요. 예를 들어 녹색, 노랑, 빨강으로 이어지는 색조에서는 가장 큰 값이 녹색, 가장 작은 값이 빨간색이에요.
• 아이콘 집합(Icon Sets)은 값이 범위에서 어디쯤인지에 따라 화살표나 신호등 같은 작은 아이콘을 붙여요.

C2:C8을 선택하고 홈› then 스타일› then 조건부 서식› then 데이터 막대를 가리키세요. 마우스를 움직이는 동안 엑셀이 각 스타일을 셀에 미리 보여 줘요. 그라데이션 채우기나 단색 채우기에서 하나를 클릭해 적용하세요.

수식으로 규칙 직접 만들기

기본 규칙은 셀을 고정된 값이나 자기 범위와 비교해요. 금액과 예산처럼 같은 행의 다른 셀과 비교하려면 수식을 쓰세요. 수식 결과가 TRUE인 셀마다 서식이 적용돼요.

금액이 예산을 넘는 행 전체를 강조해 볼게요.

1. 왼쪽 위 셀부터 행을 선택하세요

   A2에서 D8까지 끌어서 A2:D8을 선택하세요. 그러면 A2가 활성 셀이 돼요. 입력하는 수식은 이 셀을 기준으로 쓰는 것이고, 엑셀이 선택 영역의 다른 셀에 맞게 수식을 조정해요.

2. 새 규칙을 여세요

   홈› then 스타일› then 조건부 서식› then 새 규칙을 클릭하고 수식을 사용하여 서식을 지정할 셀 결정을 고르세요.

3. 수식을 입력하세요

   다음 수식이 참인 값의 서식 지정 상자에 다음 수식을 입력하세요.

   =$C2>$D2복사

4. 서식을 고르고 확인하세요

   서식을 클릭하고 채우기 탭에서 색을 고른 다음 확인을 두 번 클릭하세요.

   fx

   	A	B	C	D
   1	송장 번호	거래처	금액	예산
   2	INV-1001	강변상사	2,400	2,000
   3	INV-1002	한빛제조	1,800	2,200
   4	INV-1003	강변상사	3,100	3,000
   5	INV-1004	미래유통	1,200	1,500
   6	INV-1005	바다식품	2,750	2,500
   7	INV-1006	미래유통	950	1,000
   8	INV-1002	한빛제조	1,800	2,200

핵심은 $ 기호예요. $C와 $D가 열을 고정하기 때문에 A열부터 D열까지 한 행의 모든 셀이 그 행의 금액과 예산을 확인해요. 행 번호에는 $가 없어서 4행은 $C4>$D4, 5행은 $C5>$D5를 확인하는 식이에요. 예산을 넘은 행은 2행(2,400 > 2,000), 4행(3,100 > 3,000), 6행(2,750 > 2,500)이에요.

다시 쓸 만한 수식 규칙 몇 가지예요.

규칙에 쓰기 전에 수식을 시험해 보려면 데이터 옆의 빈 열에 입력하고 아래로 복사하세요. 강조하고 싶은 행에서만 정확히 TRUE가 나와야 해요.

규칙 관리하고 순서 바꾸고 지우기

시트의 모든 규칙은 한곳에 모여 있어요. 홈› then 스타일› then 조건부 서식› then 규칙 관리를 클릭하면 조건부 서식 규칙 관리자가 열려요. 서식 규칙 표시를 현재 워크시트로 바꾸면 선택한 셀의 규칙뿐 아니라 시트의 모든 규칙이 보여요.

여기서 다음과 같은 일을 할 수 있어요.

• 규칙 편집으로 규칙의 조건이나 서식을 고쳐요.
• 적용 대상 상자에서 규칙이 적용되는 셀을 바꿔요.
• 위로 이동, 아래로 이동 화살표 단추로 순서를 바꿔요.
• 규칙 삭제로 규칙을 지워요.

규칙이 겹칠 때는 순서가 중요해요. 이 시트에서 C4는 보다 큼 규칙(빨간색 채우기)과 예산 초과 수식 규칙(노란색 채우기)에 모두 해당해요. 규칙은 목록 위에서부터 적용되고, 두 규칙이 채우기 색처럼 같은 항목을 지정하면 위쪽 규칙이 이겨요. 엑셀은 새 규칙을 목록 맨 위에 넣기 때문에, 순서를 바꾸기 전까지는 가장 나중에 만든 규칙이 이겨요. 하나는 채우기 색, 하나는 굵은 글꼴처럼 서로 다른 항목을 지정하는 규칙은 둘 다 적용돼요.

규칙 옆의 True일 경우 중지에 체크하면, 그 규칙이 참인 셀에는 그 아래 규칙을 더 이상 적용하지 않아요.

규칙을 없애려면 홈› then 스타일› then 조건부 서식› then 규칙 지우기에서 선택한 셀의 규칙 지우기나 시트 전체에서 규칙 지우기를 고르세요.

자주 하는 실수

• 수식 규칙의 $를 잘못 쓰기. 열 앞에 $가 없는 =C2>D2를 A2:D8에 적용하면 B2 셀은 D2>E2를 확인하게 되어, 행이 엉뚱하게 강조돼요. 행 단위 규칙은 행이 아니라 열을 고정하세요: =$C2>$D2.
• 엉뚱한 셀을 기준으로 수식 쓰기. 수식은 항상 선택 영역의 활성 셀을 기준으로 써요. D8에서 A2 방향으로 선택하면 활성 셀이 D8이 되어서, 2행 기준으로 쓴 수식이 엉뚱한 행을 확인해요. 왼쪽 위 셀부터 선택하세요.
• 머리글 행까지 포함하기. 선택 영역이 머리글부터 시작하면 규칙이 머리글도 확인해서 서식을 적용할 수 있어요. 데이터 셀만 선택하세요.
• 규칙이 쌓이기. 서식이 있는 셀을 복사해서 붙여 넣으면 규칙도 함께 복사돼서, 같은 규칙이 여러 조각으로 나뉘어 쌓일 수 있어요. 가끔 규칙 관리를 열어 필요 없는 규칙을 지우세요.
• 색이 너무 많기. 시트의 절반이 강조되어 있으면 아무것도 눈에 띄지 않아요. 실제 질문에 답하는 규칙 한두 개만, 서로 구분되는 서식으로 쓰세요.

연습

연습

관리하는 표에 규칙 적용하기

1. 정기적으로 업데이트하는 표를 여세요. 경비, 할 일, 재고, 매출 등 무엇이든 좋아요. 마땅한 게 없다면 이 글의 송장 일곱 건을 입력하세요.
2. 셀 강조 규칙으로 기준값 규칙을 하나 추가하세요. 한도를 넘는 금액이나 최소 수량보다 적은 재고 같은 거예요.
3. ID 열에 중복 값 규칙을 추가하거나, =$C2>$D2처럼 행 전체를 강조하는 수식 규칙을 추가하세요.
4. 숫자 열 하나에 데이터 막대를 추가하세요.
5. 규칙 관리를 열어 현재 워크시트로 바꾸고, 두 규칙이 겹치는 곳에서 어느 규칙이 이겨야 하는지 정하세요. 그 규칙을 맨 위로 옮기고 이유를 적어 두세요.

그다음 아래 시트에서 규칙 수식 세 개를 시험해 보세요. 규칙 안에서와 똑같이 각 수식이 TRUE나 FALSE를 돌려줘야 해요.

직접 해 보기3개 중 0개 완료

1. 1.E2에 예산 초과 규칙을 테스트해 보세요. C2의 금액이 D2의 예산보다 크면 TRUE가 나와야 해요.힌트 보기
2. 2.E5에 평균 초과 규칙을 테스트해 보세요. C5의 금액이 C2:C8의 평균보다 큰가요?힌트 보기
3. 3.E8에 중복 규칙을 테스트해 보세요. A8의 송장 번호가 A2:A8에 두 번 이상 나오면 TRUE가 나와야 해요.힌트 보기

E2fx수식 입력 셀 E2Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E
1	송장 번호	거래처	금액	예산	규칙 테스트
2	INV-1001	강변상사	2,400	2,000
3	INV-1002	한빛제조	1,800	2,200
4	INV-1003	강변상사	3,100	3,000
5	INV-1004	미래유통	1,200	1,500
6	INV-1005	바다식품	2,750	2,500
7	INV-1006	미래유통	950	1,000
8	INV-1002	한빛제조	1,800	2,200

실력 점검

• 값이 기준을 넘는 셀을 강조할 수 있나요?
• 열에서 중복 값을 강조할 수 있나요?
• 범위에서 상위 값이나 평균 초과 값을 강조할 수 있나요?
• 데이터 막대를 추가해서 값의 상대적인 크기를 한눈에 보여 줄 수 있나요?
• $를 제대로 써서 행 전체를 강조하는 수식 규칙을 만들고, 두 규칙이 겹칠 때 어느 쪽이 이길지 정할 수 있나요?