엑셀에서 표(Table)의 맨 아래에 새로운 항목을 입력하면 표가 자동으로 확장됩니다. 바로 이 기능 때문에 표는 엑셀 사용자에게 가장 강력한 도구 중 하나로 꼽힙니다. 한편 데이터 유효성 검사(Data Validation) 목록은 표 데이터에 잘못된 값이 입력되는 것을 방지해 주지만, 표에 새 데이터가 추가될 때마다 유효성 검사 목록도 함께 갱신해 주어야 합니다.
이번 글에서는 표를 기반으로 동적으로 자동 갱신되는 엑셀 데이터 유효성 검사 목록을 만드는 방법을 단계별로 소개합니다.
엑셀 표에서 데이터 유효성 검사 목록을 만드는 3가지 방법
아래 이미지와 같은 예제 데이터 세트를 사용해 유효성 검사 목록을 적용해 보겠습니다.
일반적으로 데이터 탭에서 데이터 유효성 검사 옵션을 열어 설정을 시작합니다.
그다음 허용 항목에서 목록을 선택하고, 머리글이 포함된 표 이름(Table179[States])을 입력합니다.
그러나 이 방법은 작동하지 않습니다. 아래 이미지처럼 오류 메시지 상자가 나타납니다. 이 문제를 해결하기 위해 세 가지 접근 방식을 사용할 수 있습니다. 첫 번째는 셀 참조 활용, 두 번째는 이름 정의(명명된 범위) 활용, 마지막은 INDIRECT 함수를 데이터 유효성 검사 목록에 적용하는 방법입니다.
1. 셀 참조를 이용한 데이터 유효성 검사 목록 만들기
데이터 유효성 검사 목록에 직접 셀 참조를 적용하려면 아래 단계를 따르세요.
1단계:
- 데이터 탭으로 이동하여 데이터 유효성 검사를 선택합니다.
- 허용 항목에서 목록을 선택합니다.
2단계:
- 원본(Source) 입력란에 표의 머리글을 제외한 범위 B5:B11을 선택합니다.
- 마지막으로 Enter 키를 누릅니다.
3단계:
- 이제 데이터 유효성 검사 드롭다운 목록이 생성됩니다.
4단계:
- 표 맨 아래에 새 항목 'Texas'를 추가로 입력합니다.
5단계:
- 그 결과 'Texas'가 데이터 유효성 검사 목록에 자동으로 추가된 것을 확인할 수 있습니다.
표의 동적 확장 특성 덕분에 새 행이 추가되면 참조 범위도 함께 늘어나므로, 별도의 수정 없이 목록이 자동으로 갱신됩니다.
2. 이름 정의(명명된 범위)를 이용한 데이터 유효성 검사 목록 만들기
표의 특정 범위에 이름을 지정한 뒤 이를 활용할 수도 있습니다. 이름 정의를 통해 데이터 유효성 검사 목록을 만들려면 다음 지침을 따르세요.
1단계:
- 표 머리글을 제외한 셀 범위를 선택합니다.
2단계:
- 수식 탭을 클릭합니다.
- 이름 관리자(Name Manager)를 클릭합니다.
3단계:
- 새로 만들기(New)를 클릭합니다.
4단계:
- 원하는 이름을 입력합니다. 여기서는 'Named_Range'라고 입력했습니다.
- Enter 키를 누릅니다.
5단계:
- 데이터 유효성 검사의 원본 입력란에 다음과 같이 정의한 이름을 입력합니다.
=Named_Range
6단계:
- Enter 키를 눌러 목록이 정상적으로 표시되는지 확인합니다.
7단계:
- 표의 맨 아래 셀에 'Texas'를 입력합니다.
8단계:
- 드롭다운 옵션에 'Texas' 항목이 자동으로 추가됩니다.
참고로, 명명된 범위는 표 구조 참조보다 직관적이며 수식 관리가 쉬워진다는 장점이 있습니다. 여러 시트에서 반복적으로 사용할 경우 특히 유용합니다.
3. INDIRECT 함수를 이용한 데이터 유효성 검사 목록 만들기
또한 데이터 유효성 검사 원본 입력란에 함수를 사용할 수 있습니다. 여기서는 INDIRECT 함수를 적용해 보겠습니다. INDIRECT 함수는 특정 텍스트가 가리키는 범위를 찾아 반환하는 함수로, 표의 구조화된 참조 이름을 문자열로 받아 실제 범위로 변환해 줍니다. 함수를 적용하려면 아래 단계를 따르세요.
1단계:
- 임의의 빈 셀에 '=' 등호를 입력한 뒤 표의 범위를 선택합니다.
- 수식 표시줄에서 범위 이름 'Table18[States]'를 복사합니다.
2단계:
- 복사한 이름을 활용해 다음과 같이 INDIRECT 함수가 포함된 수식을 입력합니다.
=INDIRECT("Table18[States]")
3단계:
- Enter 키를 눌러 목록이 표시되는지 확인합니다.
4단계:
- 표의 맨 아래에 새 텍스트를 입력합니다.
5단계:
- 입력한 항목이 데이터 유효성 검사 목록에 자동으로 추가됩니다.
INDIRECT 방식은 표 이름만 알면 되므로 범위를 일일이 지정할 필요가 없다는 점에서 가장 유연한 방법으로 평가받습니다.
마무리
지금까지 엑셀 표에서 데이터 유효성 검사 목록을 만드는 세 가지 방법을 살펴보았습니다. 셀 참조, 이름 정의, INDIRECT 함수 각각의 장단점을 이해하고 실제 데이터에 적용해 보면서 연습해 보시기 바랍니다. 새 데이터가 추가되어도 목록이 자동으로 갱신되는 동적 유효성 검사는 데이터 입력 오류를 줄이고 작업 효율을 크게 높여 줍니다.
궁금한 점이 있다면 언제든지 문의해 주세요. 아래 댓글란에 의견을 남겨 주시면 성심껏 답변드리겠습니다.
앞으로도 유익한 엑셀 팁으로 찾아오겠습니다. 함께 배워 나가요!