엑셀 데이터 유효성 검사 목록에서 빈 셀 제거하는 5가지 방법

이 글에서는 엑셀의 데이터 유효성 검사 목록(Data Validation List)에서 빈 셀을 제거하는 실용적인 방법들을 소개합니다. 데이터 범위에 빈 셀이 포함되어 있으면 드롭다운 목록에도 그대로 빈 항목이 나타나기 때문에, 이를 제거하는 것이 깔끔한 작업 환경을 만드는 데 중요합니다.

아래 예제 데이터를 사용해 엑셀 데이터 유효성 검사 목록에서 빈 셀을 제거하는 방법을 단계별로 살펴보겠습니다.

빈 셀이 포함된 상태로 드롭다운 목록을 만들 때 발생하는 문제

먼저 빈 셀이 포함된 범위로 드롭다운 목록을 만들면 어떤 일이 발생하는지 확인해 보겠습니다.

단계:

• 셀 C5를 선택합니다.
• 데이터 >> 데이터 도구 >> 데이터 유효성 검사를 차례로 클릭합니다.

• 대화상자가 나타나면 제한 대상에서 목록을 선택합니다.

• 그 다음 표시된 아이콘을 클릭합니다.

• 셀 B5부터 B14까지 선택한 뒤 다시 표시된 아이콘을 클릭합니다.

• 마지막으로 확인을 누릅니다.

이렇게 하면 드롭다운 목록이 생성됩니다.

여기서 주목할 점은 드롭다운 목록에 빈 셀까지 함께 표시된다는 것입니다. 이제 이 빈 항목들을 목록에서 제외하는 방법을 알아보겠습니다.

엑셀 데이터 유효성 검사 목록에서 빈 셀을 제거하는 5가지 방법

1. OFFSET 함수를 활용해 빈 셀 없는 목록 만들기

이 방법은 열에 빈 셀이 없는 드롭다운 목록을 구성하면서도 새 항목을 추가할 여유 공간을 확보할 수 있는 기법입니다. 먼저 데이터에서 빈 셀을 필터링해야 합니다.

데이터 집합을 약간 수정해야 합니다.

• 드롭다운 목록에 사용 중인 열 앞에 새 열을 하나 추가합니다. 새 열과 기존 열의 이름을 각각 공백 포함 이름 목록, 공백 제외 목록으로 지정했습니다. (드롭다운 목록 생성 방법은 1번 섹션 참고)

단계:

• 먼저 셀 C5에 아래 수식을 입력합니다.

FILTER 함수는 범위 B5:B14를 검사하여 해당 범위 안의 빈 셀을 찾아내고, 비어 있는 셀들을 목록에서 걸러냅니다.

• Enter 키를 누르면 빈 셀이 없는 이름 목록이 표시됩니다.

• 그 다음 수식 탭에서 이름 관리자를 선택하고 새로 만들기를 클릭합니다.

• 범위에 이름을 지정합니다. 여기서는 NameNonBlanks라고 명명하겠습니다.
• 찾는 범위(참조 대상)에 다음 수식을 입력합니다.

이 시나리오의 목표는 C12부터 C16까지 새로운 이름을 입력할 여분의 셀을 사용하되, 그 자리가 드롭다운 목록에는 빈 항목으로 나타나지 않도록 하는 것입니다. 위 수식으로 C12~C16 영역을 데이터 유효성 검사 목록의 동적 범위에 포함시킬 수 있습니다. 'offset!'은 현재 사용 중인 시트 이름을 의미한다는 점에 유의하세요.

• 확인을 클릭하면 창이 하나 나타나는데, 그냥 닫으면 됩니다.

• 셀 D5를 선택한 후 데이터 >> 데이터 유효성 검사를 엽니다.
• 원본(Source) 값을 =NameNonBlanks로 변경합니다.
• 확인을 클릭합니다.

• 셀 D5의 드롭다운 목록을 열면 등록된 이름들만 표시되는 것을 볼 수 있습니다.

• 이제 셀 C12부터 C16까지 새 이름을 입력해 보세요.
• 그런 다음 데이터 유효성 검사 목록이 적용된 셀 D5를 다시 확인합니다.

