엑셀에서 여러 단어가 포함된 종속 드롭다운 목록 만드는 방법 (초기화 방법까지)

마이크로소프트 엑셀(Microsoft Excel)로 작업하다 보면 데이터 입력 양식이나 엑셀 대시보드를 만들어야 하는 경우가 종종 있습니다. 데이터 입력 양식을 개발할 때 드롭다운 목록은 매우 유용한 기능입니다. 셀 안에 드롭다운 메뉴 형태로 항목 목록을 표시해 사용자가 선택할 수 있게 해주기 때문인데요. 같은 목록을 여러 셀에 반복해서 입력해야 할 때 특히 편리합니다. 이 글에서는 엑셀에서 여러 단어가 포함된 종속 드롭다운 목록을 만드는 방법을 단계별로 소개하고, 나아가 해당 목록을 지우거나 초기화하는 방법까지 함께 알려드리겠습니다.

실습용 통합 문서를 내려받아 직접 따라 해볼 수도 있습니다.

엑셀에서 종속 드롭다운 목록이란?

엑셀의 드롭다운 목록은 옵션 목록 중에서 선택할 수 있게 해주는 데이터 유효성 검사 기능입니다. 그중에서도 목록에 표시되는 항목이 다른 셀의 값에 따라 달라지는 경우, 이런 유형의 목록을 '종속 드롭다운 목록'이라고 부릅니다.

엑셀에서 여러 단어가 포함된 종속 드롭다운 목록 만드는 단계

때로는 엑셀에서 두 개 이상의 드롭다운 목록을 사용하면서, 두 번째 드롭다운 목록에 나타나는 항목이 첫 번째 드롭다운 목록의 선택 결과에 따라 달라지도록 구성하고 싶을 때가 있습니다. 바로 이것이 종속 드롭다운 목록입니다.

여러 단어가 포함된 종속 드롭다운 목록을 만들기 위해 아래와 같은 데이터 목록을 사용하겠습니다. List 시트에는 세 개의 데이터 목록이 준비되어 있습니다. 첫 번째는 과일(Fruit)과 채소(Vegetable) 두 가지 제품이 담긴 Product 목록, 두 번째는 여섯 가지 과일이 담긴 Fruit Item 목록, 마지막은 다섯 가지 채소가 담긴 Vegetable Item 목록입니다. 그럼 지금부터 엑셀에서 여러 단어가 포함된 종속 드롭다운 목록을 만드는 방법을 하나씩 살펴보겠습니다.

1단계: 통합 문서에 두 개의 시트 만들기

작업 효율을 높이기 위해 예제에서는 두 개의 시트를 만들었습니다. 하나는 Data List(데이터 목록)용 시트이고, 다른 하나는 Data Entry(데이터 입력)용 시트입니다. 통합 문서에 새 시트를 추가하려면 화면 하단의 더하기(+) 기호를 클릭하기만 하면 됩니다.

이걸로 끝입니다! 시트가 준비되었으니 이제 데이터를 입력하겠습니다.

2단계: 드롭다운 메뉴에 사용할 목록 만들기

Data List 시트에는 세 개의 데이터 열이 있습니다. B 열에는 제품(Product) 목록이 있고, 실제 제품은 두 가지뿐입니다. 하나는 D 열의 Fruit(과일)이고, 다른 하나는 F 열의 Vegetable(채소)입니다. 이제 드롭다운 목록에 사용할 목록을 만들어 보겠습니다. 아래 하위 단계를 따라 해 보세요.

• 먼저, 드롭다운 메뉴용 목록으로 만들고 싶은 열의 임의의 셀을 선택합니다.
• 다음으로, 리본 메뉴에서 홈(Home) 탭으로 이동합니다.
• 그다음, 스타일(Styles) 그룹에서 표 서식(Format as Table)을 클릭합니다.

• 그러면 표 만들기(Create Table) 대화 상자가 열립니다.
• 범위 $B$4:$B$6를 선택하고 '머리글이 있는 표(My table has headers)' 확인란에 체크합니다.
• 마지막으로 확인(OK) 버튼을 클릭합니다.

• 같은 방식으로 Fruit 목록과 Vegetable 목록도 표로 만들어 줍니다. 이렇게 하면 세 개의 목록이 모두 Data List 시트에서 엑셀 테이블 형태로 구성됩니다.

• 이제 각 목록에 대한 이름 범위(named range)를 만들어야 합니다. 이를 위해서는 Product List의 항목만 선택하고 표 머리글은 제외해야 합니다.
• 다음으로, 수식 입력줄 왼쪽에 있는 이름 상자(Name Box)를 클릭합니다.
• 그런 다음, 범위에 지정할 이름을 입력합니다. 여기서는 Product라고 입력했습니다.
• 마지막으로 Enter 키를 누릅니다.

