엑셀 조건부 서식으로 예정일이 지난 행에만 색 입히기

업무 목록을 보면서 끝난 항목까지 빨간색으로 보이면 놓친 일을 찾기 어렵습니다. 날짜가 비어 있지 않고 오늘보다 빠르며 상태가 완료가 아닌 행만 표시하는 조건부 서식을 만들어 봅니다. 행 전체의 적용 범위와 기준 날짜를 확인하는 것이 핵심입니다.

연습용 표의 열과 경계 날짜 정하기

A열에 작업 이름, B열에 예정일, C열에 상태를 적고 데이터는 2행부터 시작합니다. 예정일에는 어제, 오늘, 내일 값을 준비하고, 별도 행에는 빈 날짜와 완료 상태를 넣습니다. 표에서 보여 줄 기준이 ‘오늘까지’인지 ‘이미 지난 날’인지를 먼저 정합니다. 여기서는 오늘보다 이른 날만 강조합니다.

예정일을 직접 입력한 뒤 표시 형식만 날짜로 지정하면 원래 데이터가 날짜 값인지 확실치 않을 수 있습니다. 복사해 온 값이 텍스트로 저장됐다면 날짜 비교가 예상과 달라질 수 있으므로 대표 셀의 유형을 확인합니다. 보고용 날짜를 계산할 때 날짜와 시간까지 필요한지 정하고 오늘의 기준 시각도 팀에서 같은 방식으로 해석하도록 합니다.

셀 참조 확인. 첫 데이터 2행인지 확인 / 달러 표시 열 고정에 사용 / 행 번호 각 행으로 이동 / 규칙 범위 대상 영역과 일치
셀 참조 확인

선별 조건을 먼저 일반 셀에서 시험

빈 열에 =AND(ISNUMBER($B2),$B2<TODAY(),$C2<>"완료")를 넣어 TRUE와 FALSE가 기대와 같은지 확인합니다. 첫 비교는 날짜가 숫자 값으로 저장돼 있는지 확인합니다. 두 번째는 B열이 오늘보다 이른지, 세 번째는 C열 값이 완료와 같은지를 살핍니다. 상태에 공백이 붙었거나 다른 말로 입력됐으면 예상보다 많은 행이 표시될 수 있습니다.

B와 C 앞의 달러는 수식을 A열부터 C열에 걸쳐 적용해도 날짜는 B, 상태는 C를 보도록 고정합니다. 행 번호 앞에는 달러를 붙이지 않아 아래 행으로 갈 때 2, 3, 4로 이동합니다. 수식을 엑셀에 그대로 넣을 때는 현재 프로그램의 함수 인수 구분 방법도 확인합니다. 규칙 적용 전 첫 데이터 줄과 실제 적용 시작 위치를 맞춥니다.

적용 범위를 행 전체로 고르기

A2:C30처럼 강조할 범위를 선택합니다. 홈의 조건부 서식에서 새 규칙을 만들고 수식을 사용하여 서식을 지정할 셀을 결정하는 옵션을 고릅니다. 시험한 식을 입력하고 채우기 색을 정합니다. 상태 표현을 눈으로 구분하려면 너무 진한 배경보다 글자가 읽히는 대비를 고려합니다. 화면에서 색만 사용하는 경우 별도 상태 열이나 표시도 함께 확인합니다.

조건부 서식 관리자에서 사용된 범위와 수식의 시작 행을 봅니다. 범위가 A3에서 시작하는데 수식이 B2를 가리키면 색이 한 줄씩 밀릴 수 있습니다. 같은 시트에 비슷한 규칙이 있으면 어느 것이 우선 적용되는지 확인합니다. 직접 넣어 둔 색이나 기존 규칙의 순서 탓에 새 설정 결과가 예상과 달라 보일 수도 있습니다.

평가 날짜를 고정하는 경우에는 기준 셀에 실무에서 쓰는 날짜를 직접 넣고 수식에서 그 셀을 절대 참조하도록 조정할 수 있습니다. 예를 들어 매달 말에 저장하는 보고서라면 언제 열더라도 당시 비교 기준을 유지할 수 있습니다. 일일 목록의 TODAY 방식과 마감 보고용의 고정 날짜 방식이 어느 파일에 적용됐는지 표 제목에 기록합니다.

시험할 데이터. 어제 미완료만 색 표시 / 오늘 포함 기준 따로 결정 / 빈 예정일 표시 제외 / 완료 행 표시 안 되는지
시험할 데이터

오늘과 날짜 없는 행, 완료 행 검산

같은 파일에서 어제 미완료 행, 어제 완료 행, 오늘 미완료 행, 날짜가 비어 있는 행을 두고 확인합니다. 위 예에서는 어제 미완료 행만 표시됩니다. 오늘인 행도 강조해야 한다면 비교 조건을 의도에 맞게 수정한 뒤 이전 항목들과 함께 시험합니다. 서식이 되어 보이는 이유가 실제 값인지 이미 넣어 둔 배경색인지도 비교합니다.

상태의 입력 범위가 ‘완료’, ‘진행 중’, ‘보류’로 정해져 있으면 기록에 불필요한 공백이나 다른 문구가 섞이지 않는지 확인합니다. 완료 날짜를 별도 열에서 읽는 방식이라면 수식도 그 기준을 따르게 고칩니다. 표에서 보기 쉽게 쓰는 문구와 실제 조건에 사용하는 값을 혼동하지 않도록 합니다.

분기마다 표를 새로 만들면서 범위를 복사할 때는 적용 범위가 새 시트에 맞게 바뀌었는지 확인합니다. 첫 행에는 지난 날짜를 넣고 두 번째 행에서는 상태를 완료로 바꿔 색이 내려오는 위치를 살핍니다. 보고용으로 복제한 파일에서도 원본에 연결된 조건이 없고 의도한 색으로 표시되는지 시험하면 됩니다.

다음 날과 데이터 추가 후에도 동작 확인

TODAY는 날짜에 따라 결과가 달라질 수 있으므로 어제의 결과가 오늘도 같은 색으로 유지돼야 한다고 생각하지 않습니다. 보고가 필요한 기준 날짜를 고정해야 한다면 별도 기준 셀을 사용하고 그 날짜를 기록합니다. 자동 계산 설정도 확인해 시간을 지나 파일을 다시 열었을 때 갱신되는지 봅니다.

다음 줄에 새 작업을 추가해 규칙의 적용 대상에 포함되는지 확인합니다. 복사와 붙여넣기를 반복하면 조건부 서식 규칙이 여러 조각으로 나뉘어 관리될 수 있습니다. 관리자를 열어 새로 추가된 범위와 규칙을 정리하고, 중요한 자료를 PDF로 전달한다면 미리보기에서 글자와 색을 함께 확인합니다.

색을 바꾸기 전에 이미 사용하던 서식과 겹치는 경우도 시험합니다. 위험 표시와 완료 표시가 서로 다른 규칙이라면 시작 범위와 우선 순위를 따로 읽습니다.

참고 자료 Microsoft 엑셀 조건부 서식

댓글 쓰기