엑셀 조건부 서식이 엉뚱한 행을 색칠할 때

엑셀 조건부 서식이 다른 행을 색칠한다면 어떤 셀을 검사하는 수식인지와 어느 셀부터 색을 적용하는지를 함께 확인한다. 상태가 있는 D열을 검사하더라도 색칠할 범위는 A열부터 시작할 수 있다. 이때 열은 D로 고정하고 행 번호는 각 업무에 맞춰 움직이게 해야 한다.

Microsoft Q&A의 실제 질문에서는 강의 등록률이 기준을 넘는 셀을 강조하려 했는데, 해당 묶음의 바로 위 셀부터 색이 들어가고 마지막 셀이 빠졌다. 질문에 적힌 수식은 2행의 값을 읽고 적용 대상은 C열 전체였다. 아래에서는 같은 종류의 어긋남을 확인하기 쉬운 작은 업무표로 바꿔 설명한다. 질문자의 원본 자료가 아닌 설명용 예제다.

완료된 두 업무의 행 전체 색칠하기

먼저 D열이 ‘완료’인 업무만 초록색으로 표시한다. 대상은 상태 셀 하나가 아니라 같은 업무의 A열부터 D열까지다. Windows용 Microsoft 365 Excel에서 새 시트의 A1:D7에 다음 자료를 넣는다고 가정한다. 표의 ‘시트 행’은 위치 설명이며 입력할 데이터 열이 아니다.

시트 행	A: 업무 ID	B: 업무	C: 마감일	D: 상태
2	T-201	견적 확인	2026-10-26	진행
3	T-202	자료 전달	2026-10-25	완료
4	T-203	시안 검토	2026-10-27	진행
5	T-204	일정 협의	빈칸	대기
6	T-205	계정 전달	2026-10-29	완료
7	T-206	발주 확인	텍스트 2026-10-24	진행

C2:C4와 C6에는 실제 날짜 값을 입력한다. C5는 아무것도 입력하지 않고, C7은 날짜 앞에 작은따옴표를 붙여 '2026-10-24로 입력한다. 빈칸과 텍스트 날짜는 뒤에서 날짜 조건을 만들 때 따로 검사한다. 지금 완료 상태를 검사하는 데에는 마감일이 영향을 주지 않는다.

1. A2를 클릭하고 Shift를 누른 채 D7을 클릭해 A2:D7을 선택한다. 기준이 되는 활성 셀은 A2로 둔다.
2. 홈 → 조건부 서식 → 새 규칙에서 ‘수식을 사용하여 서식을 지정할 셀 결정’을 고른다.
3. 수식에 =$D2="완료"를 입력하고 서식의 채우기에서 초록색을 정한다.
4. 확인 후 규칙 관리에서 적용 대상이 =$A$2:$D$7인지 확인한다.

이 자료에서 기대하는 초록색 범위는 A3:D3과 A6:D6이다. 상태 열만 칠해졌다면 적용 대상이 D2:D7인지 확인한다. 제목 행까지 칠해졌다면 적용 대상에 1행을 포함했는지 살핀다. 수식의 조건과 색칠할 범위를 따로 적어 놓으면 한쪽을 바꾸다가 다른 쪽까지 고치는 일을 줄일 수 있다.

수식 규칙은 조건이 참인지 거짓인지 판정하고, 참인 셀에 지정한 서식을 적용한다. 직접 칠한 초록색과 달리 상태를 바꾸면 조건에 따른 색도 달라진다. 메뉴와 수식 규칙의 동작은 Microsoft 조건부 서식 안내에서 확인할 수 있다.

수식은 2행, 적용은 1행에서 시작한 경우

A1을 기준으로 A1:D7을 선택해 규칙을 만들면서 수식은 =$D2="완료"로 넣었다고 하자. 이 규칙은 A1을 색칠할 때 한 줄 아래의 D2를 본다. A2에서는 D3를 보고, A5에서는 D6를 본다. 그래서 예제에서 완료된 3행·6행 대신 2행·5행에 초록색이 들어가는 계산이 된다.

이 경우에는 홈 → 조건부 서식 → 규칙 관리에서 해당 규칙의 적용 대상을 =$A$2:$D$7로 바꾼다. 이어 A2를 선택한 상태에서 해당 규칙의 편집 창을 열어 수식을 =$D2="완료"로 맞추고 적용한다. 머리글을 제외하고 첫 업무부터 자기 행의 상태를 검사하게 하는 수정이다. 데이터가 5행부터 시작하는 다른 파일이라면 A5를 기준으로 수식은 =$D5="완료", 적용 대상도 A5부터 시작하도록 맞춘다.