• 동일한 절차를 Fruit와 Vegetable 목록에도 반복 적용합니다.

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

이제 Data Entry 시트에 기본 드롭다운 목록을 추가해야 합니다. 아래 하위 절차를 따라 해 보세요.

• 먼저, 데이터 범위 B4:C6를 선택합니다.
• 다음으로, 리본 메뉴에서 홈(Home) 탭으로 이동합니다.
• 그런 다음, 스타일(Styles) 그룹에서 표 서식(Format as Table) 드롭다운 메뉴를 클릭하고 원하는 표 스타일을 선택합니다.

• 그러면 표 만들기(Create Table) 팝업 창이 나타납니다.
• 이제 '표 데이터 범위(Where is the data for your table?)' 입력란에 범위를 지정합니다. 여기서는 $B$4:$C$6를 선택했습니다.
• '머리글이 있는 표(My table has headers)' 옆의 확인란에 체크합니다.
• 확인(OK)을 클릭합니다.

4단계: 서로 종속되는 드롭다운 목록 추가하기

이제 드롭다운 목록을 만들 차례입니다. 아래 하위 단계를 살펴보겠습니다.

• 먼저, 셀 범위 B4:B6를 선택합니다.
• 리본 메뉴에서 데이터(Data) 탭으로 이동합니다.
• 그런 다음, 데이터 도구(Data Tools) 그룹의 데이터 유효성 검사(Data Validation) 드롭다운 목록에서 데이터 유효성 검사를 클릭합니다.

• 그러면 데이터 유효성 검사 대화 상자가 열립니다.
• 다음으로, 설정(Settings) 메뉴의 드롭다운 목록에서 목록(List)을 선택합니다.

• 원본(Source) 입력란에 등호('=')를 입력한 뒤 목록 이름인 Product를 입력합니다. 이 이름은 앞서 Data List 시트에서 지정한 것입니다.
• 마지막으로 확인(OK)을 클릭합니다.

• 이번에는 종속 드롭다운 목록을 위해 셀 C5를 선택하고 리본 메뉴의 데이터(Data) 탭으로 이동합니다.
• 그런 다음, 데이터 유효성 검사(Data Validation) 드롭다운 메뉴에서 데이터 유효성 검사를 클릭합니다.

• 그러면 데이터 유효성 검사 대화 상자가 열립니다.
• 앞서와 마찬가지로 설정(Settings)으로 이동해 허용(Allow) 섹션의 드롭다운에서 목록(List)을 선택합니다.
• 원본(Source) 입력란에 아래 수식을 입력합니다.

• 그런 다음 확인(OK)을 클릭합니다.

여기서 INDIRECT 함수는 새로 선택한 셀 C5가 셀 B5에 종속되도록 만들어 줍니다.

• 셀 B5가 비어 있으면 아래와 같은 메시지가 나타납니다. 계속 진행하려면 예(Yes)를 선택하세요.

• 셀 C6에도 동일하게 적용합니다.

5단계: 종속 드롭다운 목록 테스트하기

이제 각 셀에 드롭다운 목록이 생긴 것을 확인할 수 있습니다. 셀 C5를 선택하고 화살표를 클릭하면 과일 목록이 표시됩니다. 셀 B5에서 Fruit(과일) 제품이 선택되어 있기 때문입니다.

• 셀 C6의 화살표를 클릭하면 채소 목록이 나타납니다. 셀 B6에서 선택된 제품이 Vegetables(채소)이기 때문입니다.

함께 읽으면 좋은 글: 엑셀에서 드롭다운으로 선택 후 다른 시트에서 데이터 가져오는 방법

엑셀에서 종속 드롭다운 목록 데이터 지우기 또는 초기화하기

목록을 선택한 뒤 상위 드롭다운 목록을 변경하면 종속 드롭다운 목록은 자동으로 바뀌지 않아 잘못된 값이 남게 됩니다. 종속 드롭다운 목록의 데이터를 지우거나 초기화하려면 엑셀 VBA와 조건부 서식을 활용하면 됩니다.

1. 엑셀 VBA로 종속 드롭다운 목록 삭제하기

엑셀 VBA를 사용하면 훨씬 복잡한 탐색, 고급 실행, 다양한 제약 조건을 구현할 수 있습니다. VBA로 종속 드롭다운 데이터 입력을 지우려면 아래 단계를 따르세요.

