엑셀 드롭다운 만들기
항목을 적고 파일을 받으면 드롭다운이 이미 들어 있어요. 내 파일에 넣는 방법은 아래 단계를 따라 하세요.
드롭다운 만들기, 목록 범위와 연결 드롭다운의 원리
드롭다운은 언제 만드나요
여러 사람이 함께 채우는 업무 목록에서 상태 칸에 ‘완료’, ‘완 료’, ‘끝’이 뒤섞이면 필터와 COUNTIF가 제대로 셀 수 없어요. 드롭다운으로 고를 수 있는 값을 정해 두면 입력이 빨라지고, 철자가 다른 값이 처음부터 생기지 않아요. 분류를 고르면 그 안의 세부 항목만 나오는 연결 드롭다운은 품목 장부나 신청서처럼 단계가 있는 목록에서 특히 쓸모가 많아요. 이 도구는 항목만 적으면 드롭다운이 이미 들어간 엑셀 파일을 만들어 줘요.
사용 방법
1단계기본 드롭다운 만들기
- 1① 기본 드롭다운과 연결 드롭다운 가운데 골라요.
- 2② 항목을 쉼표나 줄바꿈으로 나눠 적어요. 앞뒤 공백은 지워지고 빈 항목은 빠져요.
- 3③ 켜 두면 목록에 없는 값을 입력했을 때 오류 메시지가 떠서 막아요. 끄면 목록은 보여 주되 다른 값도 받아요.
2단계연결 드롭다운 만들기
- 4④ 한 줄에 ‘대분류: 소분류, 소분류’ 모양으로 적어요.
- 5⑤ 아래 안내에 내 파일에서 같은 구조를 만드는 순서가 나와요.
- 6⑥ 드롭다운이 들어간 파일을 받아요. 입력 시트 200행까지 드롭다운이 걸려 있어요.
받은 파일의 구조
기본 드롭다운 파일은 ‘입력’ 시트 C열(2~200행)에 드롭다운이 걸려 있고, 항목은 같은 시트 H열에 적혀 있어요. 드롭다운은 H열 범위를 가리키고 있어서 H열 항목을 고치면 목록도 바뀌어요. 다만 항목을 아래로 더 늘리면 범위 밖이 되니, 그럴 때는 데이터 유효성 검사의 원본 범위를 함께 늘려 주세요.
연결 드롭다운 파일은 ‘목록’ 시트에 대분류마다 세로 목록을 만들고, 각 목록에 대분류 이름을 그대로 이름 정의로 붙여요. 입력 시트의 소분류 칸은 원본이 =INDIRECT($A2)라서 A2에 ‘채소’를 고르면 ‘채소’라는 이름의 범위, 곧 배추·무·양파가 목록이 돼요.
| 대분류 줄에 적은 것 | 만들어지는 이름 | 이유 |
|---|---|---|
| 과일: 사과, 배, 귤 | 과일 | 그대로 쓸 수 있는 이름 |
| 냉동 식품: 만두, 피자 | 냉동_식품 | 이름 정의에는 띄어쓰기를 쓸 수 없음 |
| 2층 창고: 박스, 테이프 | _2층_창고 | 숫자로 시작할 수 없고 띄어쓰기도 바뀜 |
항목을 칸 범위로 두는 이유
드롭다운 원본에 ‘진행,완료,보류’처럼 글자를 직접 적을 수도 있지만, 받은 파일처럼 항목을 칸에 적어 두고 범위를 가리키게 하면 고치기가 훨씬 쉬워요. 항목 이름을 바꿀 때 칸 하나만 고치면 되고, 시트를 보는 사람도 어떤 값을 고를 수 있는지 한눈에 알 수 있어요. 목록이 자주 늘어난다면 항목 칸을 표(Ctrl+T)로 만들거나 UNIQUE 결과를 원본으로 쓰는 방법도 있어요.
주의할 점
- 대분류는 띄어쓰기 없이 — 띄어쓰기가 _로 바뀌면 대분류 칸에도 ‘냉동_식품’이 표시돼요. 처음부터 붙여 쓰는 편이 보기 좋아요.
- 소분류를 먼저 고르면 — 대분류 칸이 비어 있으면 INDIRECT가 가리킬 이름이 없어서 소분류 드롭다운에 항목이 나오지 않아요. 왼쪽 대분류부터 차례로 고르세요.
- 대분류를 바꾼 뒤 — 대분류를 다른 값으로 바꿔도 이미 고른 소분류는 그대로 남아요. 짝이 맞는지 한 번 더 살펴보세요.
더 궁금한 점
항목 구분에 전각 쉼표(，)를 써도 되나요?
돼요. 기본 목록과 연결 목록 모두 일반 쉼표와 전각 쉼표를 같이 알아봐요. 연결 목록의 콜론도 전각(：)을 쓸 수 있어요.
연결 드롭다운의 대분류를 나중에 추가하려면요?
‘목록’ 시트에 새 열을 만들어 소분류를 적고, 그 범위에 대분류 이름으로 이름 정의를 더한 뒤 A열 대분류 목록과 ‘대분류목록’ 이름의 범위도 한 칸 늘려 주세요.
입력 칸에 마우스를 올리면 안내가 나오나요?
기본 드롭다운 파일은 칸을 고르면 ‘목록에서 골라 주세요’ 안내가 떠요. 오류 메시지 문구도 미리 넣어 두어서 따로 설정할 필요가 없어요.
드롭다운은 입력 실수를 막는 가장 쉬운 방법이에요
같은 ‘완료’를 누구는 ‘완료됨’, 누구는 ‘끝’으로 적으면 COUNTIF나 필터가 제대로 동작하지 않아요. 드롭다운(데이터 유효성 검사의 목록)을 만들어 두면 정해진 값만 고르게 되어 집계가 깔끔해져요. 이 도구는 항목을 적으면 드롭다운이 이미 들어 있는 엑셀 파일을 만들어 주고, 내 파일에 직접 넣는 단계도 알려 줘요.
목록 원본을 넣는 두 가지 방법
- 쉼표로 직접 적기 — 원본 칸에 진행,완료,보류처럼 적어요. 항목이 적고 잘 안 바뀔 때 편해요.
- 칸 범위 지정 — 항목을 시트 한쪽(예: H2:H5)에 적고 =$H$2:$H$5를 원본으로 지정해요. 항목을 고칠 때 칸만 바꾸면 돼서 관리하기 좋아요. 받은 예제 파일이 이 방식이에요.
연결(종속) 드롭다운
대분류에서 ‘과일’을 고르면 소분류에 사과·배·귤만 보이게 하는 방식이에요. 소분류 목록마다 대분류와 같은 이름으로 범위 이름을 정의해 두고, 소분류 칸의 원본에 =INDIRECT($A2)를 넣어요. INDIRECT는 A2에 적힌 글자를 범위 이름으로 바꿔 주는 함수예요. 범위 이름에는 띄어쓰기를 쓸 수 없고 숫자로 시작할 수 없어서, 도구가 공백을 _로 바꾸고 숫자 앞에 _를 붙여요.
꼭 알아 둘 점
드롭다운은 새로 입력하는 값만 검사해요. 다른 곳에서 복사해 붙여 넣은 값은 막지 못하니, 데이터 → 데이터 유효성 검사 → ‘잘못된 데이터’에 동그라미 치기로 가끔 확인하세요. 칸을 고른 뒤 Alt+↓를 누르면 마우스 없이 목록을 열 수 있어요. 목록 원본이 다른 시트에 있어도 되지만 다른 파일에 있으면 그 파일이 열려 있어야 해요.
자주 묻는 질문
드롭다운 항목이 늘어나면 범위를 매번 고쳐야 하나요?
항목을 표(Ctrl+T)로 만들고 이름 정의를 =표1[항목]처럼 연결하면 항목을 추가할 때 자동으로 늘어나요. 엑셀 365는 =UNIQUE(…)# 같은 동적 범위도 쓸 수 있어요.
드롭다운을 지우고 싶어요.
칸을 선택하고 데이터 유효성 검사 → 모두 지우기를 누르면 입력된 값은 남고 목록 제한만 없어져요.
구글 시트에서도 되나요?
데이터 → 데이터 확인(드롭다운)에서 목록이나 범위를 지정하면 돼요. 받은 엑셀 파일을 구글 시트로 열어도 드롭다운이 유지돼요.