엑셀 드롭다운인데 목록 밖 값이 들어왔다

붙여넣기는 값뿐 아니라 셀의 규칙도 가져온다

목록 밖 값이 들어왔다면 먼저 그 셀의 데이터 → 데이터 유효성 검사를 연다. 허용 항목이 ‘목록’이고 원본 범위가 맞으면 들어온 값을 검사한다. 규칙이 없어졌거나 다른 목록으로 바뀌었다면 원래 규칙부터 복구한다.

이 현상은 새 버전만의 오류가 아니다. 2013년 Microsoft 커뮤니티 질문에도 직접 입력은 차단되지만 다른 셀에서 복사한 값은 들어온다는 사례가 있다. Microsoft의 잘못된 데이터 원 표시 안내는 첫 문단에서 직접 입력할 때의 오류 차단과 달리, 복사·채우기로 값을 넣으면 경고 없이 잘못된 데이터가 들어올 수 있다고 설명한다. 입력 중 보이는 경고를 데이터 전체의 검사 결과로 읽으면 안 되는 이유다.

예를 들어 팀의 업무 상태를 ‘대기·진행·완료’로 모으고 있다고 가정하자. 다른 팀 표의 ‘처리 끝’을 직접 타이핑할 때는 막혔는데, 그 표의 행을 복사한 뒤에는 들어올 수 있다. 같은 열에 두 표현이 섞이면 ‘완료’만 세는 보고서에서는 ‘처리 끝’ 행이 빠진다. 담당자는 업무를 마쳤다고 생각하지만 보고서에는 그 상태가 반영되지 않는 식이다.

문제 셀에서 본 상태	먼저 할 일	그 뒤 남는 일
원래 목록 규칙이 있고 값만 목록 밖이다	현재 값을 허용 목록과 대조한다	의미를 확인해 올바른 상태로 고친다
목록 규칙이 없거나 원본이 달라졌다	정상 규칙을 다시 적용한다	규칙 복구 전 들어온 값도 검사한다
정당한 새 상태가 목록에 없다	목록 원본에 추가할 항목인지 결정한다	입력 목록과 검사 범위를 함께 갱신한다

아래 메뉴는 Windows용 Excel Microsoft 365·2024 데스크톱 앱을 기준으로 한다. 웹 Excel도 드롭다운을 만들 수 있지만, 뒤에서 쓰는 잘못된 데이터 원 표시 기능은 데스크톱에서 확인하는 절차다. 공동 작업자가 브라우저로 입력하더라도 담당자가 검사할 앱은 따로 정할 수 있다.

‘대기·진행·완료’를 고르게 하고 직접 입력 오류를 막는다

연습용 사본에서 H1에는 ‘허용 상태’, H2부터 H4까지는 각각 ‘대기’, ‘진행’, ‘완료’를 적는다. D1은 ‘상태’로 두고 D2:D100을 입력 영역으로 삼는다. 항목을 한곳에 모으면 ‘진행’과 ‘진행 중’을 서로 다른 사람이 각자 목록에 넣는 일을 줄일 수 있다. 허용 목록을 쓰는 H열과 실제 업무 상태를 적는 D열은 역할이 다르다.

D2:D100을 선택하고 데이터 → 데이터 유효성 검사를 연다.
설정 탭에서 허용을 ‘목록’으로 고르고 원본에 =$H$2:$H$4를 넣는다.
‘드롭다운 표시’를 켠다. H1의 머리글은 원본 범위에 넣지 않는다.
오류 메시지 탭에서 유효하지 않은 데이터에 오류 메시지를 표시하도록 켜고 스타일을 ‘중지’로 고른다.
저장한 뒤 D2에서 ‘진행’을 선택하고, 다른 연습 셀에는 목록에 없는 ‘처리 끝’을 직접 입력해 본다.

이 설정은 Microsoft의 드롭다운 생성 절차에 따른 것이다. ‘경고’나 ‘정보’는 사용자가 메시지를 확인한 뒤 목록 밖 값을 받아들이는 선택을 할 수 있다. 직접 입력부터 제한하려면 ‘중지’를 고른다. 입력이 허용되는 것과 선택 화살표가 보이는 것은 서로 다른 설정이므로 두 곳을 모두 맞춘다.

입력 메시지에는 ‘상태는 목록에서 선택하고 사유는 비고 열에 적는다’처럼 이 표에서 할 일을 쓴다. 오류 메시지에는 선택 가능한 항목을 다시 알려 줄 수 있다. 업무 상태 열에 ‘진행 — 고객 답변 기다림’을 넣고 싶다면 상태는 ‘진행’, 상세 설명은 비고에 두는 식으로 나눈다. 집계할 코드와 설명 문장이 한 셀에 섞이지 않게 만드는 예다.

