업무표의 상태를 사람이 직접 입력하면 ‘완료’, ‘처리 완료’, ‘끝’처럼 같은 뜻의 값이 서로 다른 글자로 쌓일 수 있습니다. 그러면 필터 결과와 건수 집계가 달라집니다.
상태처럼 정해진 값은 데이터 유효성 검사의 목록으로 선택하게 하세요. 원본 목록을 어디에 두느냐에 따라 항목을 추가하는 방법이 달라지므로, 설정 전에 입력 범위와 목록 원본을 따로 정하면 편합니다.
아래 경로는 Windows 데스크톱 Excel의 한국어 UI를 기준으로 합니다. 메뉴 위치는 버전이나 창 크기에 따라 조금 달라질 수 있습니다.
주문표에서 드롭다운을 적용할 셀 찾기
주문 상태를 네 가지로 통일하는 예제를 보겠습니다. 주문표의 상태 열은 D열이고, 드롭다운을 적용할 데이터는 D4:D11입니다. 목록 원본은 같은 시트의 G2:G5에 두고, 접수·진행 중·완료·보류를 각각 한 셀에 입력합니다.
| 주문 번호 | 상태 (D열) | 담당자 |
|---|---|---|
| 1001 | 접수 | 민지 |
| 1002 | 진행 중 | 준호 |
| 1003 | 완료 | 유진 |
| 1004 | 보류 | 민지 |
| 1005 | 완료 | 준호 |
| 1006 | 진행 중 | 유진 |
| 1007 | 완료 | 민지 |
| 1008 | 접수 | 준호 |
이 예제의 G2:G5는 고정 범위입니다. 따라서 G6에 새 항목을 적어도 목록에는 자동으로 나타나지 않습니다. 자동 갱신이 필요하면 아래 원본 비교에서 Excel 표를 선택하세요.
목록 원본은 어디에 두는 것이 좋을까
목록이 짧고 거의 바뀌지 않으면 유효성 검사 창에 직접 입력할 수 있습니다. 자주 수정할 목록은 셀 범위나 Excel 표로 관리하면 원본을 찾고 고치기 쉽습니다.
목록 원본 방식별 관리 차이
| 원본 | 적합한 경우 | 새 항목을 넣으려면 |
|---|---|---|
| 직접 입력 | 항목이 적고 거의 고정됨 | 유효성 검사 원본을 다시 편집 |
| 셀 범위 | 항목이 길거나 시트에서 관리 | 새 값이 범위 밖이면 원본 주소도 늘림 |
| Excel 표 | 항목을 자주 추가하거나 삭제 | 표에 항목을 추가·삭제한 뒤 목록 확인 |
Microsoft는 원본 목록을 Excel 표로 만들면 항목을 더하거나 제거했을 때 연결된 드롭다운도 자동으로 갱신된다고 안내합니다. 설정한 뒤 실제 항목 변경으로 반영 여부를 확인하세요.
입력 셀 범위와 목록 원본 범위는 다르다
드롭다운을 설정할 때 두 범위를 혼동하지 마세요.
- 입력 범위는 사용자가 항목을 고를 셀입니다. 예제의 주문 상태 범위는
D4:D11입니다. - 목록 원본 범위는 드롭다운에 보여 줄 값이 들어 있는 셀입니다. 예제는
G2:G5이며 제목 셀G1은 제외합니다.
다른 시트에서 목록을 관리한다면 이름 정의를 만들어 원본으로 연결할 수 있습니다. 목록이 고정된 경우는 이름이 지정한 셀 범위를 사용하고, 항목이 늘어날 때 이름 정의의 참조 범위도 함께 점검하세요.
원본을 보존하고 Excel에서 설정하기
- 복사본을 만듭니다. 데이터 유효성 검사를 설정하기 전에 다른 이름으로 저장하거나 시트를 복제해 원본을 보존합니다.
- 목록 항목을 준비합니다. 예제라면 접수, 진행 중, 완료, 보류를 각각 한 셀에 입력합니다. 목록을 직접 입력할 때는 원본에
접수,진행 중,완료,보류처럼 쉼표로 나눠 씁니다. - 입력할 셀을 선택합니다. 한 셀에서 먼저 시험하거나 적용할 전체 범위를 선택합니다. 예제는
D4:D11입니다. - 데이터 유효성 검사를 엽니다. 데이터 탭에서 데이터 유효성 검사를 선택하고 허용 항목에서 목록을 고릅니다.
- 원본을 연결합니다. 원본 칸에 직접 목록을 입력하거나 셀 범위를 선택합니다. 예제 범위를 입력할 때는
=$G$2:$G$5처럼 지정합니다. 목록 머리글은 선택하지 않습니다. - 화살표와 빈 셀 설정을 확인합니다. 셀에 드롭다운 표시를 켭니다. 빈 상태를 허용할지 업무 규칙에 맞춰 빈 셀 무시 여부를 정합니다.
- 오류 경고를 정하고 저장합니다. 목록 밖 값을 직접 입력하지 못하게 하려면 오류 경고 스타일을 중지로 설정합니다. 경고와 정보는 알림을 표시한 뒤 입력을 허용할 수 있습니다. 확인을 눌러 저장합니다.
데이터 유효성 검사 메뉴가 비활성화되어 있으면 시트가 보호되어 있거나 통합 문서가 공유 중일 수 있습니다. 회사 문서나 다른 사람이 관리하는 파일은 소유자와 먼저 확인하세요.
설정을 저장하면 D4의 화살표를 열어 네 상태가 모두 보이는지 확인하고 ‘완료’를 선택해 값이 입력되는지 살펴봅니다. 오류 경고를 ‘중지’로 설정했다면 빈 테스트 셀에 ‘완료됨’을 직접 입력해 차단되는지 확인한 뒤 시험 값을 지우세요. 새 항목을 고정 범위 원본 밖의 G6에 추가하면 원본도 =$G$2:$G$6으로 늘립니다. Excel 표를 원본으로 썼다면 표에 시험 항목을 넣어 목록에 반영되는지 확인하고 시험 값을 삭제합니다.
예제 주문 8건 중 ‘완료’는 세 건이므로 상태 범위 D4:D11에 =COUNTIF(D4:D11,"완료")를 적용하면 결과는 3이어야 합니다. 지역 설정에 따라 쉼표 대신 세미콜론을 써야 할 수 있습니다. 데이터 유효성 검사는 입력 보조 규칙이지 보안 장치는 아니므로, 붙여넣기로 들어온 값은 별도로 점검하세요.
목록이나 화살표가 예상과 다르면
- 화살표가 보이지 않으면: 대상 셀을 선택했는지, 허용 항목이 목록인지, 셀에 드롭다운 표시가 켜져 있는지 확인합니다.
- 항목이 빠졌으면: 원본 주소가 전체 값을 포함하는지 확인합니다. 고정 범위 밖에 추가한 항목은 원본 범위도 늘립니다.
- 제목이 선택지로 나오면: 원본에서 머리글 셀을 제외합니다. 예제에서는
G1을 빼고G2:G5를 지정합니다. - 목록 밖 값이 입력되면: 오류 경고의 스타일이 중지인지 확인합니다. 붙여넣기로 입력된 값은 별도로 검토합니다.
- 데이터 유효성 검사가 비활성화되면: 시트 보호나 공유 설정을 확인하고 파일 관리자에게 문의합니다.
공식 안내와 관련 글
Microsoft의 드롭다운 목록 만들기 문서는 원본 목록, 표, 드롭다운 표시, 입력 메시지와 오류 경고 설정을 설명합니다. 원본 방식별 항목 추가·삭제는 드롭다운 항목 추가 또는 제거를 참고하세요. 입력 규칙의 검증 방법은 데이터 유효성 검사 적용에 안내되어 있습니다.
중복 행을 지우기 전에 비교 기준을 확인하는 방법은 엑셀 중복 제거, 무엇을 체크해야 할까?에서 볼 수 있습니다.