엑셀 · 데이터 입력

엑셀 드롭다운 목록 만들기 5단계, 종속 드롭다운까지

엑셀 드롭다운 목록, 정식 명칭으로는 데이터 유효성 검사(Data Validation)는 셀을 클릭하면 화살표가 뜨고 미리 정해둔 값 중에서만 고르게 만드는 기능입니다. 부서명 하나 통일시키겠다고 서식을 만들어 돌렸는데, 막상 취합해보면 “경영지원팀”, “경영지원 팀”, “경영지원1팀”처럼 제각각 적혀 있는 경우 다들 한 번쯤 겪어봤을 겁니다. 담당자한테 일일이 다시 물어보고 오타를 하나씩 고치다 보면 반나절이 훌쩍 지나갑니다. 드롭다운 목록을 걸어두면 이런 표기 차이가 애초에 생기지 않습니다.

기능 자체는 단순해 보이지만, 다른 시트 참조나 표 자동 확장, 종속 드롭다운처럼 한 단계 더 들어가는 순간 헷갈리는 지점이 꽤 많습니다. 기본 절차부터 실무에서 자주 쓰는 응용, 그리고 자주 발생하는 오류까지 순서대로 짚어보겠습니다.

드롭다운 목록 만들기 기본 5단계

드롭다운을 넣는 방법 자체는 단순합니다. Microsoft 공식 지원 문서에 나온 절차를 그대로 따라가면 됩니다.

1. 목록을 넣고 싶은 셀(또는 범위)을 선택합니다.
2. [데이터] 탭 → [데이터 도구] 그룹에서 [데이터 유효성 검사]를 클릭합니다.
3. [설정] 탭에서 [허용]을 “목록”으로 바꿉니다.
4. [원본] 상자에 값을 입력하거나 셀 범위를 지정합니다.
5. [확인]을 누르면 해당 셀에 드롭다운 화살표가 생깁니다.

드롭다운 원본 지정 방법 — 직접 입력 vs 범위 참조

[원본] 상자를 채우는 방법은 크게 두 가지입니다. 하나는 “과일,채소,곡물,유제품”처럼 쉼표로 구분해 값을 직접 입력하는 방식이고, 다른 하나는 이미 시트 어딘가에 적어둔 셀 범위를 드래그해서 참조하는 방식입니다. 항목이 몇 개 안 되고 앞으로 바뀔 일이 거의 없다면 직접 입력이 빠릅니다. 항목이 많거나 나중에 추가·삭제가 잦을 예정이라면 범위를 참조하는 쪽이 관리하기 편하고요.

범위를 참조할 때는 항목이 시작되는 행부터만 잡아야 합니다. 헤더(제목) 행까지 같이 잡으면 드롭다운 맨 위에 “부서명” 같은 제목 글자가 선택지로 끼어드는 불상사가 생깁니다. 이거 의외로 자주 걸리는 실수입니다.

드롭다운 목록 자동 확장 — 표(Ctrl+T) 활용

원본으로 쓸 목록이 자주 바뀐다면, 그 목록 자체를 Excel 표(Ctrl+T)로 만들어두는 편이 낫습니다. 표로 지정한 범위를 드롭다운 원본으로 삼으면, 표 끝에 새 항목을 추가하거나 지울 때 그 표를 참조하는 모든 드롭다운이 자동으로 업데이트됩니다. 일반 셀 범위를 원본으로 쓰면 이 부분이 그대로 안 됩니다. 새 항목을 목록 아래에 적어 넣어도 원본 범위 자체를 수동으로 늘려주지 않는 이상 드롭다운에는 반영되지 않거든요.

참고로 SharePoint 사이트에 연결된 표에는 데이터 유효성 검사 자체를 추가할 수 없다는 제약도 있습니다(추정 — 워크팁 독자 대부분에게 필수 정보는 아니지만, 공유 문서함을 SharePoint와 동기화해 쓰는 팀이라면 표를 만들기 전에 한 번 확인해두는 편이 안전합니다).

다른 시트 참조로 드롭다운 만들기와 이름 정의

목록을 다른 시트에 만들어두고 참조하는 경우도 많습니다. [원본] 상자에서 다른 시트로 이동해 범위를 드래그하면 Excel이 알아서 “시트이름!범위” 형태로 채워 넣어줍니다. 문제는 이 상태로 놔두면 나중에 범위가 어디 있는지 찾기가 번거롭다는 점입니다. Microsoft 공식 문서도 다른 시트를 참조할 목록은 이름을 정의(Define Name)해서 관리하라고 권합니다. 이름을 정의해두면 원본 상자에는 셀 주소 대신 이름 하나만 적으면 되고, 목록이 있는 시트를 통째로 숨겨버려도 드롭다운은 멀쩡히 작동합니다.