빈칸을 허용할지도 정한다. 입력 중인 행은 비어 있어도 되지만 제출한 행에는 상태가 필요할 수 있다. ‘공백 무시’ 설정 하나만으로 제출 완료 여부까지 판단하지 말고, 뒤의 검사 열에서 미입력을 별도로 표시한다. 아직 시작하지 않은 업무의 ‘대기’와 아무것도 입력하지 않은 셀도 구별된다.

정상 셀의 규칙만 복사하면 기존 입력값을 남길 수 있다

Excel 붙여넣기 옵션에 따르면 기본 Ctrl+V는 데이터뿐 아니라 서식·수식·유효성 검사 등도 가져온다. 다른 표를 통째로 붙인 뒤 원래 드롭다운이 달라졌다면 이 전달 범위를 먼저 본다. 데이터만 옮기려던 작업이 입력 양식의 규칙까지 바꾼 셈이다.

복구할 때는 원본이 정확한 정상 셀 하나를 고른다. 예를 들어 D2가 =$H$2:$H$4를 참조하고 있고 D20:D30의 규칙이 사라졌다면, D2를 복사한 뒤 D20:D30을 선택한다. 홈 → 붙여넣기 → 선택하여 붙여넣기에서 유효성 검사를 선택한다. 이 옵션은 규칙을 전달하므로 D2에 쓰인 상태까지 모든 행에 복제하려는 작업과 다르다.

복구 뒤 D20과 D30의 원본 범위를 열어 보고, 기존 상태 값도 남아 있는지 확인한다. 예제의 달러 기호는 허용 목록의 위치를 고정한다. 원래 파일이 행마다 다른 목록을 참조하는 복잡한 양식이라면 이 한 셀의 규칙을 전체 열에 덮지 않는다. 부서마다 다른 선택지를 쓰는 셀은 같은 규칙으로 묶을 대상이 아니기 때문이다.

앞으로 데이터만 옮길 때는 값 붙여넣기를 사용해 원본 셀의 규칙과 서식을 함께 들여오는 일을 피할 수 있다. 다만 Microsoft 개발 문서는 같은 통합문서의 값을 Values로 붙여넣는 경우 검사가 작동한다고 별도로 설명한다. 이를 웹·외부 앱·일반 붙여넣기의 모든 경로에 확대하지 않는다. 사용 중인 경로로 연습하고 실제 들어온 값은 다음 절에서 확인한다.

규칙을 복구해도 이미 들어온 오자는 남아 있다

기존 데이터 위에 규칙을 다시 적용했다고 ‘처리 끝’이 자동으로 ‘완료’로 바뀌지는 않는다. 데스크톱 Excel에서 데이터 → 데이터 유효성 검사 옆 화살표 → 잘못된 데이터를 선택하면 규칙에 맞지 않는 셀 주위에 빨간 원이 생긴다. 영문 메뉴는 Circle Invalid Data다. 공식 원 표시 안내는 복사·수식 등으로 들어온 값도 현행 규칙에 따라 검사한다고 설명한다.

이 검사는 셀에 규칙이 있어야 의미가 있다. D20의 규칙이 통째로 사라진 상태라면 원이 없다는 사실만으로 정상이라고 판단할 수 없다. 먼저 규칙을 복구한 뒤 다시 실행한다. ‘유효성 표시 지우기’는 원을 숨기는 동작이고 입력값을 고치는 기능이 아니다. 반면 값 자체를 유효한 상태로 고치면 해당 원은 사라진다.

많은 행을 받아야 한다면 드롭다운 규칙과 독립된 검사 열도 둘 수 있다. E1을 ‘상태 검사’로 쓰고 E2에 다음 수식을 넣은 뒤 E100까지 복사한다. H2:H4에는 앞의 세 상태가 들어 있고, D열에는 오류 값이 아닌 텍스트나 빈칸이 들어 있다는 예제다.

=IF(D2="","미입력",IF(SUMPRODUCT(--(D2=$H$2:$H$4))>0,"정상","목록 밖"))

SUMPRODUCT 부분은 D2가 허용 목록의 각 항목과 같은지 비교하고, --로 참·거짓을 1·0으로 바꿔 더한다. 하나라도 같으면 합계가 0보다 크다. IF는 빈칸을 먼저 가려낸 뒤 이 비교 결과에 따라 ‘정상’ 또는 ‘목록 밖’을 표시한다. 검사 수식은 D셀의 드롭다운 설정을 읽지 않고 H열의 목록과 값을 직접 비교한다.

D열의 예제 값	E열의 결과	처리 예
진행	정상	현재 목록과 일치한다
처리 끝	목록 밖	담당자에게 실제 완료 여부를 확인한다
진행 뒤에 공백이 붙은 값	목록 밖	원문을 보고 불필요한 공백을 고친다
빈 셀	미입력	제출 대상 행이면 상태를 받는다

