엑셀 차트 빈 셀 표시 오류 해결: 항목 축 공백 처리 완벽 가이드

이 글의 목적은 엑셀 차트에서 항목 축 또는 계열 데이터에 빈 셀이 포함될 때 발생하는 잘못된 표시, 누락, 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 대치	`=IF(LEN(B2)=0,0,B2)`	간단하다	실제로 0이 아님에도 추세 왜곡이다
직선 보간	`=IF(LEN(B2),B2,FORECAST.LINEAR(ROW(B2),$B$2:$B$100,$ROW($B$2:$B$100)))`	연속성 확보하다	결측 구간이 길면 신뢰도 저하이다
최근값 유지	`=IF(LEN(B2),B2,LOOKUP(2,1/($B$2:B2<>""),$B$2:B2))`	실적 유지형 지표에 유리하다	급변을 숨길 수 있다
완전 배제	`=IF(LEN(B2)=0,NA(),B2)`	허위 신호 차단하다	연속성 단절을 감수한다

해결 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가 필요하다.

OfficeWorkbench

이 블로그 검색

엑셀 파일 호환성 문제 완벽 해결 가이드: 버전·형식·기능 충돌 진단과 복구 방법