엑셀 조건부 서식, 수식으로 행 전체 색칠하는 법

엑셀 조건부 서식은 셀 값이나 수식 결과가 정해 둔 조건에 맞으면 채우기 색, 글꼴, 데이터 막대, 아이콘을 자동으로 바꿔 주는 기능입니다. 600행짜리 재고 현황표에서 안전재고 아래로 내려간 품목 20개를 눈으로 찾으면 10분이 넘게 걸리지만, 규칙 하나를 걸어 두면 파일을 여는 순간 색으로 보입니다. 값이 바뀌면 색도 따라 바뀌기 때문에 매주 손으로 칠하고 지우는 일이 없어집니다.

어려운 부분은 규칙을 만드는 순간입니다. 행 전체를 칠하려다 $ 하나를 잘못 붙여 엉뚱한 행이 칠해지거나, 여러 사람이 덧붙인 규칙이 서로 겹쳐 색이 뒤섞입니다. 아래에서 기본 메뉴부터 수식 규칙, 규칙 관리, 막힐 때 확인할 곳까지 재고표 한 장으로 따라가 봅니다.

엑셀 조건부 서식 메뉴와 규칙 종류

조건부 서식은 **[홈] 탭 → [스타일] 그룹 → [조건부 서식]**에 있습니다. 서식을 적용할 범위를 먼저 선택하고 메뉴를 엽니다. Microsoft 지원 문서는 규칙을 크게 다섯 갈래로 나눕니다.

여기에 없는 조건은 [새 규칙]에서 '수식을 사용하여 서식을 지정할 셀 결정'을 골라 직접 씁니다. 실무에서 "행 전체를 칠하고 싶다", "다른 열 값을 보고 칠하고 싶다"는 요구는 거의 모두 이 수식 규칙으로 풉니다.

값과 텍스트로 셀 강조하기

가장 쉬운 시작은 [셀 강조 규칙]입니다. 주문 금액 열을 선택하고 [보다 큼]에 100을 넣으면 100을 넘는 셀만 연한 빨강으로 바뀝니다.

기준값 칸에 숫자 대신 $C$1 같은 셀 주소를 넣으면, 기준 셀만 바꿔도 강조가 함께 바뀝니다. 담당자가 매달 기준을 바꾸는 표라면 이렇게 두는 편이 규칙을 다시 열 일이 없습니다. [텍스트 포함]은 "긴급", "반품" 같은 단어가 들어간 셀을, [발생 날짜]는 어제, 오늘, 지난 7일, 이번 달 같은 기간을 고르게 해 줍니다.

수식으로 행 전체 색칠하기

셀 하나가 아니라 그 행 전체를 칠하려면 수식 규칙을 씁니다. 순서는 다음과 같습니다.

1. 머리글을 뺀 데이터 전체를 선택합니다. 예: A2:H600.
2. [조건부 서식] → [새 규칙] → '수식을 사용하여 서식을 지정할 셀 결정'을 고릅니다.
3. '다음 수식이 참인 값의 서식 지정' 칸에 =$D2="보류"처럼 입력합니다.
4. [서식]에서 채우기 색을 고르고 [확인]을 누릅니다.

핵심은 $ 위치입니다. 수식은 선택 범위의 활성 셀(보통 왼쪽 위 A2)을 기준으로 쓰고, 엑셀은 이 수식을 범위 안의 나머지 셀에 상대적으로 옮겨 적용합니다. $D2는 열을 D로 고정하고 행은 따라 내려가게 하므로, A2부터 H2까지 같은 행의 모든 셀이 D2를 보고 판단합니다. $D$2라고 쓰면 600행 전체가 D2 하나만 보게 되어 전부 칠해지거나 전부 안 칠해지고, D2라고만 쓰면 B열 셀은 E열을, C열 셀은 F열을 보게 되어 무늬처럼 엉뚱하게 칠해집니다.

수식 결과는 TRUE나 FALSE여야 합니다. 조건 두 개를 함께 걸 때는 =AND($D2="출고대기",$F2<TODAY()), 둘 중 하나만 맞아도 될 때는 OR를 씁니다. 다른 시트의 셀도 참조할 수 있어서 =$B2<>단가표!$B2처럼 두 시트를 비교하는 데도 쓰입니다.

줄무늬(격행) 색칠: =MOD(ROW(),2)=0

긴 목록에서 줄을 헷갈리지 않게 한 줄 걸러 색을 넣는 방법입니다. ROW()는 그 셀의 행 번호, MOD(행 번호,2)는 2로 나눈 나머지라서 짝수 행에서만 TRUE가 됩니다. Microsoft 지원 문서의 대체 행 음영 안내도 같은 수식을 씁니다.