행 번호에 달러 기호를 붙여 어긋남을 없애려 하면 다른 문제가 생긴다. =$D$2="완료"는 2행만 검사하므로 3행이 완료돼도 그 값을 보지 않는다. 예제에서 D2는 진행이므로 어느 업무도 완료 색을 얻지 못한다. 나중에 D2를 완료로 바꾸면 적용 범위 전체가 조건을 만족하게 된다.

조건이 맞는지 눈으로 따라가기 어렵다면 빈 F2에 =$D2="완료"를 일반 수식으로 입력하고 F7까지 아래로 채운다. 예상 결과는 FALSE, TRUE, FALSE, FALSE, TRUE, FALSE다. F3·F6은 TRUE인데 색이 2행·5행에 있다면 판정할 데이터는 맞고 서식의 위치를 연결한 방식이 어긋난 것이다. 이 보조 열은 서식의 다른 규칙까지 검사하지는 않는다.

D열만 고정하고 행은 바뀌게 하기

$D2는 열 D를 고정하고 행 2는 상대 위치로 남긴 참조다. 같은 행의 A열이나 B열을 칠할 때도 D열의 상태를 검사한다. 다음 행으로 내려가면 D3, 그다음에는 D4를 검사한다. 절대·상대·혼합 참조 설명에서 달러 기호는 바로 뒤의 열 문자나 행 번호를 고정한다.

A2를 기준으로 쓴 참조	B2를 판정할 때	A3를 판정할 때	이 업무표에서 생기는 차이
D2	E2	D3	가로로 갈 때 상태 열에서 벗어난다.
$D2	D2	D3	각 업무의 상태로 그 행을 함께 판정한다.
D$2	E2	D2	가로 참조는 움직이고 세로 참조는 2행에 남는다.
$D$2	D2	D2	모든 셀이 첫 업무의 상태만 검사한다.

달러 기호를 모두 붙이는 것이 더 정확한 수식은 아니다. 판정하려는 데이터가 움직여야 하는 방향을 먼저 정해야 한다. 이 표에서는 업무마다 상태가 다르므로 행은 바뀌어야 한다. 반대로 기준 금액이나 기준 날짜 하나를 모든 업무가 함께 쓰는 경우에는 그 기준 셀의 열과 행을 모두 고정한다.

수식을 입력할 때 마우스로 셀을 누르면 절대 참조가 들어갈 수 있다. D2를 클릭한 결과가 $D$2라면 상태 비교에서는 행 번호 앞의 달러 기호를 뺀다. 셀을 찍었다는 이유로 의도한 혼합 참조까지 완성되는 것은 아니다. 앞의 표처럼 같은 행의 옆 셀과 바로 아래 셀을 각각 대입해 보면 무엇을 고정했는지 확인할 수 있다.

날짜가 없는 업무를 지연으로 칠하지 않기

이번에는 마감일이 지났고 아직 완료되지 않은 업무에 빨간색을 넣는다. 먼저 G1에 =DATE(2026,10,27)을 입력하고 날짜 서식을 지정한다. 여기서는 ‘2026년 10월 27일보다 앞선 마감일’을 지연으로 정한다. 당일 마감인 T-203은 포함하지 않는다. 매일 열어 쓰는 파일이라면 G1을 =TODAY()로 바꿀 수 있지만, 아래 예상 결과를 비교할 때는 고정 날짜를 사용한다.

=IFERROR(AND(ISNUMBER($C2),$C2<$G$1,$D2<>"완료"),FALSE)

A2:D7을 A2부터 선택해 이 수식의 새 규칙을 만들고 빨간색 채우기를 지정한다. AND 안에는 세 조건이 있다. 마감일이 숫자 값인지, G1보다 이전인지, 상태가 완료가 아닌지를 모두 검사한다. C열과 D열은 고정하되 행은 이동하고, 모든 행이 함께 쓸 G1은 $G$1로 고정한다.

예제에서 빨간색이 될 업무는 T-201 하나다. T-202는 날짜가 지났어도 완료됐고, T-203은 기준일 당일이다. T-204는 마감일이 없으며, T-206의 날짜는 텍스트로 입력했으므로 이 규칙에서 제외된다. T-205는 완료이면서 마감일도 기준일 뒤에 있다. 업무 ID별로 이유를 비교하면 날짜 조건을 잘못 이해했는지, 좌표가 틀렸는지 나눠 볼 수 있다.

ISNUMBER는 값을 숫자로 바꾸지 않고 자료형만 검사한다. 따라서 C7이 날짜처럼 보이더라도 FALSE가 나올 수 있다. 이 결과는 T-206의 마감이 지나지 않았다는 뜻이 아니다. 계산에 쓸 날짜 값으로 확인되지 않았다는 뜻이다. 실제 업무에서는 C7의 원본 날짜를 확인해 올바른 날짜로 다시 입력한 뒤 색을 확인한다.

