엑셀 드롭다운 목록에 빈 옵션 추가하는 방법 2가지 (삭제 방법까지 총정리)

엑셀에서 드롭다운 목록을 만들다 보면 목록 맨 앞이나 중간에 빈 옵션(blank option)을 포함하고 싶은 경우가 있습니다. 반대로 이미 만들어진 드롭다운 목록에서 불필요한 빈 항목을 제거해야 할 때도 있죠. 이 글에서는 데이터 유효성 검사(Data Validation)를 활용해 빈 옵션이 있는 드롭다운 목록을 만드는 2가지 방법과, 빈 옵션 없이 깔끔한 목록을 만드는 방법, 그리고 기존 목록에서 빈 항목을 삭제하는 방법까지 모두 알아보겠습니다.

엑셀 드롭다운 목록에 빈 옵션 추가하는 2가지 방법

방법 1. 빈 셀을 참조하여 빈 옵션 추가하기

첫 번째 방법은 원본 데이터 목록에 빈 셀을 포함시키는 것입니다. 예를 들어 과일 이름으로 구성된 데이터가 있다고 가정하고, 이 목록을 기반으로 빈 옵션이 포함된 드롭다운 목록을 만들어 보겠습니다.

진행 단계:

• 먼저 원본 목록의 시작 부분에 빈 셀을 삽입합니다. 여기서는 B5 셀을 비워 두었습니다.

• 드롭다운 목록을 만들 위치인 D5 셀에 커서를 놓습니다.

• 리본 메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사 > 데이터 유효성 검사로 이동합니다.

• 데이터 유효성 검사 대화상자가 나타나면 설정 탭에서 제한 대상 항목의 목록을 선택하고, 원본에 데이터 범위를 지정한 뒤 확인을 누릅니다. 이때 공백 무시 옵션의 체크를 해제해야 한다는 점을 꼭 기억하세요.

• 확인을 누르면 아래와 같이 빈 옵션이 포함된 드롭다운 목록이 완성됩니다.

방법 2. 목록 값을 직접 입력하여 빈 옵션 추가하기

드롭다운 목록을 만들 때 셀 참조 대신 데이터 유효성 검사 대화상자에 원본 데이터를 직접 입력할 수도 있습니다. 이 방법으로 빈 옵션을 넣어 보겠습니다.

진행 단계:

• 드롭다운 목록을 배치할 셀에 커서를 놓고, 데이터 > 데이터 도구 > 데이터 유효성 검사 > 데이터 유효성 검사 경로로 대화상자를 엽니다.

• 제한 대상에서 목록을 선택합니다. 그다음 원본 입력란에 표시할 항목들을 쉼표로 구분해 입력하되, 맨 앞에 이중 대시(—)를 먼저 입력합니다. 입력이 끝나면 확인을 누릅니다.

• 완성된 드롭다운 목록에는 이중 대시(—)가 하나의 옵션으로 표시되지만, 실제로 선택하면 빈 값이 입력됩니다.

엑셀에서 빈 옵션 없이 드롭다운 목록 만들기

이번에는 반대로 빈 옵션이 없는 드롭다운 목록을 만드는 방법을 살펴보겠습니다. 과일 이름이 담긴 긴 목록이 있다고 가정하고, 여기에서 빈 항목 없는 드롭다운 목록을 생성해 보겠습니다.

방법 1. 엑셀 테이블 활용하기

드롭다운 목록을 만들기 전에 원본 데이터를 엑셀 테이블로 변환하는 방법입니다. 엑셀 테이블의 가장 큰 장점은 원본 테이블에 새 항목을 추가하면 별도 작업 없이 드롭다운 목록도 자동으로 갱신된다는 점입니다.

진행 단계:

• 먼저 원본 데이터 범위를 선택한 뒤 Ctrl + T 단축키로 엑셀 테이블로 변환합니다.

• 그다음 이름 상자(Name Box)를 이용해 테이블에 원하는 이름을 지정합니다. 여기서는 테이블 이름을 Table1로 정했습니다.

• 이제 D5 셀에 드롭다운 목록을 만듭니다. 데이터 > 데이터 도구 > 데이터 유효성 검사 > 데이터 유효성 검사 경로로 이동한 후, 대화상자의 원본에 Table1을 지정합니다. 이때 원본 데이터의 셀 참조 앞에 $ 기호를 붙여야 한다는 점에 유의하세요. 마지막으로 확인을 누릅니다.

• 확인을 클릭하면 아래와 같이 빈 옵션 없는 드롭다운 목록이 생성됩니다.

