엑셀 드롭다운 목록 만들기: 데이터 유효성 검사, 자동으로 늘어나는 목록, INDIRECT 종속 드롭다운

1. 기본 드롭다운 만들기

1. 드롭다운을 넣을 셀 범위(예: C2:C200)를 선택합니다.
2. 데이터 탭 → 데이터 도구 → 데이터 유효성 검사를 누릅니다.
3. 설정 탭의 제한 대상에서 '목록'을 고릅니다.
4. 원본 칸에 항목을 입력하거나 범위를 지정하고 확인을 누릅니다.

• 직접 입력: 접수,진행,완료 처럼 쉼표로 구분해 적습니다. 항목이 적고 바뀔 일이 없을 때 편합니다.
• 범위 지정: 다른 시트에 목록을 적어 두고 =목록!$A$2:$A$20 처럼 참조합니다. 항목이 많거나 자주 바뀐다면 이쪽이 관리하기 쉽습니다.

2. 이름 정의로 원본 관리하기

1. 목록 범위를 선택하고 수식 탭 → 이름 정의를 누릅니다(또는 이름 상자에 직접 입력).
2. 이름을 '부서' 처럼 정합니다.
3. 데이터 유효성 검사 원본에 =부서 라고 적습니다.

3. 항목이 늘어나면 자동으로 따라오는 목록

• 표로 만들기: 목록 범위를 선택하고 Ctrl+T로 표를 만든 뒤, 표의 데이터 범위에 이름을 정의해 원본으로 씁니다. 표 아래에 항목을 추가하면 범위가 자동으로 늘어나고 목록도 따라옵니다. 가장 추천하는 방법입니다.
• OFFSET으로 동적 범위: 이름 정의의 참조 대상에 =OFFSET(목록!$A$2,0,0,COUNTA(목록!$A:$A)-1,1) 을 넣습니다. A열의 값 개수만큼 범위가 늘어납니다. 단, 목록 중간에 빈 칸이 있으면 개수가 어긋납니다.
• 동적 배열 활용(Microsoft 365, Excel 2021 이후): 다른 칸에 =SORT(UNIQUE(원본범위)) 로 정리된 목록을 만들고, 유효성 검사 원본에 =$E$2# 처럼 결과가 시작하는 셀 뒤에 #을 붙이면 펼쳐진 범위 전체가 목록이 됩니다.

4. 종속 드롭다운: INDIRECT로 연결하기

1. 목록 시트에 대분류마다 열을 하나씩 만듭니다. 첫 행에 '서울', '부산'처럼 대분류 이름을 쓰고 아래에 하위 항목을 적습니다.
2. 각 열의 하위 항목 범위에 대분류와 똑같은 이름을 정의합니다. 표 전체를 선택하고 수식 탭의 선택 영역에서 만들기 → 첫 행을 쓰면 한 번에 만들어집니다.
3. A열에는 대분류 드롭다운을 만듭니다.
4. B2를 선택하고 데이터 유효성 검사 원본에 =INDIRECT($A2) 를 입력합니다. 열만 고정하고 행은 상대참조로 두어야 아래로 복사했을 때 각 행이 자기 A열을 봅니다.

5. 설명 메시지와 오류 메시지

• 설명 메시지: 셀을 선택하면 '목록에서 골라 주세요' 같은 안내가 뜹니다.
• 오류 메시지: 목록에 없는 값을 입력했을 때의 동작을 정합니다. 스타일은 세 가지입니다.

1. 중지: 입력 자체를 막습니다. 기본값이고 가장 엄격합니다.
2. 경고: 경고를 띄우되 '예'를 누르면 입력됩니다.
3. 정보: 알림만 띄우고 입력을 허용합니다.

자주 겪는 문제

• 붙여넣기로 유효성 검사가 사라짐: 다른 셀을 복사해 붙이면 규칙도 덮어써집니다. 값만 붙여넣기(Ctrl+Alt+V → 값)를 쓰세요.
• 기존에 잘못 들어간 값 찾기: 데이터 유효성 검사 버튼 옆 화살표의 잘못된 데이터에 동그라미를 누르면 규칙에 맞지 않는 셀이 표시됩니다.
• 드롭다운 지우기: 범위를 선택하고 데이터 유효성 검사 창에서 '모두 지우기'를 누릅니다.