이 글의 목적은 엑셀에서 양식 컨트롤(Form Control) 목록 상자(List Box) 또는 콤보 상자(Combo Box) 항목이 비어 보이거나 일부만 표시되는 문제를 실무 수준에서 신속히 진단·해결할 수 있도록 체계적인 절차와 재발 방지 팁을 제공하는 것이다.
문제 개요와 증상 정의
엑셀에서 개발 도구 > 삽입 > 양식 컨트롤 > 목록 상자를 배치하고, 컨트롤 서식 > 입력 범위에 셀 범위를 지정했음에도 목록이 비어 있거나 빈 줄이 다수 섞여 보이는 현상이 발생한다. 동일 현상은 콤보 상자(Form Control)에도 동일하게 발생한다. 대표 증상은 다음과 같다.
- 목록이 전부 빈 칸으로만 보인다.
- 일부 항목만 보이고 중간중간 빈 줄이 섞여 있다.
- 동적 배열 수식 또는 표(Excel Table)를 원본으로 사용할 때 새 항목이 반영되지 않거나 모두 빈 칸으로 바뀐다.
- 시트 간 참조 또는 외부 통합 문서 참조에서만 비어 보인다.
주의 : 본 글은 양식 컨트롤(Form Control) 기준으로 설명한다. ActiveX 컨트롤(ListBox, ComboBox)은 속성·동작이 다르므로 혼용하지 않아야 한다.
핵심 원인 맵(원인→증상→해결 레버)
| 주요 원인 | 대표 증상 | 진단 포인트 | 가장 빠른 해결 레버 |
|---|---|---|---|
| 입력 범위 주소 오류(숨김 시트/잘못된 시트 참조/상대참조) | 모두 빈 항목 | 컨트롤 서식의 입력 범위가 #REF!, 잘못된 시트, 상대주소인지 확인 | 절대주소로 재지정 =Sheet1!$A$2:$A$100로 교체 |
| 빈 문자열("") 반환 수식 포함 | 빈 줄 다수 | 원본 열에 IF(...,"",...) 패턴 존재 | 필터/동적배열로 LEN(>0) 조건 걸어 공급 |
| 비표시 문자(공백, NBSP, 줄바꿈) 혼입 | 보이지만 선택 시 오류 또는 공란처럼 보임 | LEN, CODE로 제어문자 탐지 | TRIM+CLEAN+SUBSTITUTE로 정규화 |
| 동적 배열/테이블 직접 참조 미지원 | 모두 빈 항목 또는 일부만 갱신 | 입력 범위에 # 스필 영역이나 구조적 참조 사용 | INDEX로 1열 동적 범위 재작성 후 이름으로 공급 |
| 표시 서식/글꼴 색상으로 내용이 가려짐 | 빈 것처럼 보임 | 원본 셀에 사용자 지정 서식 ;;; 또는 흰색 글꼴 | 표시 서식 일반으로 복원, 글꼴 자동/검정으로 변경 |
| 워크시트 범위가 필터/숨김/병합 영향 | 간헐적 비어 보임 | 필터 적용 여부, 병합 셀 포함 여부 확인 | 별도 정규화 열 생성 후 그 열을 입력 범위로 지정 |
| 이름 정의 범위 Scope 오류 | 다른 시트에서 비표시 | 이름 관리자에서 범위가 시트 한정인지 확인 | 통합 문서 범위로 다시 정의 |
| 외부 통합 문서/닫힌 소스 참조 | 열었을 때만 정상 | 경로가 외부 파일을 향하는지 확인 | 현재 통합 문서로 데이터 복제 또는 Power Query 사용 |
| 계산 모드/이벤트 일시 중지 | 갱신 지연 | 상태 표시줄에 수동 표시 | 자동 계산으로 전환 후 재계산(F9) |
| ActiveX와 혼용 또는 컨트롤 손상 | 설정과 표시 불일치 | 컨트롤 종류 점검 | 양식 컨트롤로 교체 생성 |
1단계. 컨트롤 입력 범위와 연결 셀 기본 점검
- 목록 상자 우클릭 → 컨트롤 서식을 연다.
- 입력 범위가 절대 주소인지 확인한다. 예:
=제품목록!$A$2:$A$200로 지정한다. - 연결된 셀에 인덱스가 기록되는지 확인한다. 선택 시 숫자(1부터)가 써지면 정상이다.
- 입력 범위가 다른 통합 문서라면 우선 동일 통합 문서로 복사하여 재현성을 제거한다.
주의 : 상대 주소(예:
A2:A200)로 두면 시트 이동·복사 시 어긋나 빈 항목이 발생한다. 반드시 절대 주소를 사용한다.2단계. 공란·빈 문자열·비표시 문자 정리
원본 열에 수식이 ""를 반환하거나, 웹 복붙으로 NBSP(160), 제어문자(0~31)가 섞이면 목록 상자에 빈 줄 또는 보이지 않는 항목이 생긴다. 다음 정규화 열을 만들어 입력 범위로 사용한다.
=LET( rng, 제품표[제품명], txt, SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(rng,CHAR(160)," "))),CHAR(9)," "), FILTER(txt, LEN(txt)>0) )동적 배열이 없는 버전이라면 헬퍼 열을 단계적으로 만든다.
1) B2: =SUBSTITUTE(A2,CHAR(160)," ") 2) C2: =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(9)," "))) 3) D2: =IF(LEN(C2)>0,C2,NA()) '차트용 NA()를 쓰되 목록 원본은 E열을 사용 4) E2: =IF(LEN(C2)>0,C2,"") '목록 원본은 공란 제거 후 필터 범위로 지정 주의 : 목록 상자(Form Control)는 필터된 가시 셀만 자동으로 집계하지 않는다. 반드시 필터 결과를 별도 연속 범위로 복제하거나 동적 배열/고급 필터로 추출해 그 범위를 입력으로 준다.
3단계. 동적 범위(이름 정의)로 빈 줄 없이 공급
Form Control은 스필 참조(A2#) 또는 구조적 참조만으로 불안정할 수 있다. 가장 안전한 방식은 이름 관리자로 1열 동적 범위를 정의한 뒤 그 이름을 입력 범위에 넣는 것이다.
- 수식 > 이름 관리자 > 새로 만들기를 연다.
- 이름:
List_Items, 범위: 통합 문서. - 참조 대상(동적 길이, 빈 값 제거 버전 예시):
=LET( col, 제품목록!$A:$A, last, MATCH(2,1/(col<>"")), rng, INDEX(col,2):INDEX(col,last), FILTER(rng, LEN(rng)>0) )이후 목록 상자의 입력 범위를 =List_Items로 지정한다. 새로운 값이 추가되어도 빈 줄 없이 갱신된다.
4단계. Excel Table 원본일 때의 안정화
표(예: 제품표[제품명])를 직접 입력 범위에 주면 버전에 따라 빈 항목으로 렌더링될 수 있다. 다음 두 방식 중 하나로 안정화한다.
- 스냅샷 열을 만들어 구조적 참조를 일반 범위로 복제한다. 예:
G2:=제품표[@제품명]를 아래로 채우기 후=G2:INDEX(G:G, ...)형태의 이름 정의를 건다. - 동적 배열로 중복 제거·정렬까지 처리한 별도 열을 만든다.
=LET(u, UNIQUE(FILTER(제품표[제품명], LEN(제품표[제품명])>0)), SORT(u))주의 : 표 열에 수식이
""을 반환하면 빈 줄이 반영된다. IF(LEN(...)>0,값,"") 대신 FILTER로 집합을 축소하여 공급하는 것이 좋다.5단계. 표시 서식, 글꼴 색, 병합·숨김의 영향 제거
- 원본 셀 사용자 지정 서식이
;;;이면 값이 화면에서 숨겨져 빈 것처럼 보인다. 표시 형식: 일반로 바꾼다. - 흰색 글꼴이나 조건부 서식으로 흰색이 적용된 경우 검정으로 복원한다.
- 병합 셀은 목록 원본으로 부적합하다. 병합을 해제하고 단일 열로 정규화한다.
- 필터로 숨긴 행은 Form Control의 입력 범위에 그대로 포함된다. 필터 결과를 별도 범위로 복제하여 사용한다.
6단계. 시트/통합 문서 범위, 외부 링크 검증
- 이름 관리자에서 범위가 특정 시트로 한정되어 있으면, 다른 시트의 목록 상자에서 비어 보일 수 있다. 통합 문서 범위로 재정의한다.
- 입력 범위가 닫힌 외부 파일을 참조하면 갱신 타이밍에 따라 빈 목록으로 보일 수 있다. 데이터를 현재 통합 문서로 로드하거나 Power Query로 입력 테이블을 가져온다.
7단계. 계산 모드, 이벤트, 화면 갱신 상태 점검
수동 계산 또는 이벤트 중지 상태에서는 원본 범위만 갱신되고 컨트롤은 이전 스냅샷을 가질 수 있다. 다음 VBA를 임시로 실행해 상태를 초기화한다.
Sub ResetCalcAndRefreshList() Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Calculate End Sub주의 : 기업 환경의 일부 애드인이
ScreenUpdating 또는 이벤트를 꺼둔 채 오류로 복구하지 못하는 사례가 있다. 위 매크로로 일괄 복귀 후 동작을 확인한다.8단계. Form Control vs ActiveX 구분과 교체 전략
ActiveX ListBox/ComboBox는 RowSource 또는 List 속성으로 동작하며, Form Control의 입력 범위와 호환되지 않는다. 혼용 시 속성 창에서 보이는 주소와 실제 렌더링 주소가 달라 보일 수 있다. 문제가 지속되면 다음 절차로 컨트롤을 재구축한다.
- 문제 컨트롤을 삭제한다.
- 개발 도구 > 삽입 > 양식 컨트롤에서 목록 상자를 새로 삽입한다.
- 입력 범위에 검증된
=List_Items이름을 지정한다. - 연결된 셀을 새 셀로 지정한다.
9단계. 안정적인 동적 목록 구성 레시피(권장 패턴)
9-1. 중복 제거·정렬·공백 제거 1열 동적 이름
=LET( src, 제품표[제품명], clean, SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(src,CHAR(160)," "))),CHAR(9)," "), u, UNIQUE(FILTER(clean, LEN(clean)>0)), SORT(u) )위 수식을 이름 관리자에 정의하고 목록 상자 입력 범위에 이름을 넣는다.
9-2. 동적 배열 미지원 버전 호환 패턴
=OFFSET($G$2,0,0,COUNTA($G:$G)-1,1)G열에 정규화된 결과를 연속 데이터로 채운 뒤, OFFSET 또는 INDEX로 높이를 계산한다.
=G2:INDEX($G:$G, MATCH(2,1/($G:$G<>"")))주의 :
OFFSET은 휘발성 함수이다. 대용량 시 재계산 비용이 커질 수 있으므로 가능하면 INDEX+MATCH 조합을 권장한다.10단계. 자주 발생하는 특수 이슈와 해법
10-1. 국제화(지역 설정)로 구분자/정렬 문제
- 콤마와 세미콜론 구분자 차이로 배열 수식이 실패할 수 있다. 로케일에 맞는 구분자를 사용한다.
- 정렬 민감도(대소문자, 음절 분해)로 보기 불일치가 있을 때는 정렬 키를 별도 열로 만들어 안정화한다.
10-2. 숨겨진 행/열만 비어 보이는 현상
원본 범위에 숨겨진 행이 다수 포함된 경우, 사용자 입장에서는 빈 줄처럼 인식된다. 가시 셀만을 별도 범위로 추출한다.
=LET(r, 원본범위, FILTER(r, SUBTOTAL(103, OFFSET(r, ROW(r)-MIN(ROW(r)),0,1)) ))동적 배열 미지원이면 고급 필터를 사용해 가시 값만 복사하여 그 결과 범위를 입력으로 준다.
10-3. 유니코드 결합 문자, 폭이 0인 공백
웹 데이터에는 Zero-Width Space(8203), Zero-Width No-Break Space(65279) 등이 섞일 수 있다. 아래 정규화 함수로 제거한다.
=LET(t, A2, t1, SUBSTITUTE(t,CHAR(160)," "), t2, SUBSTITUTE(t1,UNICHAR(8203),""), t3, SUBSTITUTE(t2,UNICHAR(65279),""), TRIM(CLEAN(t3)) )10-4. 수식의 빈 문자열 정책 변경
일부 버전에서 수식이 반환한 ""가 비어 있음으로 처리되며 목록에서는 빈 줄로 표시된다. 원본 수식에서 IF 분기 시 빈 문자열을 반환하지 말고 필터 단계에서 제외하는 것이 확실하다.
10-5. 매크로에 의한 일시 변경 복구
다음 유틸리티 매크로는 이름 정의/계산/화면 상태를 점검하고 입력 범위를 재지정한다.
Sub RebindFormList(ByVal shpName As String, ByVal listName As String) Dim s As Shape On Error GoTo Fail Set s = ActiveSheet.Shapes(shpName) s.ControlFormat.ListFillRange = listName s.ControlFormat.DropDownLines = 12 Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Calculate Exit Sub Fail: MsgBox "컨트롤 이름 또는 이름 정의를 확인하라.", vbExclamation End Sub실무 진단 체크리스트(5분 버전)
- 컨트롤 종류 확인: Form Control인지 확인한다.
- 입력 범위 절대 주소·통합 문서 범위 이름인지 확인한다.
- 원본에
""반환 수식·NBSP·제어문자가 없는지 확인한다. - 테이블·스필 직접 참조 대신 이름 정의 동적 범위를 사용한다.
- 표시 서식
;;;·흰색 글꼴·병합·필터 영향 제거 후 재테스트한다.
사례별 레시피
사례 A. 피벗테이블 결과를 목록에 쓰려는데 빈 줄이 많다
피벗 결과에는 빈 문자열 셀 또는 공백만 있는 항목이 있다. 별도 열에 정규화 후 고유값 추출하여 입력한다.
=LET(src, 피벗!$B:$B, SORT(UNIQUE(FILTER(src, LEN(src)>0))))사례 B. Power Query로 가져온 표를 바로 참조하니 비어 보인다
쿼리 새로 고침 타이밍에 따라 컨트롤이 먼저 그려지면 빈 목록이 잠깐 유지될 수 있다. 쿼리 완료 후 헬퍼 열을 채우고 그 열을 입력 범위로 쓴다. 필요하면 다음 이벤트 코드를 사용한다.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next ActiveSheet.Shapes("lstProducts").ControlFormat.ListFillRange = "List_Items" End Sub사례 C. 외부 파일 닫으면 목록이 전부 빈 칸
입력 범위가 외부 통합 문서를 가리킨다. 데이터를 현재 파일로 로컬 복사하거나 Power Query로 가져온 뒤, 이름 정의를 내부 범위로 전환한다.
재발 방지 설계 패턴
- 원본 데이터는 정규화(공백·제어문자 제거) 후 별도 열로 유지한다.
- 입력 범위는 항상 이름 정의를 통해 공급한다.
- 표·피벗·외부데이터는 헬퍼 열→동적 이름의 2단 분리 구조로 배선한다.
- 매크로나 애드인이 화면/계산 상태를 바꾸면 종료 시 원복 루틴을 둔다.
빠른 점검 스크립트 모음(VBA)
'이름 정의가 유효한지 검사 Sub ValidateNamedRangeAddress() Dim nm As Name, rng As Range For Each nm In ThisWorkbook.Names On Error Resume Next Set rng = Range(nm.Name) If Err.Number <> 0 Then Debug.Print "이름 오류:", nm.Name, nm.RefersTo Err.Clear End If Next nm End Sub
'비표시 문자 일괄 제거
Sub CleanNonPrintingChars(src As Range, dst As Range)
Dim c As Range
For Each c In src.Cells
Dim t As String
t = c.Value
t = Replace(t, Chr(160), " ")
t = WorksheetFunction.Trim(WorksheetFunction.Clean(t))
t = Replace(t, ChrW(8203), "")
t = Replace(t, ChrW(65279), "")
dst.Cells(c.Row - src.Row + 1, 1).Value = t
Next c
End Sub
'목록 상자 입력 범위를 지정한 이름으로 재바인딩
Sub RebindByName(shp As String, nm As String)
On Error GoTo E
ActiveSheet.Shapes(shp).ControlFormat.ListFillRange = nm
Exit Sub
E:
MsgBox "컨트롤/이름을 확인하라."
End Sub검증용 샘플 구성
- 원본 시트 A열에 예시 데이터를 입력한다. 공백·NBSP·빈 문자열이 섞인 데이터를 일부러 포함한다.
- B열에 정규화 수식을 적용한다.
=SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))),CHAR(9)," ") - C열에
=FILTER(B2:B200, LEN(B2:B200)>0)로 집합을 만든다. - 이름 관리자에서
List_Items를 C열 스필 영역을 참조하도록 정의한다. - 목록 상자 입력 범위를
=List_Items로 설정한다.
FAQ
연결된 셀에 0 또는 공백이 기록된다. 정상인가?
연결된 셀에는 1부터 시작하는 인덱스가 기록되어야 한다. 0이나 공백은 선택이 해제되었거나 컨트롤이 입력 범위를 인식하지 못한 상태이다. 입력 범위를 이름 정의로 교체하고 절대주소인지 확인한다.
Form Control이 동적 배열(#)을 직접 참조해도 되는가?
버전·환경에 따라 불안정할 수 있다. 가장 안전한 방법은 동적 배열 결과를 이름 정의로 감싸고 그 이름을 입력 범위로 지정하는 것이다.
빈 문자열("") 대신 NA()를 쓰면 목록에서 사라지는가?
Form Control은 #N/A 셀을 그대로 무시하지 않는다. 공백 제거는 원본 단계에서 FILTER로 수행하는 것이 확실하다.
ActiveX 컨트롤로 바꾸면 문제가 사라지는가?
환경에 따라 다르다. ActiveX는 보안·배포·버전 문제를 동반한다. 조직 표준이 정해지지 않았다면 Form Control 유지가 안전하다.
Power Query 새로 고침 후 간헐적으로 빈 목록이 뜬다.
새로 고침 타이밍과 컨트롤 렌더 순서 문제일 가능성이 높다. 쿼리 완료 이벤트에 맞춰 입력 범위를 재바인딩하거나 헬퍼 열을 통해 안정화한다.
요약
목록 상자(Form Control)에서 항목이 비어 보이는 문제는 대부분 입력 범위 정의와 데이터 정규화에서 해결된다. 절대주소·통합 문서 범위의 이름 정의를 사용하고, 공백·제어문자를 제거한 1열 동적 범위를 공급하면 재발 가능성을 최소화할 수 있다. 테이블·피벗·외부데이터는 헬퍼 열을 통해 분리하여 안정화하고, 계산/이벤트 상태를 기본값으로 유지하면 운영 중단 없이 신뢰 가능한 UI를 유지할 수 있다.