엑셀 조건부서식 방법으로 점검표 쉽게 만드는 법

얼마 전 차량 정비 이력표를 검토했는데, 점검 예정일이 지난 차량을 찾으려고 날짜 열을 한 줄씩 읽어야 했습니다. 엑셀 조건부 서식을 적용하니 기한이 지난 행에 색이 표시돼 훨씬 빠르게 확인할 수 있었죠. 셀 값을 바꿀 때 표시도 함께 바뀌므로, 계속 업데이트하는 관리표에 특히 유용합니다.

범위와 기준부터 정하면 설정이 쉬워집니다

엑셀 조건부서식 방법의 출발점은 ‘어디에’ ‘어떤 조건으로’ 색을 넣을지 정하는 것입니다. 예를 들어 차량별 정비 비용이 B열에 있고 데이터가 2행부터 100행까지 있다면 B2:B100을 먼저 선택합니다. 그런 다음 홈 탭에서 조건부 서식, 셀 강조 표시 규칙, 보다 큼을 차례로 선택하세요. 기준값에 100000을 입력하고 눈에 띄는 채우기 색을 고르면 10만 원을 초과한 비용만 표시됩니다. 정확히 10만 원인 셀은 포함되지 않는다는 점도 기억해 두면 좋습니다.

범위를 선택할 때는 제목 행을 빼는 편이 안전합니다. 비용 열에 ‘정비 비용’이라는 제목까지 포함하면 규칙을 확인하거나 수정할 때 적용 범위를 읽기 어려워집니다. 또 숫자처럼 보이는 값이 실제로는 문자로 저장돼 있으면 기대한 결과가 나오지 않을 수 있습니다. 셀의 값과 표시 형식을 먼저 확인하세요.

중복값과 큰 숫자는 메뉴만으로 표시할 수 있습니다

차량번호가 두 번 입력됐는지 확인할 때

차량번호가 C2:C100에 있다면 그 범위를 선택하고 홈, 조건부 서식, 셀 강조 표시 규칙, 중복 값을 누릅니다. 같은 값이 두 번 이상 들어간 셀을 함께 표시하므로 입력 오류를 찾기 편합니다. 이 기능은 중복 항목에 색을 붙일 뿐, 데이터를 삭제하지는 않습니다. 공백이나 띄어쓰기가 섞인 차량번호는 겉보기에 비슷해도 다른 값으로 취급될 수 있으니 입력 방식을 통일하는 것이 좋습니다.

비용의 크기를 한눈에 비교할 때

정비 비용의 분포를 보고 싶다면 B2:B100을 선택한 뒤 조건부 서식의 데이터 막대를 사용해 보세요. 숫자가 큰 셀일수록 막대가 길게 나타납니다. 다만 막대는 선택한 범위 안의 값끼리 비교해 표시합니다. 이번 달 비용만 선택했을 때와 1년치 비용을 선택했을 때 같은 금액의 막대 길이가 달라질 수 있으므로, 비교할 자료를 먼저 한 범위로 묶는 게 좋습니다. 정확한 기준 금액을 넘었는지 판단해야 한다면 데이터 막대보다 ‘보다 큼’ 규칙이 더 분명합니다.

행 전체를 칠하려면 수식 규칙을 쓰세요

차량번호, 차종, 점검 예정일, 진행 상태를 함께 보는 표에서는 날짜 셀 하나보다 해당 행 전체에 색을 넣는 편이 읽기 쉽습니다. A열부터 E열까지 데이터가 있고 D열이 점검 예정일, E열이 진행 상태라고 가정해 보겠습니다. A2:E100을 선택한 다음 홈, 조건부 서식, 새 규칙에서 ‘수식을 사용하여 서식을 지정할 셀 결정’을 선택합니다. 수식 입력란에는 =AND(ISNUMBER($D2),$D2<TODAY(),$E2<>"완료")를 넣고 채우기 색을 지정합니다.

이 규칙은 D열에 실제 날짜 값이 있고, 그 날짜가 오늘보다 이전이며, E열의 상태가 ‘완료’가 아닌 행을 표시합니다. 오늘이 예정일인 차량까지 표시하려면 $D2<TODAY()를 $D2<=TODAY()로 바꾸면 됩니다. TODAY()는 현재 날짜를 반환하므로 날짜가 바뀌거나 통합 문서가 다시 계산될 때 표시 대상도 달라질 수 있습니다.

수식의 $D2에서 달러 기호는 D열을 고정하고, 행 번호 2는 각 행에 맞춰 이동하게 합니다. 적용 범위를 A2:E100으로 잡았으므로 첫 데이터 행인 2행을 기준으로 써야 합니다. 실수로 $D$2처럼 행까지 고정하면 모든 행이 D2의 날짜만 검사하게 됩니다. 조건은 맞는데 색이 엉뚱한 곳에 나타난다면 적용 범위의 첫 행과 수식의 행 번호부터 확인하세요.

색이 이상하게 보일 때는 규칙 관리자를 확인하세요

규칙을 여러 개 만들다 보면 같은 셀에 서로 다른 색을 지정할 수 있습니다. 홈, 조건부 서식, 규칙 관리에서 적용 범위와 규칙 순서를 확인하고 필요 없는 규칙을 삭제하면 됩니다. 드롭다운에 현재 선택 영역의 규칙만 보인다면 표시 대상을 현재 워크시트로 바꿔 보세요. 이미 설정한 규칙의 기준값이나 색을 바꿀 때도 이 화면에서 편집할 수 있습니다.

표를 복사하거나 행을 추가한 뒤 일부 셀에만 색이 들어간다면 ‘적용 대상’ 범위가 새 데이터까지 포함하는지 확인해야 합니다. 날짜가 들어 있어 보이는데도 수식 규칙이 반응하지 않는다면 날짜가 문자로 입력됐을 가능성도 있습니다. 날짜 셀의 표시 형식만 바꾸는 것으로 값 자체가 날짜로 바뀌지는 않습니다.

저는 정비표를 만들 때 기한 초과에는 연한 빨강, 비용 비교에는 데이터 막대처럼 역할을 나눠 씁니다. 색을 많이 쓰는 것보다 각 색이 무엇을 뜻하는지 분명하게 정해 두는 편이 실제 점검 업무에서 더 빠르게 읽힙니다.