엑셀 데이터 유효성 검사를 제대로 활용하면 여러 사람이 함께 입력하는 표에서 생기는 실수를 크게 줄일 수 있습니다. 누군가는 “서울”이라고 쓰고 누군가는 “서울시”라고 써서 나중에 집계할 때 애를 먹은 경험, 다들 한 번쯤 있으실 겁니다.
목차
엑셀 데이터 유효성 검사의 기본, 드롭다운 목록 만들기
가장 많이 쓰는 형태입니다. 정해진 값 중에서만 고를 수 있게 하면 오타나 표현 차이가 원천적으로 사라지고, 나중에 데이터를 집계하거나 필터링할 때도 훨씬 수월해집니다.
목록을 적용할 범위를 선택하고, 데이터 탭의 데이터 유효성 검사를 누르세요. 제한 대상을 “목록”으로 선택하고, 원본 칸에 쉼표로 구분해서 값을 입력하면 됩니다.
서울,경기,인천,부산이렇게 입력하면 해당 셀을 클릭했을 때 화살표가 나타나고, 목록에서 고르기만 하면 됩니다. 직접 타이핑할 필요가 없으니 속도도 빨라집니다.
다른 시트의 목록 참조하기
목록 항목이 많거나 자주 바뀐다면, 값을 직접 입력하는 대신 다른 시트에 목록을 만들어 두고 그 범위를 참조하는 게 관리하기 편합니다.
원본 칸에 값 대신 범위를 지정하면 됩니다. 예를 들어 별도 시트의 A2:A20에 부서 목록을 적어뒀다면, 원본 칸에 =부서목록!$A$2:$A$20 형태로 입력하면 그 범위의 값이 그대로 드롭다운에 반영됩니다. 나중에 부서가 추가되거나 이름이 바뀌어도, 참조한 시트만 수정하면 모든 드롭다운에 자동으로 반영되니 관리가 한결 편해집니다.
숫자 범위 제한하기
나이나 점수처럼 특정 범위를 벗어나면 안 되는 숫자도 제한할 수 있습니다. 제한 대상을 “정수” 또는 “소수점”으로 바꾸고, 최소값과 최대값을 지정하면 됩니다.
범위를 벗어난 값을 입력하면 바로 오류 메시지가 뜨기 때문에, 나중에 잘못된 데이터를 걸러내는 수고를 덜 수 있습니다.
날짜 입력 범위 제한하기
계약일이나 마감일처럼 특정 기간 안의 날짜만 입력받고 싶을 때도 엑셀 데이터 유효성 검사를 활용할 수 있습니다. 제한 대상을 “날짜”로 바꾸고, 시작일과 종료일을 지정하면 그 범위를 벗어난 날짜는 입력이 막힙니다.
예를 들어 이번 달 안에만 접수를 받는 양식이라면, 최소값에 이번 달 1일을, 최대값에 말일을 넣어두면 됩니다. 오래된 날짜나 미래 날짜가 실수로 들어가는 일을 막을 수 있습니다.
여러 조건을 함께 적용하고 싶다면
목록이나 범위 하나만으로는 부족하고, 조건을 조합하고 싶을 때는 제한 대상을 “사용자 지정”으로 선택하면 됩니다. 수식을 직접 입력해서 원하는 조건을 자유롭게 만들 수 있습니다.
예를 들어 특정 칸이 비어 있지 않을 때만 입력을 허용하거나, 다른 셀의 값과 비교해서 더 큰 값만 받도록 하는 것도 가능합니다. 처음에는 수식이 낯설 수 있지만, 자주 쓰는 몇 가지 패턴만 익혀두면 충분합니다. 더 자세한 옵션은 마이크로소프트 공식 지원 문서에서도 확인할 수 있습니다.
안내 메시지와 오류 메시지 꾸미기
데이터 유효성 검사 창의 “설명 메시지” 탭에서, 셀을 클릭했을 때 나타날 안내 문구를 설정할 수 있습니다. “여기에는 부서명만 입력하세요”처럼 안내해 두면 다른 사람이 입력할 때 헷갈리지 않습니다.
“오류 메시지” 탭에서는 잘못된 값을 입력했을 때 뜨는 메시지를 직접 쓸 수 있습니다. 기본 메시지 대신 “정해진 목록에서만 선택하세요” 같은 친절한 문구로 바꿔두면 좋습니다.
이미 쌓인 데이터의 중복도 함께 점검하세요
유효성 검사로 앞으로의 입력은 막을 수 있지만, 이미 입력된 데이터에 중복이나 오타가 있는지도 별도로 확인해야 합니다. 엑셀 중복값 찾기와 제거하는 세 가지 방법에서 기존 데이터를 정리하는 방법을 다뤘습니다. 양식을 처음부터 다시 만드신다면 엑셀 셀 병합을 피해야 하는 이유도 함께 참고해 두시면 나중에 데이터를 다루기 훨씬 수월해집니다.
자주 묻는 질문
이미 입력된 잘못된 데이터도 찾아낼 수 있나요
데이터 탭의 데이터 유효성 검사 옆 화살표를 누르면 “잘못된 데이터” 항목이 있습니다. 이걸 누르면 규칙을 어긴 셀들이 빨간 원으로 표시되어 한눈에 찾을 수 있습니다.
드롭다운 화살표가 안 보여요
셀 너비가 너무 좁으면 화살표가 잘려서 안 보일 수 있습니다. 열 너비를 조금 넓혀보세요. 그래도 안 보인다면 설정 과정에서 “셀 안에 드롭다운 표시” 체크가 빠졌는지 확인해 보세요. 병합된 셀에 유효성 검사를 걸었을 때도 비슷한 문제가 생길 수 있으니 참고하세요.
기존에 입력된 값이 유효성 검사 조건을 벗어나면 어떻게 되나요
유효성 검사는 앞으로 새로 입력하는 값부터 적용되며, 이미 입력되어 있던 값은 조건에 맞지 않아도 자동으로 지워지지 않습니다. 기존 값까지 확인하고 싶다면 앞서 설명한 “잘못된 데이터” 표시 기능으로 함께 점검해 주세요.
엑셀 데이터 유효성 검사는 특히 여러 사람이 함께 쓰는 양식에서 효과가 큽니다. 처음 양식을 만들 때 조금만 신경 써두면, 나중에 데이터를 정리하는 시간을 크게 아낄 수 있습니다. 지금 쓰고 있는 양식이 있다면, 오늘 다룬 방법 중 하나만 골라 바로 적용해 보시길 권합니다.