새로 입력한 이름들이 드롭다운 목록에 자동으로 반영됩니다. 단, 셀 C16 아래에 입력한 내용은 설정된 범위를 벗어나므로 목록에 나타나지 않습니다.

이 방식을 활용하면 드롭다운 목록에 빈 항목을 노출하지 않으면서도 새 항목을 위한 여유 공간을 확보할 수 있습니다.

더 읽어보기: 엑셀에서 데이터 유효성 검사 드롭다운 목록 만드는 8가지 방법

2. 이동 옵션(Go To Special) 명령으로 목록에서 빈 셀 제거하기

앞서 만든 드롭다운 목록(1번 섹션)에는 여전히 빈 항목이 남아 있습니다. 이를 제거하려면 아래 단계를 따르면 됩니다.

단계:

• 셀 B5부터 B14까지 선택한 후 홈 >> 찾기 및 선택 >> 이동 옵션을 클릭합니다.

• 빈 셀을 선택하고 확인을 클릭합니다.

• 이 작업을 통해 빈 셀들이 한꺼번에 선택됩니다.

• 선택된 빈 셀 중 아무 곳이나 마우스 오른쪽 버튼으로 클릭한 후 삭제를 선택해 빈 셀을 삭제합니다.

• 대화상자가 나타나면 아래 셀을 위로 이동을 선택하고 확인을 클릭합니다.

• 이렇게 하면 원본 목록과 드롭다운 목록 모두에서 빈 항목이 사라집니다.

이 방법을 사용하면 드롭다운 목록에서 빈 셀을 손쉽게 제거할 수 있습니다.

더 읽어보기: 엑셀에서 다중 선택 가능한 데이터 유효성 검사 드롭다운 목록 만들기

3. FILTER 함수로 데이터 유효성 검사 목록의 빈 셀 제거하기

FILTER 함수를 활용하는 방법입니다. 2번 섹션의 데이터를 사용하며, 드롭다운 목록 생성 방법은 1번 섹션을 참고하세요.

단계:

• 셀 C5에 다음 수식을 입력합니다.

FILTER 함수는 범위 B5:B14를 검사하여 빈 셀을 찾아내고, 비어 있거나 공백인 셀들을 목록에서 필터링합니다.

• Enter 키를 누르면 빈 셀이 없는 이름 목록이 표시됩니다.

• 하지만 드롭다운 목록을 열어보면 여전히 C열의 빈 항목이 포함되어 있습니다.

• 이 빈 항목을 제거하려면 데이터 탭에서 데이터 유효성 검사로 이동합니다.
• 필터링된 목록의 범위가 C5~C11이므로 원본(Source) 범위의 마지막 셀을 C11로 변경합니다.

• 확인을 클릭하면 드롭다운 목록에 더 이상 빈 셀이 표시되지 않습니다.

드롭다운 목록에서 빈 셀을 제거하는 매우 효율적인 방법입니다.

더 읽어보기: 필터 기능이 있는 엑셀 데이터 유효성 검사 드롭다운 목록 (예제 2가지)

관련 글:

• 엑셀 데이터 유효성 검사에서 사용자 지정 VLOOKUP 수식 활용법
• [해결] 엑셀에서 복사-붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제
• 엑셀 표(Table)로 데이터 유효성 검사 목록 만드는 3가지 방법
• 엑셀 한 셀에 여러 개의 데이터 유효성 검사 적용하기 (예제 3가지)
• 엑셀 데이터 유효성 검사에서 숫자와 문자만 허용하기 (사용자 지정 수식)

4. IF, COUNTIF, ROW, INDEX, SMALL 함수 조합으로 빈 셀 제거하기

IF, COUNTIF, ROW, INDEX, SMALL 함수를 조합해서도 데이터 유효성 검사 목록의 빈 셀을 제거할 수 있습니다. 다소 복잡하지만 단계별로 살펴보겠습니다. 2번 섹션의 데이터를 사용하며, 드롭다운 목록 생성은 1번 섹션을 참고하세요.

단계:

• 먼저 셀 C5에 다음 수식을 입력합니다.

이 수식은 크게 두 부분으로 구성됩니다. 첫 번째 부분은 COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,""이고, 두 번째 부분은 INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5)))입니다.

