엑셀 조건부서식 원하는 행에 색 넣는 방법
부품 보증 기간을 엑셀로 관리하다 보면 날짜가 얼마 안 남은 항목만 눈에 띄게 만들고 싶을 때가 있습니다. 저도 SSD와 메모리 교체 이력을 적어두다가 조건부서식을 걸었는데, 엉뚱한 행에 색이 들어와 한참을 들여다본 적이 있습니다. 대부분 색을 고르는 단계보다 ‘어느 셀을 기준으로 판단할지’를 정하는 단계에서 틀어집니다.
먼저 색을 칠할 범위를 선택합니다
예시로 A열에는 부품명, B열에는 시리얼 번호, C열에는 보증 종료일, D열에는 상태를 적었다고 해보죠. 1행은 제목이고 실제 데이터는 2행부터 101행까지 있습니다. 보증 종료일이 가까운 부품의 행 전체를 칠하려면 A2:D101을 먼저 선택합니다. C열의 날짜만 칠하고 싶다면 C2:C101을 선택하면 됩니다.
홈 탭의 조건부 서식에서 셀 강조 규칙을 고르면 특정 값보다 크거나 작은 셀, 중복 값처럼 간단한 조건을 바로 적용할 수 있습니다. 하지만 C열의 날짜를 보고 A열부터 D열까지 한꺼번에 칠하려면 새 규칙에서 ‘수식을 사용하여 서식을 지정할 셀 결정’을 선택하는 편이 정확합니다. 수식은 선택한 범위의 첫 셀, 여기서는 A2를 기준으로 작성합니다.
보증 종료일이 30일 이내인 행 표시하기
A2:D101을 선택한 상태에서 새 규칙의 수식 칸에 =AND(ISNUMBER($C2),$C2>=TODAY(),$C2<=TODAY()+30)을 입력합니다. 이어서 서식에서 연한 채우기 색을 정하고 확인을 누릅니다. 오늘부터 30일 안에 보증이 끝나는 부품만 표시됩니다. 이미 보증이 끝난 항목은 제외됩니다.
여기서 $C2가 중요합니다. 달러 기호가 C 앞에 있으므로 A열이나 D열에 색을 칠할 때도 판단 기준은 C열로 고정됩니다. 반면 행 번호 2는 고정하지 않았기 때문에 3행에서는 C3, 4행에서는 C4를 확인합니다. $C$2처럼 행 번호까지 고정하면 모든 행이 C2만 바라보게 됩니다. 조건부서식이 여러 행에서 한꺼번에 잘못 표시될 때 제가 가장 먼저 확인하는 부분입니다.
ISNUMBER를 넣은 이유도 있습니다. 엑셀이 실제 날짜로 인식한 값은 숫자로 저장되지만, 붙여 넣은 날짜가 문자라면 비교 결과가 예상과 달라질 수 있습니다. C열에서 날짜 하나를 선택해 표시 형식을 바꿔도 날짜 계산이 되지 않는다면 문자로 들어왔는지 먼저 확인해야 합니다. 색이 안 나온다고 규칙부터 여러 개 만드는 것보다 입력값을 점검하는 편이 빠릅니다.
상태값과 중복 시리얼 번호에도 적용하기
D열에 ‘점검 필요’라고 적힌 부품의 행 전체를 표시하려면 A2:D101에 새 수식 규칙을 하나 더 만듭니다. 수식은 =AND($A2<>"",$D2="점검 필요")입니다. A열이 비어 있는 행은 빼고, D열의 상태가 정확히 일치할 때만 색이 들어갑니다. 상태값 앞뒤에 공백이 섞이면 일치하지 않으니, 표시가 빠진 행이 있으면 입력한 글자부터 확인합니다.
시리얼 번호 중복은 B2:B101만 선택한 뒤 조건부 서식의 ‘중복 값’을 써도 됩니다. 다만 비어 있는 칸까지 함께 눈에 띄는 경우라면 수식 규칙 =AND($B2<>"",COUNTIF($B$2:$B$101,$B2)>1)을 쓰는 편이 깔끔합니다. 비어 있지 않은 번호가 해당 범위에 두 번 이상 있을 때만 B열 셀에 색이 들어갑니다. 이 규칙은 행 전체가 아니라 시리얼 번호 칸에만 적용하는 게 확인하기 편했습니다.
색이 예상과 다르면 규칙 관리부터 확인합니다
조건부서식은 규칙을 만든 뒤 적용 범위가 달라지기 쉽습니다. 행을 복사하거나 일부 셀에 서식을 붙여 넣은 다음 특정 줄만 다른 색으로 보인다면 홈 탭의 조건부 서식에서 ‘규칙 관리’를 엽니다. 표시할 규칙을 현재 선택 영역 대신 현재 워크시트로 바꾸면 흩어진 규칙도 찾기 쉽습니다. 각 규칙의 적용 대상이 A2:D101인지, B2:B101인지 확인하고 필요하면 범위를 고칩니다.
한 셀이 여러 규칙에 동시에 해당하면 규칙 목록의 순서도 봐야 합니다. 마이크로소프트의 엑셀 안내에 따르면 서식이 충돌할 때는 목록에서 위에 있는 규칙의 우선순위가 높습니다. 예를 들어 ‘점검 필요’와 ‘보증 30일 이내’가 동시에 맞는 부품이라면 어느 색을 먼저 보여줄지 정해 규칙 순서를 조정하면 됩니다.
처음에는 테스트용 부품을 서너 줄만 넣어 보증 종료일을 어제, 오늘, 30일 뒤, 31일 뒤로 나눠 확인하는 게 좋습니다. 기대한 행에만 색이 들어오면 그때 실제 목록에 적용하면 됩니다. 조건부서식은 수식을 길게 만드는 기술보다 범위와 기준 셀을 정확히 맞추는 습관이 더 중요하다고 느낍니다.
