이 글의 목적은 엑셀 차트에서 항목 축 또는 계열 데이터에 빈 셀이 포함될 때 발생하는 잘못된 표시, 누락, 0값 처리, 불연속 연결 등의 문제를 체계적으로 진단하고, 실제 현장에서 바로 적용 가능한 설정 변경, 수식, 동적 범위, 보조열, VBA 등 다층적 해결책을 제시하는 것이다.
문제 개요와 증상 정의
차트 범위 안에 빈 셀이 존재하면 엑셀이 이를 여러 방식으로 해석하여 시각화 오류가 발생한다. 대표 증상은 다음과 같다.
- 범주(항목) 축에 빈 레이블이 표시되어 간격이 과도하게 벌어지거나 불연속이 어색하게 보이는 현상이다.
- 계열 값이 빈 셀을 0으로 인식하여 꺾은선이 바닥으로 떨어지는 급격한 변동이 나타난다.
- 빈 구간이 단절로 처리되어 선이 끊기거나, 반대로 임의로 연결되어 트렌드 왜곡이 발생한다.
- 날짜 축을 사용할 때 존재하지 않는 날짜까지 자동 보간되어 원치 않는 틈이 생긴다.
주의 : 같은 “빈 셀”이라도 진짜 빈 셀(NULL)과 빈 문자열("")은 차트에서 다르게 동작한다. 또한 텍스트 "0", 숫자 0, #N/A도 각각 다른 의미로 처리되므로 원인 유형을 먼저 구분해야 한다.
우선 점검 체크리스트
| 점검 항목 | 의미 | 권장 조치 |
|---|---|---|
| 빈 셀 유형 | 빈 셀, 빈 문자열(""), 공백 포함 텍스트, 숫자 0, 오류 | ISTEXT, ISNUMBER, LEN, TYPE로 분류한다. |
| 축 유형 | 텍스트 축 vs 날짜 축 | 날짜 축이면 비어 있는 날짜가 생성될 수 있다. |
| 계열 옵션 | 숨겨진/빈 셀 표시 방식 | 갭·0·데이터 연결 중 목적에 맞게 선택한다. |
| 원본 범위 | 필터, 숨김, 부분합, 동적 범위 | 필터/숨김 포함 여부와 동적 배열 사용을 검토한다. |
| 데이터 품질 | 앞뒤 공백, 비가시 문자, 혼합 형식 | TRIM, CLEAN, VALUE 등으로 정규화한다. |
해결 1: 차트의 “숨겨진 및 빈 셀 설정” 정확히 지정
가장 먼저 시도할 수 있는 방법은 차트 자체의 빈 셀 표시 정책을 바꾸는 것이다.
- 차트를 클릭한다.
- 데이터 선택(Select Data) → 숨겨진 및 빈 셀( Hidden and Empty Cells )을 클릭한다.
- 빈 셀 표시 옵션에서 간격(갭), 0, 데이터 요소 연결 중 목적에 맞게 선택한다.
- 필요 시 “숨겨진 행과 열의 데이터 표시”를 활성화한다.
주의 : “데이터 요소 연결”은 추세선을 연속적으로 보이게 하지만 실제 데이터를 보간하는 것은 아니다. 공백 구간의 실제 값이 0 또는 미측정인지에 따라 통계 해석이 달라질 수 있다.
해결 2: 데이터 쪽에서 #N/A로 명확히 배제
계열 값에서 빈 셀을 시각적으로 배제하고 싶다면 #N/A를 사용한다. 엑셀 차트는 #N/A를 점으로 그리지 않는다.
=IF(A2="","",A2) ' 차트에 빈 문자열은 경우에 따라 0처럼 보일 수 있다 =IF(A2="",NA(),A2) ' 빈 셀은 #N/A로 반환하여 차트에서 점을 그리지 않게 한다 =IF(LEN(A2)=0,NA(),A2) ' LEN 기준으로 빈 문자열까지 포착한다 범주 레이블을 유지하면서 값만 배제하려면 값 열에만 NA() 처리를 적용한다.
주의 : NA()와 N/A 텍스트는 다르다. N/A라는 문자열은 오류가 아니므로 점이 그려질 수 있다. 반드시 NA() 함수를 사용한다.
해결 3: 빈 문자열("") vs 진짜 빈 셀 구분
수식 결과로 ""를 반환하면 셀은 비어 보이지만 실질적으로 텍스트로 취급되어 일부 차트에서 0 또는 값으로 간주될 수 있다. 엄정한 배제를 원하면 NA()를, 시각적 공백만 원하면 갭 처리를 선택한다.
' 잘못된 예: "" 반환은 차트에서 혼란을 만든다 =IF(측정값이없음,"",측정값)
' 권장 예: 차트 배제
=IF(측정값이없음,NA(),측정값)
해결 4: 날짜 축 자동 채움 제어
범주가 날짜인 꺾은선차트는 기본적으로 날짜 축을 사용한다. 이 경우 실제 없는 날짜까지 생성되어 빈 구간이 길어지거나 빈 값이 0처럼 해석되는 착시가 생긴다.
- 축 서식 → 축 옵션에서 “축 유형”을 텍스트 축으로 바꾸면 존재하는 날짜만 표시한다.
- 실제 시간 간격 비례가 중요하면 날짜 축을 유지하고 값은 NA() 처리하여 단절을 표현한다.
주의 : 텍스트 축은 간격이 균등하다. 시계열 분석에서 시간 간격 보존이 중요하다면 날짜 축 + NA()가 더 합리적이다.
해결 5: 동적 범위로 “빈 값 제외” 후 차트 연결
동적 배열을 지원하는 버전에서는 FILTER로 빈 값을 제외한 정제 범위를 만들고, 그 범위를 차트에 연결한다.
' A2:A는 범주, B2:B는 값이라고 가정 ' 1) 행 필터링 키 생성 =LET(rA,A2:A1000,rB,B2:B1000,FILTER(HSTACK(rA,rB),rB<>""))
' 2) 범주와 값 각각 생성
=LET(rA,A2:A1000,rB,B2:B1000,FILTER(rA,rB<>""))
=LET(rA,A2:A1000,rB,B2:B1000,FILTER(rB,rB<>""))
동적 배열이 없는 환경에서는 정의 이름과 INDEX/SMALL/IF 조합으로 빈 값을 제거한 압축 범위를 구성한다.
' 정의 이름 cat_rng =INDEX($A$2:$A$1000,SMALL(IF($B$2:$B$1000<>"",ROW($B$2:$B$1000)-ROW($B$2)+1),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$1000,"<>")))))
' 정의 이름 val_rng
=INDEX($B$2:$B$1000,SMALL(IF($B$2:$B$1000<>"",ROW($B$2:$B$1000)-ROW($B$2)+1),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$1000,"<>")))))
주의 : 위 수식은 배열 수식이다. 구버전에서는 Ctrl+Shift+Enter로 확정한다. 정의 이름을 차트 계열의 계열 값과 범주 레이블에 직접 지정한다.
해결 6: 보조열로 데이터 정규화
현장에서는 비가시 공백, BOM, 줄바꿈 등 비정규 문자가 빈 셀처럼 보이게 한다. 보조열을 만들어 정규화 후 차트는 보조열을 참조한다.
' 공백/비가시 문자 정리 + 숫자 강제 변환 =LET(x,TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))),IFERROR(VALUE(x),x))
' 값 열 빈 처리 일관화
=IF(OR(LEN(B2)=0,TRIM(B2)=""),NA(),B2)
해결 7: 피벗차트에서 빈 항목 제어
피벗 기반이면 값 필드 설정과 레이블 필터로 빈 항목을 제거한다.
- 피벗 테이블에서 행 레이블 필터 → “빈 항목 숨기기”를 사용한다.
- 값 표시 형식에서 빈 값은 표시하지 않도록 원본 데이터에서 NA()로 처리한다.
- 피벗차트 옵션에서 숨겨진 항목 표시를 해제한다.
해결 8: 보간 전략을 명시하고 시각화 일치
실무에서는 결측을 0으로 가정, 직선 보간, 최근값 유지 등 규칙을 문서화해야 한다. 각 방법을 보조열로 구현하고 차트는 선택된 규칙만 참조한다.
| 전략 | 수식 예시 | 장점 | 주의사항 |
|---|---|---|---|
| 0 대치 | | 간단하다 | 실제로 0이 아님에도 추세 왜곡이다 |
| 직선 보간 | | 연속성 확보하다 | 결측 구간이 길면 신뢰도 저하이다 |
| 최근값 유지 | | 실적 유지형 지표에 유리하다 | 급변을 숨길 수 있다 |
| 완전 배제 | | 허위 신호 차단하다 | 연속성 단절을 감수한다 |
해결 9: 숨김/필터와 차트의 상호작용 제어
필터로 숨긴 행이 차트에 남는 경우가 있다. 다음을 점검한다.
- 차트 → 데이터 선택 → 숨겨진 및 빈 셀 → “숨겨진 행과 열의 데이터 표시” 체크 상태를 목적에 맞게 설정한다.
- 테이블(Structured Reference)을 차트 소스로 사용하면 필터와 자연스럽게 연동된다.
해결 10: 항목 축 레이블의 공백 정리
범주 레이블 자체에 빈 셀이 끼어 어색한 간격이 생기면 레이블만 별도 보조열로 압축한다.
' 레이블 압축(값이 있는 행만 유지) =LET(lbl,$A$2:$A$1000,val,$B$2:$B$1000,FILTER(lbl,val<>"")) 값은 NA()로 단절 처리하고 레이블은 압축된 배열을 사용하면 간격과 단절 모두 제어 가능하다.
해결 11: 산점도로 해소
시간 간격이 불균등하고 결측이 잦다면 범주 축 기반 꺾은선 대신 산점도를 고려한다. X에 실제 일련번호(또는 날짜 시리얼), Y에 값, 결측은 NA()로 처리한다. 산점도는 X값에 따라 점이 배치되어 공백 구간이 자연스럽게 표현된다.
해결 12: Power Query로 전처리
대용량 데이터에서 다수의 빈 값 유형이 섞여 있으면 Power Query에서 null, 빈 문자열, 공백만 있는 텍스트를 일괄 정리한다.
- null → 오류 또는 0 또는 제거 중 정책 선택
- 공백 트리밍, 비가시 문자 제거
- 형식 강제(숫자/날짜)
- 결측 규칙에 따라 사용자 정의 열 생성
현장 적용 절차(표준 운영 프로토콜)
- 데이터 프로파일링: 빈 값 유형 파악(LEN, ISTEXT, ISNUMBER, COUNTBLANK)하다.
- 축 유형 결정: 텍스트 축 vs 날짜 축을 지표 목적에 맞춰 확정하다.
- 시각화 규칙 문서화: 0 대치, 배제, 보간 등 원칙을 기록하다.
- 보조열 구현: 규칙을 수식으로 고정화하고 원본은 보존하다.
- 차트 설정: 숨겨진 및 빈 셀 정책, 숨김 행 처리, 데이터 원본을 점검하다.
- 검증: 표본 구간을 수작업 그래프와 비교 검증하다.
진단을 돕는 핵심 수식 모음
' 빈 값 유형 판정 =IF(LEN(A2)=0,"BLANK",IF(AND(ISTEXT(A2),LEN(TRIM(A2))=0),"SPACE-ONLY", IF(ISNUMBER(A2),"NUMBER","TEXT")))
' 0과 NULL 구분 집계
=SUMPRODUCT(--(LEN(B2:B1000)=0)) ' NULL 또는 "" 개수
=COUNTIF(B2:B1000,0) ' 숫자 0 개수
' 비정규 문자 제거
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
' 차트용 값 확정
=IF(LEN(B2)=0,NA(),VALUE(B2))
VBA로 차트용 NA() 변환 자동화
분기마다 동일한 정제 작업을 반복한다면 VBA로 보조열을 생성하거나 빈 값을 NA()로 대치할 수 있다.
Sub PrepareSeriesForChart() Dim rng As Range, c As Range Set rng = Range("B2", Cells(Rows.Count, "B").End(xlUp)) For Each c In rng If Len(c.Value) = 0 Or Trim(c.Value) = "" Then c.Value = CVErr(xlErrNA) End If Next c End Sub 주의 : 원본 보존이 필요하면 별도 보조열에 기록하도록 수정한다. 오류 대치를 되돌리려면 “오류 → 빈 셀” 역변환 루틴을 추가한다.
실무 패턴 3가지 빠른 레시피
- 정책: 빈 값은 표시하지 않음 → 값 열에
=IF(LEN(B2)=0,NA(),B2)→ 차트 빈 셀=간격이다. - 정책: 추세선은 끊기지 않음 → 값 열 NA() 유지 → 차트 빈 셀=데이터 요소 연결이다.
- 정책: 존재하는 범주만 표시 → FILTER로 값 있는 행만 압축 → 차트는 압축 배열 참조이다.
자주 하는 실수와 방지책
- 빈 문자열을 빈 셀로 오인한다 → LEN으로 검사하고 NA()로 표준화한다.
- 날짜 축에서 결측을 0으로 대치한다 → 트렌드 왜곡이 크므로 NA() 또는 보간 규칙을 문서화한다.
- 필터 숨김이 차트에 남는다 → 숨겨진 및 빈 셀 설정에서 숨김 표시 옵션을 확인한다.
- 정렬/부분합 후 범위가 어긋난다 → 테이블 참조 사용 또는 정의 이름으로 고정한다.
점검·대응 흐름도 요약
- 증상 파악: 선 끊김, 갑작스런 0, 간격 비정상 중 어떤가 판단하다.
- 데이터 유형 검증: 빈 셀, "", 공백 텍스트, 0, 오류를 구분하다.
- 축 전략 선택: 텍스트 축(존재 레코드만) vs 날짜 축(시간 비례)이다.
- 처리 규칙 확정: 0/보간/배제 중 하나를 문서화하다.
- 수식/보조열 구현 후 차트 연결하다.
- 차트의 빈 셀 옵션과 숨김 행 표시를 마무리 점검하다.
FAQ
빈 셀을 0으로 보이게 하고 싶지 않다. 가장 간단한 방법은 무엇인가?
값 열에서 =IF(LEN(B2)=0,NA(),B2)를 사용하고, 차트에서 빈 셀은 “간격”으로 두거나 “데이터 요소 연결”을 켠다.
날짜 축에서 결측 날짜가 자동으로 채워진다. 막을 수 있나?
축 서식에서 텍스트 축으로 바꾸면 존재하는 날짜만 표시한다. 시간 간격 보존이 필요하면 날짜 축을 유지하고 값은 NA()로 처리한다.
필터로 감춘 행이 차트에 계속 나온다.
차트의 “숨겨진 및 빈 셀 설정”에서 “숨겨진 행과 열의 데이터 표시” 체크를 해제한다. 테이블 기반 범위를 사용하면 동기화가 안정적이다.
빈 문자열과 빈 셀을 한 번에 잡아내려면?
=IF(LEN(B2)=0,NA(),B2)처럼 LEN 기준으로 판별하면 ""까지 포착 가능하다.
보간이 필요한데 엑셀 기본 옵션만으로 충분한가?
“데이터 요소 연결”은 시각적 연결일 뿐 수치 보간을 계산하진 않는다. 계산이 필요하면 보조열에서 FORECAST.LINEAR 등으로 값을 산출한 뒤 차트는 그 보조열을 참조한다.
동적 배열 없이 빈 값 제외 압축 범위를 만들 수 있나?
가능하다. 정의 이름에 INDEX/SMALL/IF 조합을 사용한다. 배열 수식이므로 Ctrl+Shift+Enter가 필요하다.