• COUNTIF 함수는 여기서 빈 셀이 아닌 텍스트의 개수를 세므로, C열에 7개의 이름이 반환됩니다.
• ROW 함수는 셀의 행 번호를 반환하며, 첫 빈 셀은 B5 기준 5번째 위치에 있습니다. 결과값을 그보다 작게 만들기 위해 4를 빼줍니다.
• Enter 키를 누릅니다.

• 채우기 핸들을 사용해 아래 셀까지 자동 채움(AutoFill)합니다.

• 이제 빈 셀이 없는 이름 목록이 완성되었습니다. 하지만 데이터 유효성 검사 목록을 클릭해 보면 드롭다운에 여전히 빈 항목이 남아 있습니다.

• 이 빈 항목을 제거하려면 데이터 탭에서 데이터 유효성 검사로 이동합니다.
• 필터링된 목록의 범위가 C5~C11이므로 원본 범위의 마지막 셀을 C11로 변경합니다.

• 확인을 클릭하면 드롭다운 목록에서 빈 셀이 완전히 사라집니다.

이렇게 하면 빈 셀이 없는 데이터 유효성 검사 목록을 만들 수 있습니다.

더 읽어보기: 엑셀 VBA로 데이터 유효성 검사 목록의 기본값 설정하기 (매크로 및 사용자 폼)

5. ADDRESS, INDIRECT, COUNTBLANK 등 결합 함수로 빈 셀 제거하기

ADDRESS, INDIRECT, COUNTBLANK, IF, SMALL 함수를 결합한 중첩 수식을 사용할 수도 있습니다. 절차를 살펴보겠습니다. 2번 섹션의 데이터를 사용하며, 드롭다운 목록 생성 방법은 1번 섹션을 참고하세요.

단계:

• 먼저 셀 C5에 다음 수식을 입력합니다.

이 수식의 작동 원리를 간단히 설명하면 다음과 같습니다. 수식은 범위 B5:B14를 훑으며 COUNTBLANK 함수로 빈 셀을 확인하고, 동시에 B5:B14에서 비어 있지 않은 셀들을 판별해 빈 셀이 아닌 값들만 반환합니다.

• 배열 수식이므로 Ctrl + Shift + Enter를 눌러야 하며, 그러면 셀 C5에 아래와 같은 결과가 표시됩니다.

• 채우기 핸들로 아래 셀까지 자동 채움합니다.

• 하지만 드롭다운 목록을 열어보면 여전히 C열의 빈 항목이 포함되어 있습니다.

• 이를 제거하려면 데이터 탭에서 데이터 유효성 검사로 이동합니다.
• 필터링된 목록의 범위가 C5~C11이므로 원본 범위의 마지막 셀을 C11로 변경합니다.

• 확인을 클릭하면 드롭다운 목록에 더 이상 빈 셀이 없습니다.

이것으로 드롭다운 목록에서 빈 셀을 제거하는 또 다른 방법을 알아보았습니다.

더 읽어보기: 엑셀 데이터 유효성 검사 수식에서 IF 문 활용법 (6가지 방법)

연습 섹션

이 섹션에서는 소개한 방법들을 직접 연습해 볼 수 있도록 데이터 집합을 제공합니다.

마무리

지금까지 엑셀의 데이터 유효성 검사 목록에서 빈 셀을 제거하는 다양한 방법을 살펴보았습니다. 몇 가지 방법은 다소 복잡할 수 있지만, 최대한 쉽고 이해하기 편하게 설명하려고 노력했습니다. 이 글이 도움이 되었다면 댓글로 피드백을 남겨주시고, 여러분만의 아이디어나 궁금한 점이 있다면 언제든지 댓글로 공유해 주세요.

관련 글

• 엑셀에서 여러 조건에 맞는 사용자 지정 데이터 유효성 검사 적용하기 (예제 4가지)
• 엑셀에서 색상과 함께 데이터 유효성 검사 활용하기 (4가지 방법)
• 다른 시트의 데이터로 유효성 검사 목록 만들기 (6가지 방법)
• 엑셀 VBA로 배열에서 데이터 유효성 검사 목록 만들기
• 엑셀 VBA로 이름 정의된 범위를 활용한 데이터 유효성 검사 목록 사용법