• 먼저, 리본 메뉴에서 개발 도구(Developer) 탭으로 이동합니다.
• 다음으로, Visual Basic을 클릭하거나 Alt + F11을 눌러 Visual Basic 편집기(VBE)를 엽니다.

• 또는 시트 탭에서 마우스 오른쪽 버튼을 클릭한 후 코드 보기(View Code)를 선택해도 Visual Basic 편집기가 열립니다.

• 이제 아래 코드를 복사해서 붙여 넣습니다.

VBA 코드:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 4 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
  End If
End If
exitHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub

• Ctrl + S를 눌러 코드를 워크시트에 저장합니다.

참고: 코드는 수정하지 말고 그대로 복사해 붙여 넣으세요. 코드를 변경하면 제대로 작동하지 않을 수 있습니다.

• 현재 Product는 Fruit이고 Item은 Banana로 되어 있습니다.
• 이제 Product를 Vegetable로 변경해 보겠습니다.

• 제품을 새로 선택하는 순간 해당 항목(Item)이 자동으로 지워지는 것을 확인할 수 있습니다.

관련 콘텐츠: 엑셀에서 드롭다운 목록 편집하는 방법 (4가지 기본 방법)

유사한 주제의 글:

• 엑셀에서 드롭다운 목록 선택에 따라 데이터 추출하는 방법
• 엑셀 드롭다운 목록에 빈 옵션 추가하기 (2가지 방법)
• 엑셀 드롭다운 목록에 항목 추가하는 방법 (5가지 방법)
• VBA로 엑셀 드롭다운 목록에서 값 선택하기 (2가지 방법)
• VBA로 드롭다운 목록에 고유 값 넣기 (완전 가이드)

2. 조건부 서식으로 일치하지 않는 항목 강조하기

잘못 입력된 값을 강조 표시하면 데이터를 한눈에 파악하기 쉽습니다. 여기서는 VLOOKUP, ISERROR, INDEX, MATCH 함수를 조합한 수식을 조건으로 사용합니다. 아래 절차를 따라 해 보세요.

• 종속 드롭다운 목록이 있는 셀을 선택합니다. 여기서는 셀 E5를 선택했습니다.
• 다음으로, 홈(Home) 탭으로 이동해 스타일(Styles) 그룹의 조건부 서식(Conditional Formatting) 드롭다운 메뉴에서 새 규칙(New Rule)을 선택합니다.

• 그러면 새 서식 규칙(New Formatting Rule) 대화 상자가 열립니다.
• 이제 '규칙 유형 선택(Select a Rule Type)' 목록에서 '수식을 사용하여 서식을 지정할 셀 결정(Use a formula to determine which cells to format)'을 선택합니다.
• '규칙 설명 편집(Edit the Rule Description)' 영역에 아래 수식을 입력합니다.

• 그런 다음, 서식(Format)을 클릭해 셀을 강조할 서식을 선택합니다.

• 또 다른 팝업 창인 셀 서식(Format Cells) 대화 상자가 나타납니다.
• 채우기(Fill) 메뉴에서 강조 표시에 사용할 색상을 고릅니다.
• 그런 다음 확인(OK)을 클릭합니다.

• 다시 새 서식 규칙(New Formatting Rule) 대화 상자로 돌아옵니다.
• 이 대화 상자에서도 확인(OK)을 클릭합니다.

• 이제 잘못된 데이터를 입력하면 해당 셀이 자동으로 강조 표시되는 것을 확인할 수 있습니다.

여기서 수식에 사용된 VLOOKUP 함수는 해당 항목이 종속 드롭다운 목록에 존재하는지 판별하는 역할을 합니다. ISERROR 함수가 이를 검사하며, 결과가 참(TRUE)일 때만 셀이 강조 표시됩니다.

함께 읽으면 좋은 글: 엑셀에서 조건부 드롭다운 목록 만들기, 정렬 및 활용법

결론

이 글을 통해 엑셀에서 여러 단어가 포함된 종속 드롭다운 목록을 만드는 방법을 익히실 수 있기를 바랍니다. 도움이 되었길 바랍니다! 궁금한 점, 제안 사항 또는 피드백이 있다면 댓글로 알려주세요. 또한 ExcelDemy.com 블로그의 다른 글들도 살펴보시길 추천합니다!

관련 글

• 엑셀에서 VLOOKUP과 드롭다운 목록 함께 사용하기
• IF문으로 엑셀 드롭다운 목록 만드는 방법
• 선택 항목에 따라 달라지는 엑셀 드롭다운 목록
• 엑셀에서 셀 값과 드롭다운 목록 연결하는 방법 (5가지 방법)
• 엑셀에서 선택 기준으로 데이터를 추출하는 드롭다운 필터 만들기