손으로 칠한 줄무늬는 행을 넣거나 지우면 어긋나지만, 이 규칙은 행 번호로 다시 계산하므로 항상 맞습니다. 세 줄마다 칠하려면 =MOD(ROW()-2,3)=0처럼 시작 행을 빼 줍니다. 범위를 [홈] → [표로 서식 지정]으로 바꿀 수 있다면 '줄무늬 행' 옵션이 같은 일을 하므로 규칙을 따로 둘 필요가 없습니다.

데이터 막대, 색조, 아이콘 집합

숫자의 크고 작음을 한눈에 비교할 때는 색을 켜고 끄는 규칙보다 이 세 가지가 낫습니다. 데이터 막대는 셀 안에 값 크기만큼 막대를 그려 품목별 재고량이나 거래처별 미수금을 막대그래프처럼 보여 줍니다. 숫자는 감추고 막대만 보이게 하려면 [규칙 관리] → [규칙 편집]에서 '막대만 표시'를 선택합니다. 색조는 가장 작은 값부터 가장 큰 값까지 색이 짙어져, 월별 라인별 불량 건수를 히트맵처럼 볼 때 씁니다.

아이콘 집합은 기본값이 백분율 기준(상위 67%, 33%)이라, 목표 대비 달성률처럼 기준이 정해진 숫자에는 [규칙 편집]에서 종류를 '숫자'로 바꾸고 경계값을 직접 넣어야 의도대로 나옵니다. 예를 들어 달성률이 1 이상이면 초록, 0.8 이상이면 노랑, 그 아래는 빨강으로 정해 두면 매달 데이터가 바뀌어도 기준이 흔들리지 않습니다.

재고 현황표에 조건부 서식 적용하기 (예시)

세진물산 물류팀은 매주 월요일 재고 현황표를 점검합니다. 표에는 품목코드(A), 품목명(B), LOT(C), 가용재고(D), 안전재고(E), 유통기한(F), 최근 출고일(G), 창고(H)가 있고 데이터는 2행부터 600행까지입니다. 담당자가 매주 보는 것은 세 가지입니다. 발주가 필요한 품목, 유통기한이 다가오는 LOT, 석 달 넘게 안 나간 악성 재고입니다.

1. 규칙 네 개를 정합니다

A2:H600을 선택한 상태에서 아래 순서로 수식 규칙을 만듭니다. 위에 쓴 규칙일수록 먼저 적용되도록 마지막에 순서를 정리합니다.

• 유통기한 경과: =$F2<TODAY() → 진한 빨강 채우기, 흰 글꼴
• 안전재고 미달: =$D2<$E2 → 연한 빨강 채우기
• 유통기한 30일 이내: =AND($F2>=TODAY(),$F2<=TODAY()+30) → 주황 채우기 (적용 대상은 F2:F600만)
• 90일 무출고: =TODAY()-$G2>90 → 회색 글꼴

유통기한 규칙만 F열에 걸면, 품목 행 전체는 재고 상태를 보여 주고 유통기한 칸은 날짜 상태를 따로 보여 줍니다. 한 행이 두 가지 신호를 동시에 낼 수 있게 범위를 나누는 것입니다.

2. 규칙 관리자에서 순서를 확인합니다

[조건부 서식] → [규칙 관리]를 열고 '서식 규칙 표시'를 '이 워크시트'로 바꿉니다. 지난달 누가 만든 "재고 0이면 빨강" 규칙이 A2:H350에만 걸려 남아 있다면 여기서 보입니다. 같은 뜻의 규칙은 지우고, 적용 대상이 쪼개진 규칙은 하나로 합칩니다. 유통기한 경과 규칙은 맨 위로 올립니다.

3. 결과를 검산합니다

칠해진 행 두세 개와 안 칠해진 행 하나를 골라 숫자를 직접 비교합니다. 가용재고가 비어 있는 행은 0으로 계산되어 안전재고 미달로 칠해지므로, 빈 셀을 빼려면 =AND($D2<>"",$D2<$E2)로 고칩니다. 날짜가 텍스트로 들어온 행은 TODAY()와 비교가 안 되어 색이 들어가지 않으니, 날짜 열이 왼쪽 정렬로 보이면 먼저 날짜 형식부터 바로잡습니다.

4. 색으로 걸러 발주 목록을 만듭니다

조건부 서식으로 칠한 색도 필터 기준이 됩니다. 머리글에 필터를 걸고 가용재고 열의 [색 기준 필터]에서 연한 빨강을 고르면 발주가 필요한 품목만 남습니다. 이 목록을 복사해 공급처별로 정렬하면 그날 보낼 발주 목록이 됩니다.