반대로 숫자 1을 입력해도 ISNUMBER는 TRUE다. 숫자 여부만으로 정상적인 업무 날짜까지 검증한 것은 아니다. 날짜가 들어갈 열에 금액이나 임의의 번호가 섞여 있다면 데이터를 먼저 고친다. 상태가 ‘완료’ 대신 다른 표현으로 들어간 경우에도 그 표현을 완료로 취급할지 정한 뒤 조건을 바꾼다.

바깥의 IFERROR는 판정식에서 오류가 생기면 FALSE를 반환하게 한다. 원본 마감일이나 상태에 오류가 있더라도 이 빨간색 규칙이 오류 값을 그대로 반환하지 않게 하는 처리다. 원본 오류 자체를 고치지는 않으므로, 색이 없다는 이유만으로 해당 업무를 정상·완료로 취급하지 않는다.

TODAY는 현재 날짜를 반환하며 재계산 설정의 영향을 받는다. 매일 지연 업무를 찾는 표는 오늘 날짜를 쓰고, 월말 당시의 판정을 보존하는 표는 G1에 마감 기준일을 고정하는 식으로 목적을 정한다. 나중에 같은 파일을 열었을 때 색이 달라져도 되는지가 두 방식의 선택 기준이다.

맞는 수식인데 다른 색이 보일 때

완료 초록색과 지연 빨간색은 앞의 조건대로라면 같은 업무에서 겹치지 않는다. 빨간색 수식에 ‘완료가 아님’을 넣었기 때문이다. 하지만 이전에 만들어 둔 ‘마감일이 지났으면 빨강’ 규칙이 남아 있다면 완료된 T-202도 두 규칙을 동시에 만족한다.

이때 규칙을 하나 더 만들어 색을 덮기보다 규칙 관리에서 이 워크시트의 규칙을 표시해 기존 조건을 읽는다. 같은 채우기 색 속성이 충돌하면 목록에서 위에 있는 규칙이 우선한다. 두 규칙이 모두 필요한 경우에는 업무 의도에 맞게 순서를 조정하고, 불필요한 옛 규칙은 해당 항목만 제거한다. Microsoft의 규칙 우선순위 설명은 충돌하는 속성과 함께 적용 가능한 속성을 구분한다.

예를 들어 완료 행을 초록색으로 채우는 규칙과 특정 담당자의 글씨를 굵게 만드는 규칙은 서로 다른 속성을 바꾼다. 두 표시를 함께 쓰려는데 완료 규칙에 ‘참이면 중지’를 켜면 아래 규칙의 판정을 막을 수 있다. 이 옵션은 우선순위를 올리는 버튼이 아니라, 해당 조건이 참일 때 뒤의 규칙 평가를 멈추는 옵션이다.

옛 규칙 하나를 고치려다 시트 전체의 규칙 지우기를 누르면 다른 업무에 쓰던 강조도 사라진다. 수식·색·적용 대상이 문제와 연결되는 규칙을 찾아 편집한다. 보조 열의 TRUE/FALSE는 맞는데 화면 색이 다를 때에는, 같은 위치에 적용된 다른 규칙이 있는지 확인하는 순서가 유효하다.

새 업무와 붙여넣기 뒤에 확인할 결과

현재 적용 대상은 A2:D7이다. 8행에 T-207, 검수 요청, 날짜 2026-10-26, 상태 대기를 추가했다면 두 규칙 모두 새 행까지 포함하는지 확인한다. 범위가 그대로라면 =$A$2:$D$8로 늘린다. 기준 셀은 여전히 A2이므로 수식의 시작 행은 2로 유지한다. 범위를 늘린다고 수식까지 D8로 바꾸면 첫 행에서부터 다른 업무를 검사하게 된다.

다른 표에서 서식까지 복사하면 도착 범위에 새 조건부 서식 규칙이 생길 수 있다. 기존 업무표의 표시 규칙을 유지하며 데이터만 가져올 때는 값 붙여넣기를 사용하고, 규칙 관리에서 적용 대상이 여러 조각으로 나뉘었거나 비슷한 규칙이 늘었는지 읽는다. 값 붙여넣기는 수식의 계산 결과를 가져오는 방식이기도 하므로 원본 수식까지 유지하려는 작업에는 그대로 적용하지 않는다.

설명용 표에서 상태 변경과 새 행 추가의 결과를 비교한다. T-201의 상태를 완료로 바꾸면 빨간색 대상에서 빠지고 초록색 대상에 들어가야 한다. 앞서 추가한 T-207은 기준일보다 마감이 이르고 대기 상태이므로 빨간색 대상이다. 기존 T-202·T-205도 계속 초록색이어야 한다. 이렇게 바뀌어야 할 업무와 그대로여야 할 업무를 함께 비교하면, 전체가 한 셀의 상태를 따라가는 잘못된 고정 참조도 드러난다.