이름을 나중에 수정하고 싶으면 [수식] 탭 → [이름 관리자]에서 대상 이름을 선택하고 [참조 위치] 상자에서 범위를 다시 지정한 뒤 저장하면 끝입니다.

데이터 유효성 검사 오류 메시지 스타일 3가지 설정하기

목록에 없는 값을 억지로 입력했을 때 뜨는 경고창도 취향껏 바꿀 수 있습니다. [데이터 유효성 검사] 대화상자의 [오류 메시지] 탭에서 스타일을 고르면 되는데, 스타일마다 사용자가 그냥 넘어갈 수 있는지 여부가 다릅니다.

제목과 메시지 문구는 직접 입력해서 바꿀 수 있어서, “부서명을 목록에서 선택해 주세요”처럼 담당자가 알아듣기 쉬운 말로 바꿔두면 훨씬 친절합니다.

종속 드롭다운 만들기(INDIRECT 함수 활용)

1차 드롭다운에서 “과일”을 고르면 2차 드롭다운에는 사과·배만, “채소”를 고르면 당근·양파만 뜨게 만드는 방식을 종속 드롭다운이라고 부릅니다. 여기서부터는 Microsoft 공식 지원 문서에 별도로 정리된 페이지가 없고, INDIRECT 함수와 이름 정의를 조합하는 방법이 커뮤니티 답변을 통해 널리 알려져 있습니다(추정 — 공식 절차로 단정하기보다는 널리 쓰이는 방법 정도로 받아들이는 편이 맞습니다).

1. 1차 목록의 각 항목(예: 과일, 채소)마다 하위 목록 범위를 따로 만듭니다.
2. 그 범위마다 항목 이름과 똑같은 이름으로 이름 정의를 해둡니다(예: “과일”이라는 이름의 범위에는 사과·배).
3. 1차 드롭다운의 원본에는 “과일,채소”처럼 상위 항목 목록을 넣습니다.
4. 2차 드롭다운의 원본에는 =INDIRECT(1차 셀 주소)를 입력합니다.
5. 1차에서 고른 값과 이름 정의한 이름의 철자가 정확히 같은지 다시 확인합니다.

여기서 걸리는 사람이 은근히 많습니다. 이름 정의는 공백을 쓸 수 없어서, “과일 종류”처럼 띄어쓰기가 들어간 이름을 그대로 쓰면 INDIRECT가 참조를 못 찾고 오류를 냅니다(추정 — 이 부분도 공식 문서가 아니라 커뮤니티 답변에서 확인한 내용입니다). 이럴 땐 SUBSTITUTE 함수로 선택값의 공백을 밑줄(_)로 바꿔서 =INDIRECT(SUBSTITUTE(셀주소," ","_")) 형태로 고쳐주는 보정이 필요합니다.

UNIQUE·FILTER로 동적 드롭다운 목록 만들기

목록 자체를 수식으로 뽑아내고 싶다면 UNIQUE나 FILTER 같은 동적 배열 함수를 활용하는 방법도 있습니다. 원본 데이터에서 중복 없는 값만 뽑아 만든 스필(spill) 범위를, 원본 상자에서 그대로 참조하는 방식입니다.

다만 두 가지는 미리 알아두는 게 좋습니다(추정 — Microsoft 공식 문서는 스필 범위 연산자(#) 자체는 설명하지만, 이걸 데이터 유효성 검사 원본으로 쓸 수 있다는 내용은 제3자 자료에서만 확인했고 공식 문서로는 직접 검증하지 못했습니다. 적용 전에 본인 파일에서 한 번 테스트해보길 권합니다). 그리고 이 방법은 Excel for Microsoft 365, Excel 2021, Excel 2019에서만 지원되는 함수라 그 이전 버전(2016 이하)을 쓰는 회사 PC에서는 애초에 통하지 않는 방법입니다.

드롭다운 흔한 오류 5가지와 해결법

지금까지 나온 내용을 실제로 적용하다 보면 반복적으로 걸리는 문제들이 있습니다. 아래 표로 모아봤습니다.

유효성 검사 위반 값(잘못된 데이터) 찾아내기

이미 들어간 값이 맞는지 사후에 확인하려면 [데이터] 탭 → [데이터 유효성 검사] 옆 화살표를 눌러 [잘못된 데이터 표시]를 클릭하면 됩니다. 유효성 검사 조건에 안 맞는 셀마다 빨간 원이 표시됩니다.