Excel의 드롭다운 목록은 매우 강력한 도구입니다. 사용자에게 드롭다운 화살표를 제공하고, 이를 클릭하면 선택할 수 있는 항목 목록이 나타나도록 설정할 수 있습니다.
이 기능을 활용하면 사용자가 직접 답변을 입력하지 않아도 되기 때문에 데이터 입력 오류를 크게 줄일 수 있습니다. 심지어 Excel에서는 셀 범위에 있는 데이터를 드롭다운 목록 항목으로 불러올 수도 있습니다.
그런데 여기서 끝이 아닙니다. 드롭다운 셀에 대한 데이터 유효성 검사를 창의적으로 구성하면 여러 개의 연동된 드롭다운 목록까지 만들 수 있습니다. 즉, 두 번째 목록에서 선택 가능한 항목이 첫 번째 목록에서 사용자가 선택한 값에 따라 달라지도록 설정하는 것입니다.
연동 드롭다운 목록은 어떤 경우에 유용할까요?
온라인에서 양식을 작성해 본 경험이 있다면, 앞선 드롭다운 목록에서 선택한 답변에 따라 다음 드롭다운 목록의 내용이 자동으로 바뀌는 것을 본 적이 있을 것입니다. 바로 이런 방식으로 Excel 데이터 입력 시트도 온라인 양식만큼 정교하게 만들 수 있습니다. 사용자의 답변에 따라 시트 스스로 내용을 조정하는 것이죠.
예를 들어, 컴퓨터 수리가 필요한 사용자들로부터 컴퓨터 정보를 수집하는 Excel 스프레드시트를 운영한다고 가정해 보겠습니다.
입력 옵션은 다음과 같이 구성될 수 있습니다.
- 부품(Computer Part): 모니터, 마우스, 키보드, 본체
- 세부 유형(Part Type):
- 모니터: 액정, 하우징, 전원 코드, 내부 회로
- 마우스: 휠, LED 조명, 케이블, 버튼, 케이스
- 키보드: 키캡, 하우징, 멤브레인, 케이블, 내부 회로
- 본체: 케이스, 버튼, 포트, 전원 장치, 내부 부품, 운영체제
위 트리 구조에서 볼 수 있듯이, '세부 유형'에서 선택 가능한 항목은 사용자가 첫 번째 드롭다운 목록에서 어떤 부품을 선택했는지에 따라 달라집니다.
이 예시에서 스프레드시트는 처음에 대략 다음과 같은 모습일 것입니다.
B1 셀의 드롭다운 목록에서 선택한 항목을 B2 셀의 드롭다운 목록 내용을 결정하는 데 활용하면, 여러 개의 연동 드롭다운 목록을 완성할 수 있습니다.
지금부터 설정 방법을 살펴보겠습니다. 아래 예제가 포함된 샘플 Excel 파일을 직접 내려받아 따라 해 보셔도 좋습니다.
1단계: 드롭다운 목록 소스 시트 만들기
이러한 구조를 가장 깔끔하게 설정하는 방법은 Excel에 새 탭을 만들어 드롭다운 목록에 사용할 모든 항목을 그곳에 정리하는 것입니다.
연동 드롭다운 목록을 설정하려면 먼저 표를 만듭니다. 맨 위 행에는 첫 번째 드롭다운 목록에 넣을 모든 부품 이름을 헤더로 입력하고, 각 헤더 아래에 해당하는 세부 유형 항목들을 나열합니다.
다음으로, 나중에 데이터 유효성 검사를 설정할 때 올바른 범위를 선택할 수 있도록 각 범위에 이름을 지정해야 합니다.
방법은 각 열 아래의 항목들을 모두 선택한 뒤, 해당 범위의 이름을 헤더와 동일하게 지정하는 것입니다. 범위 이름 지정은 'A'열 위쪽에 있는 이름 상자에 원하는 이름을 입력하면 됩니다.
예를 들어, A2부터 A5까지의 셀을 선택한 후 해당 범위를 "모니터"라고 이름 짓는 식입니다.
이 과정을 반복하여 모든 범위에 알맞은 이름을 붙여줍니다.
또 다른 방법으로는 Excel의 선택 영역에서 만들기(Create from Selection) 기능을 사용하는 것입니다. 위의 수동 작업과 동일한 결과를 단 한 번의 클릭으로 얻을 수 있습니다.
이 방법을 사용하려면 두 번째 시트에서 모든 데이터 범위를 선택한 후, 메뉴에서 수식(Formulas)을 클릭하고 리본 메뉴에서 선택 영역에서 만들기를 선택합니다.
팝업 창이 나타나면 첫 행(Top row)만 선택되어 있는지 확인한 후 확인(OK)을 누릅니다.
그러면 첫 행의 헤더 값이 그 아래에 있는 각 범위의 이름으로 자동 지정됩니다.
2단계: 첫 번째 드롭다운 목록 설정하기
이제 여러 개의 연동 드롭다운 목록을 실제로 구성할 차례입니다. 절차는 다음과 같습니다.
1. 첫 번째 시트로 돌아가서, 첫 번째 라벨 오른쪽의 빈 셀을 선택합니다. 그런 다음 메뉴에서 데이터(Data)를 클릭하고 리본 메뉴에서 데이터 유효성 검사(Data Validation)를 선택합니다.
2. 열리는 데이터 유효성 검사 창에서 제한 대상(Allow) 항목을 목록(List)으로 설정하고, 원본(Source) 항목 오른쪽의 위쪽 화살표 아이콘을 클릭합니다. 이렇게 하면 드롭다운 목록의 소스로 사용할 셀 범위를 직접 선택할 수 있습니다.
3. 드롭다운 목록 소스 데이터를 구성해 둔 두 번째 시트로 이동하여, 헤더 필드만 선택합니다. 선택한 헤더들이 앞서 지정한 셀의 초기 드롭다운 목록을 채우게 됩니다.
4. 선택 창의 아래쪽 화살표를 클릭해 데이터 유효성 검사 창을 다시 펼치면, 방금 선택한 범위가 원본(Source) 필드에 표시되는 것을 확인할 수 있습니다. 확인(OK)을 눌러 마무리합니다.
5. 이제 메인 시트로 돌아가 보면, 첫 번째 드롭다운 목록에 두 번째 시트의 헤더 필드들이 모두 포함되어 있는 것을 확인할 수 있습니다.
첫 번째 드롭다운 목록이 완성되었으니, 이제 연동되는 두 번째 드롭다운 목록을 만들 차례입니다.
3단계: 두 번째(연동) 드롭다운 목록 설정하기
첫 번째 셀에서 선택한 값에 따라 항목이 달라질 두 번째 셀을 선택합니다.
앞서 설명한 과정을 반복해 데이터 유효성 검사 창을 엽니다. 제한 대상(Allow) 드롭다운에서 목록(List)을 선택합니다. 이번에는 원본(Source) 필드에 첫 번째 드롭다운 목록에서 무엇을 선택했느냐에 따라 목록 항목을 불러오는 수식을 입력해야 합니다.
다음 수식을 입력하세요.
=INDIRECT($B$1)
INDIRECT 함수는 어떻게 작동할까요?
이 함수는 텍스트 문자열로부터 유효한 Excel 참조(여기서는 셀 범위)를 반환합니다. 위 수식에서 텍스트 문자열은 첫 번째 셀($B$1)이 전달하는 범위 이름입니다. 즉, INDIRECT 함수가 범위 이름을 받아서, 데이터 유효성 검사에 해당 이름과 연결된 올바른 범위를 제공하는 원리입니다.
참고: 첫 번째 드롭다운 목록에서 값을 선택하지 않은 상태로 두 번째 드롭다운의 데이터 유효성 검사를 구성하면 오류 메시지가 나타날 수 있습니다. 이때 예(Yes)를 선택하면 오류를 무시하고 계속 진행할 수 있습니다.
이제 새로 만든 연동 드롭다운 목록을 테스트해 보세요. 첫 번째 드롭다운에서 부품 중 하나를 선택한 다음, 두 번째 드롭다운을 열면 해당 부품에 맞는 세부 유형 항목들만 표시되는 것을 확인할 수 있습니다. 이 항목들은 두 번째 시트에서 해당 부품 열에 입력해 둔 값들입니다.
마무리: Excel에서 연동 드롭다운 목록 활용하기
지금까지 살펴본 것처럼, 이 방법을 활용하면 스프레드시트를 훨씬 더 동적으로 만들 수 있습니다. 사용자가 특정 셀에서 선택한 값에 따라 이후의 드롭다운 목록 내용이 자동으로 바뀌도록 구성하면, 스프레드시트의 사용자 반응성이 크게 향상되고 데이터의 활용 가치도 훨씬 높아집니다.
위에서 소개한 팁들을 직접 응용해 보면서, 여러분의 스프레드시트에 어떤 흥미로운 연동 드롭다운 목록을 만들 수 있는지 실험해 보시기 바랍니다. 여러분만의 유용한 팁이 있다면 댓글로 공유해 주세요.