👉 엑셀 필터 사용법, 자동 필터부터 고급 필터까지

조건부 서식 규칙이 안 먹을 때 확인할 곳

앞에서 본 Microsoft 지원 문서의 규칙 우선순위 설명에 따르면 같은 범위에 규칙이 여럿이면 목록의 위쪽 규칙이 먼저 평가되고, 새 규칙은 기본으로 목록 맨 위에 추가됩니다. 그래서 순서를 따로 정하지 않으면 마지막에 만든 규칙이 이깁니다. 서식이 서로 부딪치지 않으면(한 규칙은 굵은 글꼴, 다른 규칙은 채우기) 둘 다 적용되고, 부딪치면(둘 다 채우기 색) 위쪽 규칙이 이깁니다. 색이 기대와 다르면 대부분 아래 다섯 가지 중 하나입니다.

복사와 붙여넣기도 자주 문제를 만듭니다. 서식이 걸린 셀을 다른 곳에 붙여 넣거나 행을 잘라 옮기면, 같은 규칙이 적용 대상만 다르게 여러 개로 쪼개집니다. 규칙이 수십 개로 늘어난 파일은 스크롤이 느려지므로, 분기마다 규칙 관리자를 열어 같은 수식의 규칙을 하나로 합쳐 두면 좋습니다. 붙여 넣을 때 [선택하여 붙여넣기] → [값]을 쓰면 원본 서식이 따라오지 않습니다.

규칙을 지울 때는 [조건부 서식] → [규칙 지우기]에서 '선택한 셀의 규칙 지우기'와 '시트 전체에서 규칙 지우기' 중 고릅니다. 시트 전체를 지우면 다른 사람이 만든 규칙까지 사라지므로, 지우기 전에 규칙 관리자에서 무엇이 걸려 있는지 먼저 봅니다.

버전별 차이

Microsoft 365, Excel 2021, 2019, 2016은 메뉴 이름과 위치가 같습니다. 범위를 선택하면 오른쪽 아래에 뜨는 [빠른 분석] 단추(Ctrl+Q)의 [서식] 탭에서도 데이터 막대, 색조, 보다 큼 같은 규칙을 바로 걸 수 있습니다. 웹용 Excel에서도 [홈] → [조건부 서식]으로 같은 규칙을 만들고 편집합니다. Excel 97-2003 형식(.xls)은 셀 하나에 조건을 세 개까지만 저장하므로, 규칙이 많은 표는 .xlsx로 저장해 둡니다.

👉 엑셀 중복값 찾기, 조건부 서식과 COUNTIF로 표시

엑셀 조건부 서식 자주 묻는 질문 (FAQ)

Q. 조건부 서식을 다른 범위나 다른 파일로 복사할 수 있나요?

서식이 걸린 셀을 선택하고 [홈] 탭의 [서식 복사](붓 아이콘)를 누른 뒤 대상 범위를 드래그하면 규칙까지 복사됩니다. 다른 파일로 옮길 때도 같은 방법이 통하며, 수식이 다른 시트를 참조하면 붙여 넣은 파일에도 같은 이름의 시트가 있어야 합니다.

Q. 다른 셀 값에 따라 이 셀 색을 바꿀 수 있나요?

수식 규칙으로 가능합니다. C열 셀을 선택하고 =$E2="반품"처럼 E열을 보는 수식을 쓰면, E열 값에 따라 C열 셀 색이 바뀝니다.

Q. 조건부 서식 색도 인쇄되나요?

일반 채우기 색과 똑같이 인쇄됩니다. 흑백 프린터에서는 회색 농도로만 구분되므로, 출력용 표는 색과 함께 굵은 글꼴이나 아이콘을 같이 걸어 두면 읽기 쉽습니다.

Q. 오늘 날짜 기준 규칙은 매일 자동으로 바뀌나요?

TODAY()는 파일을 열거나 다시 계산할 때마다 오늘 날짜로 바뀌므로, 유통기한 임박 규칙도 매일 새 기준으로 칠해집니다.

inline AI로 재고표 표시 규칙 맡기기

inline AI는 재고표를 연 채로 "안전재고보다 적은 행은 빨간색, 유통기한 30일 이내는 주황색"처럼 말하면 수식 규칙을 만들고, 이미 걸려 있던 규칙과 겹치는 부분까지 정리합니다. ARP를 함께 쓰면 안전재고 아래로 내려간 품목은 색으로 표시되는 데서 그치지 않고 공급처에 발주서까지 보냅니다.