• 이후 원본 테이블에 새 항목을 추가하면 드롭다운 목록도 자동으로 함께 업데이트됩니다.

방법 2. 이름 정의 범위(Named Range) 활용하기

이름 정의 기능으로 데이터 범위에 이름을 부여한 뒤, 해당 이름을 이용해 드롭다운 목록을 만들 수도 있습니다.

진행 단계:

• 먼저 전체 데이터 범위(B5:B14)를 선택하고, 수식 > 정의된 이름 > 이름 정의 > 이름 정의로 이동합니다.

• 새 이름 창이 나타나면 이름 입력란에 원하는 이름을 입력하고, 참조 대상을 확인한 뒤 확인을 눌러 이름 지정을 완료합니다. 여기서는 데이터 범위의 이름을 'Fruits'로 지정했습니다.

• 이제 D5 셀에 드롭다운 목록을 만듭니다. 데이터 > 데이터 도구 > 데이터 유효성 검사 > 데이터 유효성 검사로 이동한 뒤, 대화상자의 원본 필드에 '=Fruits'를 입력하고 확인을 누릅니다.

• 완료하면 아래와 같이 빈 옵션이 없는 드롭다운 목록이 완성됩니다.

동적 이름 범위 수식으로 드롭다운 목록의 빈 옵션 제거하기

지금까지는 빈 옵션의 유무에 따라 드롭다운 목록을 만드는 방법을 다뤘습니다. 이번 섹션에서는 이미 만들어진 드롭다운 목록에서 빈 옵션을 삭제하는 방법을 알아보겠습니다. 여기에는 빈 옵션이 여러 개 포함된 드롭다운 목록이 있으며, 'FruitList'라는 이름의 범위로 만든 목록이라고 가정합니다.

진행 단계:

• 먼저 수식 > 이름 관리자(정의된 이름 그룹 내)로 이동합니다.

• 이름 관리자 대화상자가 열리면 수정할 범위를 선택하고 편집을 누릅니다. 여기서는 FruitList 범위를 작업할 것이므로 해당 항목을 선택했습니다.

• 이름 편집 대화상자가 나타나면 참조 대상 필드에 아래 수식을 입력하고 확인을 누릅니다.

• 확인을 누르면 다시 이름 관리자 화면으로 돌아갑니다. 참조 대상 필드에 커서를 올려 보면, 이름 범위의 과일 항목 중 빈 셀을 제외한 부분만 선택되어 있는 것을 확인할 수 있습니다. 닫기를 눌러 작업을 마칩니다.

• 마지막으로 드롭다운 목록을 펼쳐 보면 아래와 같이 모든 빈 옵션이 제거된 깔끔한 목록이 표시됩니다.

🔎 수식의 작동 원리

• COUNTA(FIx!$B:B)-2

이 부분은 다음과 같은 결과를 반환합니다.

{10}

COUNTA 함수는 B열에서 비어 있지 않은 셀의 개수를 반환합니다. 여기서 2를 빼준 이유는, 과일이 아닌 데이터가 들어 있는 셀이 두 개이기 때문입니다.

• OFFSET(FIx!$B$5,0,0,COUNTA(FIx!$B:B)-2,1)

이 부분은 다음 배열을 반환합니다.

{"Watermelon";"Apple";"Orange";"Grapes";"Cherries";"Banana";"Kiwi";"Grapefruit";"Mandarin";"Coconut"}

OFFSET 함수는 지정한 기준 참조에서 일정한 행과 열만큼 떨어진 범위의 참조를 반환하는 함수입니다.

마무리

지금까지 엑셀 드롭다운 목록에 빈 옵션을 추가하는 여러 가지 방법을 자세히 살펴보았습니다. 빈 옵션을 의도적으로 넣는 방법부터, 처음부터 빈 항목 없이 목록을 설계하는 방법, 그리고 기존 목록의 빈 항목을 제거하는 방법까지 모두 익혔다면 상황에 맞게 활용할 수 있을 것입니다. 궁금한 점이 있다면 언제든 질문해 주세요.

함께 읽으면 좋은 글

• 엑셀 VLOOKUP 함수와 드롭다운 목록 연동하기
• 엑셀 드롭다운 목록 만들기 (독립형 및 종속형)
• IF문을 활용해 엑셀 드롭다운 목록 만들기
• 엑셀 드롭다운 목록 편집하는 방법 (4가지 기본 방법)
• 엑셀 조건부 드롭다운 목록 만들기, 정렬 및 활용법