엑셀 드롭다운 목록 만들기와 2단계 연동 방법

입력 양식을 여러 사람이 함께 쓰면 표기가 제각각이 됩니다. 서울, 서울시, 서울특별시가 섞이면 나중에 집계가 안 됩니다.

드롭다운 목록으로 선택지를 고정하면 이 문제가 사라집니다. 여기에 대분류를 고르면 소분류가 바뀌는 2단계 연동까지 만들면 입력 양식으로서 제 역할을 하게 됩니다.

1단계. 기본 드롭다운 만들기

목록을 넣을 셀 범위를 선택하고 데이터 탭 → 데이터 유효성 검사 → 제한 대상을 목록으로 선택합니다.

원본 칸에 항목을 직접 입력할 수도 있습니다.

식품,의류,가전

다만 이 방식은 항목이 바뀔 때마다 유효성 설정을 다시 열어야 합니다. 항목이 서너 개로 고정된 경우가 아니라면 다음 방법을 쓰세요.

2단계. 목록을 별도 시트에 두기

목록이라는 시트를 만들고 항목을 세로로 적습니다. 그다음 그 범위에 이름을 정의합니다.

범위 선택 → 수식 탭 → 이름 정의
이름을 대분류로 지정
유효성 검사의 원본에 =대분류 입력

이렇게 하면 목록 시트에서 항목을 고치는 것만으로 드롭다운이 갱신됩니다. 유효성 설정을 다시 열 필요가 없습니다.

목록 시트는 숨겨두는 편이 깔끔합니다. 시트 탭 우클릭 → 숨기기. 숨겨도 정상 작동합니다.

3단계. 2단계 연동 만들기

대분류에서 식품을 고르면 소분류에는 식품 항목만 나오게 하는 방법입니다.

목록 시트 준비

소분류를 대분류별로 열을 나눠서 적습니다.

B열: 식품 항목들 | C열: 의류 항목들 | D열: 가전 항목들

각 열에 이름 정의

B열의 식품 항목 범위를 선택하고 이름을 식품으로 정의합니다. C열은 의류, D열은 가전.

여기가 핵심입니다. 이름이 대분류의 값과 정확히 같아야 합니다. 오타가 있으면 작동하지 않습니다.

소분류 유효성 수식

소분류 셀(B2라고 가정)을 선택하고 유효성 검사 원본에 넣습니다.

=INDIRECT($A2)

INDIRECT는 셀에 적힌 글자를 이름이나 주소로 해석하는 함수입니다. A2에 식품이 있으면 식품이라는 이름의 범위를 찾아갑니다.

행 번호에 $를 붙이지 않는 게 중요합니다. $A2는 열만 고정이라 아래로 채울 때 각 행의 A열을 참조합니다. $A$2로 하면 모든 행이 2행만 보게 됩니다.

Microsoft 365라면 더 간단합니다

=FILTER(목록!$B$2:$B$100, 목록!$A$2:$A$100=$A2)

이 방식은 목록 시트를 대분류 | 소분류 두 열로만 만들면 되고, 이름 정의가 아예 필요 없습니다. 항목이 늘어도 손댈 게 없습니다.

자주 막히는 지점 세 가지

① 대분류를 바꿔도 소분류 값이 그대로 남는다

드롭다운은 새로 입력할 때만 검사합니다. 이미 들어 있는 값은 조건이 바뀌어도 자동으로 지워지지 않습니다.

잘못된 조합을 눈에 띄게 하려면 조건부 서식을 겁니다.

=AND($B2<>"", COUNTIF(INDIRECT($A2), $B2)=0)

대분류에 속하지 않는 소분류가 들어 있으면 색이 칠해집니다. 완전한 방지는 어렵지만 검토는 가능해집니다.

② 이름에 공백이 있으면 만들 수 없다

엑셀의 이름에는 공백과 대부분의 특수문자를 쓸 수 없습니다. 생활 용품처럼 띄어쓰기가 있으면 이름 정의 자체가 안 됩니다.

해결책은 두 가지입니다.

대분류 항목명에서 공백을 빼기 (생활용품)
공백을 밑줄로 바꾸고 수식에서 치환하기 → =INDIRECT(SUBSTITUTE($A2," ","_"))

숫자로 시작하는 이름(1분기)도 안 되므로 주의하세요.

③ 항목을 추가했는데 드롭다운에 안 나온다

이름 정의 범위가 고정돼 있기 때문입니다. B2:B8로 정의했다면 B9에 추가한 항목은 포함되지 않습니다.

목록 범위를 Ctrl + T로 표로 만들면 자동 확장되지만, 유효성 검사 원본에는 표 이름을 직접 쓸 수 없습니다. 이름 정의를 한 번 거쳐야 합니다.

목록 범위를 표로 변환
이름 정의에서 참조 대상에 =표1[대분류] 형태로 입력
유효성 원본에 =대분류

자주 묻는 질문

Q. 3단계까지 연동할 수 있나요?

같은 원리로 가능합니다. 소분류 값과 같은 이름으로 3단계 범위를 정의하고 =INDIRECT($B2)를 쓰면 됩니다. 다만 이름이 수십 개로 늘어나 관리가 어려워지므로, 3단계부터는 365의 FILTER 방식을 권합니다.

Q. 목록에 없는 값도 입력할 수 있게 하려면?

유효성 검사의 오류 메시지 탭에서 스타일을 '경고' 또는 '정보'로 바꾸세요. 경고만 뜨고 입력은 허용됩니다. '중지'가 기본값입니다.

Q. 드롭다운이 걸린 셀을 복사하면 어떻게 되나요?

유효성 설정까지 함께 복사됩니다. 값만 옮기려면 값만 붙여넣기를 쓰세요. 반대로 다른 셀에 같은 설정을 퍼뜨리려면 선택하여 붙여넣기 → 유효성 검사를 선택하면 됩니다.

Q. 구글 시트에서도 되나요?

기본 드롭다운은 데이터 → 데이터 확인으로 만듭니다. 다만 구글 시트의 INDIRECT는 이름 정의와 연동이 매끄럽지 않아서, =FILTER()로 별도 셀에 목록을 만든 뒤 그 범위를 참조하는 방식이 일반적입니다.

정리

목록은 별도 시트에 두고 이름 정의로 참조
2단계 연동의 핵심은 이름 = 대분류 값
$A2 — 열만 고정
이름에 공백·특수문자·숫자 시작 불가
365라면 FILTER가 훨씬 간단

관련 글

엑셀 파일 만들 때 지켜야 할 데이터 구조 7가지
조건부 서식으로 마감일·재고 알림 만들기
가계부 카테고리 자동 분류하기, 키워드 규칙표 만드는 법