이 예제의 ‘정상’은 글자가 목록에 있다는 뜻이다. 실제 업무가 완료됐는지까지 증명하지 않는다. 원본의 ‘처리 끝’이 단순히 검토 종료를 뜻한다면 무조건 ‘완료’로 바꾸면 안 된다. 상태를 확인한 뒤 고치고, 원래 받은 자료는 별도 시트에 남겨 두면 어떤 값에서 바꿨는지 다시 볼 수 있다.

검사 열 자체를 데이터와 함께 덮으면 결과도 사라진다. D열만 값으로 옮기는 범위를 정하고 E열의 수식을 보존한다. 값이 #N/A 같은 Excel 오류라면 이 예제 수식도 오류를 낼 수 있으므로 원본 오류부터 처리한다.

‘보류’를 추가했다면 목록과 검사 범위를 함께 늘린다

H5에 ‘보류’를 썼는데 드롭다운에 나타나지 않는다면 기존 원본이 여전히 H2:H4인지 본다. 목록 수정 안내는 셀 범위 기반 목록에서 원본 범위를 다시 선택하도록 설명한다. 예제에서는 H2:H5로 바꾸고 E열 검사 수식의 범위도 같은 H2:H5로 넓힌다. 목록만 늘리면 새 항목을 선택하고도 검사 열에는 ‘목록 밖’이 나올 수 있다.

항목이 자주 바뀐다면 허용 목록을 Excel 표로 만들고 그 표의 데이터 셀을 드롭다운 원본으로 선택할 수 있다. Microsoft는 표 기반 원본에 항목을 추가·삭제하면 연결된 드롭다운이 자동 갱신된다고 설명한다. 여기서 늘어나는 것은 선택 항목이다. 업무 입력 영역 D2:D100을 넘어 새 행을 붙이는 일까지 자동으로 해결한다는 뜻은 아니다.

목록에서 상태를 삭제할 때도 기존 행을 본다. ‘진행’을 없애고 ‘검토 중’으로 이름을 바꾸었다면 예전에 입력한 ‘진행’은 새 목록과 달라진다. 목록 원본의 이름을 바꿔도 과거 D열에 입력한 텍스트까지 함께 바뀌지는 않는다. 팀에서 어떤 의미로 이름을 바꿨는지 정한 다음 기존 행을 검사해 필요한 항목만 수정한다.

입력표를 배포하기 전에 타이핑과 붙여넣기를 각각 해 본다

외부에서 받은 표는 원본 수집 시트에 남겨 두고, 가져온 상태 값을 허용 목록과 비교한다. 검사 결과가 ‘미입력’인 행은 담당자에게 상태를 받고, ‘목록 밖’인 행은 원래 표현이 무엇을 뜻하는지 확인해 고친다. 이렇게 확정한 값만 최종 입력표로 옮겨 집계한다. 나중에 완료 건수가 맞지 않으면 처음 받은 표현과 최종 상태를 나란히 볼 수 있다.

허용 목록과 검사 수식의 실수 수정을 줄이려면 시트 보호를 사용할 수 있다. 입력할 D2:D100만 셀 서식 → 보호에서 잠금을 해제하고 검토 → 시트 보호를 적용한다. H열과 E열은 잠긴 상태로 둔다. 보호 뒤 팀원이 D열을 편집할 수 있는지 확인한다. D열은 입력할 수 있도록 열어 둔 곳이므로, 제출된 상태 값은 E열의 검사 결과를 보고 정리한다.

사본에서 정상 상태와 목록 밖 상태를 직접 입력해 오류 메시지를 확인한다.
평소 쓰는 다른 통합문서나 외부 자료에서 값을 붙여넣는다. 일반 붙여넣기와 값 붙여넣기를 각각 해 본다.
붙인 셀의 원본 범위·오류 스타일을 열고, 규칙이 달라졌다면 복구한다. 보호 때문에 메뉴가 잠겼다면 담당자가 보호를 해제해 검사·수정하고 다시 보호한다.
원 표시와 검사 열로 남은 값을 찾는다. 마지막 입력 행까지 규칙과 검사 수식이 있는지 본다.
새 상태를 추가하는 담당자와 목록·검사 범위를 함께 바꾸는 위치를 인계한다.

사본의 연습값을 지운 뒤에는 정상 규칙이 있는 셀과 허용 목록을 남긴다. 다음번에 규칙이 덮였을 때 복사할 기준이 된다. 원본 수집 시트에 남긴 값과 최종 상태를 나란히 볼 수 있으면, 보고서에서 빠진 한 행이 미입력인지 다른 표현으로 들어온 상태인지까지 되짚을 수 있다.