이 글의 목적은 엑셀에서 피벗 차트에 필터를 적용한 뒤 데이터가 사라지고 공백 영역만 남는 문제를 재현 가능한 원인별로 분해하고, 현장에서 바로 적용 가능한 설정 변경·데이터 정비·자동화 스크립트로 완전히 해결하도록 돕는 것이다.
문제 정의와 증상 구분
피벗 차트에서 슬라이서나 보고서 필터, 행/열 레이블 필터를 적용했을 때 차트가 비어 보이거나 카테고리 눈금만 남고 데이터 계열이 사라지는 현상이 발생한다. 대표 증상은 다음과 같다.
- 데이터 범위에 값이 존재하지만 차트에는 아무 것도 표시되지 않는다.
- 범례는 남아 있으나 계열이 그려지지 않는다.
- 카테고리 축에 오래된 항목만 남고 실제 표시되어야 할 항목은 사라진다.
- 피벗 테이블에는 값이 보이나, 피벗 차트만 공백이다.
근본 원인 체계도
| 분류 | 세부 원인 | 대표 증상 |
|---|---|---|
| 피벗 캐시 보존 항목 | 필드별 ‘데이터 원본에서 삭제된 항목 보존’이 기본값으로 유지됨 | 과거 항목 눈금만 남고 실제 데이터는 없음 |
| 빈 셀/숨김 데이터 처리 | 차트의 빈 셀 표시 옵션이 ‘틈’으로 설정됨 | 필터 후 계열이 단절되거나 통째로 사라짐 |
| 필터 논리 | 레이블/값 필터 조합으로 결과가 공집합이 됨 | 피벗 테이블은 0행, 차트는 완전 공백 |
| ‘데이터 없는 항목 표시’ | 필드 설정에서 ‘데이터가 없는 항목 표시’가 꺼짐 또는 반대로 켜짐 | 관계형 모델에서 고아 항목이 축만 차지 |
| 데이터 모델/관계 | 일치 키 누락, 일대다 관계 쪽 키 불일치 | Blank/빈 카테고리 발생, 축 공백 확대 |
| 숨김 행/열 | 원본 테이블 일부 열/행 숨김 + 차트가 숨김 데이터 무시 | 필터 후 계열 일부 또는 전체 미표시 |
| 서식/데이터형 | 숫자형 기대 필드가 텍스트형, Null이 공백 문자열 | 합계가 Null로 계산되어 0이 아닌 공백 처리 |
가장 효과적인 기본 해법(빠른 순서)
- 피벗 캐시의 보존 항목 제거를 수행한다.
피벗 테이블 안 아무 셀 선택 → 피벗테이블 옵션 → 데이터 탭 → ‘원본 데이터에서 삭제된 항목 보존’ 항목을 없음으로 설정 → 확인 → 새로 고침을 두 번 실행한다. 한 번은 플래그를 지우고, 두 번째는 축 항목을 재계산한다. - 차트의 빈 셀 처리를 조정한다.
피벗 차트 선택 → 데이터 선택 → 숨김 및 빈 셀 설정 → ‘빈 셀 표시’에서 0으로 표시 또는 선형 차트인 경우 ‘데이터 요소 사이의 접속’ 체크를 검토한다. 원본 행/열이 숨김이라면 ‘숨겨진 행과 열의 데이터 표시’를 체크한다. - 필드 설정의 ‘데이터가 없는 항목 표시’를 점검한다.
축 역할 필드에서 마우스 오른쪽 → 필드 설정 → 레이아웃 및 인쇄 → 데이터가 없는 항목 표시를 상황에 맞게 켠다 또는 끈다. 카테고리를 모두 유지해야 하는 보고서는 켠다, 실제 존재 데이터만 표시는 끈다. - 값 필터/레이블 필터 조합을 단순화한다.
복수 필터가 공집합을 만드는지 확인하고, 먼저 모두 지우기 후 핵심 필터만 다시 적용한다. - 데이터 모델 관계를 검증한다.
데이터 모델 사용 시 관계 보기에서 키가 일치하는지 확인한다. 고아 키가 많다면 루크업 테이블의 모든 키가 사실상 팩트 테이블에 존재하도록 정비한다.
주의 : 피벗 차트의 축 공백은 대부분 ‘보존 항목’과 ‘데이터가 없는 항목 표시’의 상호작용에서 발생한다. 먼저 보존 항목을 제거하고, 그 다음 표시 정책을 결정하는 순서를 지키는 것이 처리 시간을 가장 줄인다.
원인별 상세 해결 절차
1) 원본에서 삭제된 항목이 축에 남아 공백을 만드는 경우
- 피벗 테이블 내부 클릭 → 피벗테이블 옵션 → 데이터 탭.
- ‘원본 데이터에서 삭제된 항목 보존’ → 없음 선택.
- 확인 후, 피벗 테이블을 두 번 연속 새로 고침한다.
이후에도 축이 비면, 동일 통합 문서의 동일 캐시를 공유하는 다른 피벗 테이블이 있는지 확인하고, 모두 같은 설정으로 맞춘 다음 전체 새로 고침을 수행한다.
2) 빈 셀 처리 정책 때문에 계열이 사라지는 경우
- 피벗 차트 선택 → 데이터 선택 → 숨김 및 빈 셀 설정.
- ‘빈 셀 표시’를 0 또는 선형 차트의 경우 데이터 요소 사이 연결로 지정한다.
- 원본 테이블 일부 열/행이 숨김인 경우 숨겨진 행과 열의 데이터 표시를 체크한다.
열지도의 경우 0 처리는 색상 스케일에 영향을 주므로, 0과 Null을 구분해야 한다면 원본에서 명시 0을 채우기보다는 피벗 차트의 옵션으로 해결하는 것이 바람직하다.
3) ‘데이터가 없는 항목 표시’ 설정 상충
보고용 차트에서 카테고리 풀을 고정하고 실적이 없는 구간에 0을 그리고 싶다면 아래처럼 구성한다.
- 축 필드 오른쪽 클릭 → 필드 설정 → 레이아웃 및 인쇄 → 데이터가 없는 항목 표시 체크.
- 피벗 테이블 옵션 → 표시 탭 → 빈 셀에 표시를 0으로 지정한다.
- 차트의 숨김 및 빈 셀 설정에서 ‘빈 셀 표시: 0’로 일치시킨다.
반대로 실제 존재 데이터만 엄격히 보여야 한다면, 위 체크를 해제하고 보존 항목을 없음으로 두어 빈 축을 제거한다.
4) 값/레이블 필터 공집합 예방
다음 패턴은 공집합을 만들기 쉽다.
- 레이블 필터로 특정 텍스트 포함 + 값 필터로 상위 n개 → 현재 기간 데이터에서는 겹치는 항목이 없음.
- 슬라이서 다중 선택과 보고서 필터 결합 → OR와 AND 논리 충돌.
해결은 필터 지우기로 초기화 후 핵심 조건 하나씩 재적용하며 피벗 테이블의 결과 행 수를 확인하는 것이다. 행 수가 0이면 차트가 비는 것은 정상 동작이다.
5) 데이터 모델 관계로 인한 Blank/고아 항목
파워 피벗/데이터 모델을 사용하는 경우 다음을 점검한다.
- 관계 보기에서 팩트 테이블의 외래 키가 조회 테이블 키와 모두 일치하는지 확인한다.
- 누락 키가 많다면 조회 테이블을 기반 축으로 쓰는 대신 팩트 테이블에서 실제 존재 키만 축으로 사용한다.
- 필요 시 조회 테이블의 모든 항목 표시를 켜되, 값 측정치는 0을 반환하도록 DAX를 설계한다.
DAX 예시는 아래를 참고한다.
// 존재하지 않는 조합은 0으로 매출액 안전측정치 := VAR v = SUM('Fact'[Amount]) RETURN IF(ISBLANK(v), 0, v) 6) 데이터형·Null 처리 표준화
합계 대상 열이 텍스트로 저장되어 있거나 공백 문자열이 섞인 경우 합계가 BLANK로 처리되어 차트가 비어 보일 수 있다. 원본 테이블을 다음 규칙으로 정비한다.
- 숫자형 열은 숫자 형식으로 통일한다.
- 빈 문자열("")은 Null로 정규화하거나 0으로 명시한다.
- 날짜 축은 실제 날짜형으로 변환한다.
Power Query M 예시 = Table.TransformColumnTypes( Source, {{"Amount", type number}, {"OrderDate", type date}} ) 현장 체크리스트(바로 적용)
| 점검 항목 | 작업 경로 | 권장 설정 | 영향 |
|---|---|---|---|
| 보존 항목 제거 | 피벗테이블 옵션 → 데이터 | 원본에서 삭제된 항목 보존: 없음 | 오래된 축 항목 제거 |
| 빈 셀 표시 | 차트 → 데이터 선택 → 숨김 및 빈 셀 | 빈 셀: 0 또는 선 연결 | 단절/공백 방지 |
| 숨김 데이터 표시 | 동일 | 숨겨진 행과 열의 데이터 표시: 체크 | 숨김으로 인한 누락 방지 |
| 데이터 없는 항목 표시 | 필드 설정 → 레이아웃 및 인쇄 | 보고 목적에 맞춰 On/Off | 축 고정 또는 실제값만 |
| 필터 간소화 | 필터 메뉴 | 중복 조건 제거 | 공집합 예방 |
| 관계 검증 | 데이터 모델 → 관계 보기 | 키 일치 보장 | Blank 카테고리 제거 |
VBA로 보존 항목 일괄 제거 및 새로 고침 자동화
여러 피벗 테이블과 차트를 운영하는 통합 문서라면 아래 매크로로 한 번에 정리한다.
Option Explicit
Sub Pivot_Chart_BlankFix_All()
Dim pc As PivotCache
Dim pt As PivotTable
Dim ws As Worksheet
Dim co As ChartObject
' 1) 모든 피벗 캐시의 보존 항목 제거
For Each pc In ThisWorkbook.PivotCaches
On Error Resume Next
pc.MissingItemsLimit = xlMissingItemsNone
On Error GoTo 0
Next pc
' 2) 모든 피벗테이블 새로 고침(두 번)
For Each pt In ActiveWorkbook.PivotTables
pt.RefreshTable
Next pt
For Each pt In ActiveWorkbook.PivotTables
pt.RefreshTable
Next pt
' 3) 피벗 차트의 숨김/빈 셀 표시 정책 표준화
For Each ws In ThisWorkbook.Worksheets
For Each co In ws.ChartObjects
With co.Chart
.SetElement (msoElementLegendBottom)
' 빈 셀을 0으로
.DisplayBlanksAs = xlZero
' 숨겨진 행/열 데이터 표시
.PlotVisibleOnly = False
End With
Next co
Next ws
End Sub
주의 : 위 코드는 모든 피벗 캐시에 적용한다. 특정 리포트에서 ‘데이터가 없는 항목 표시’를 의도적으로 사용하는 경우, 해당 시트는 제외 조건을 추가하여 운영한다.
Power Pivot·DAX 환경 권장 패턴
- 측정치는 BLANK를 0으로 강제 변환하여 시각화 공백을 차단한다.
측정치 := VAR v = SUM('Fact'[Amount]) RETURN COALESCE(v, 0) - 날짜 테이블은 연속 날짜로 구성하고, 모든 날짜 표시를 켠 뒤 계절적 공백은 0으로 채운다.
- 다대다 관계나 양방향 필터가 공집합을 만들 수 있으므로, 가능하면 단방향 일대다로 단순화한다.
시나리오별 처방전
| 상황 | 진단 | 해결 |
|---|---|---|
| 슬라이서 다중 선택 후 공백 | 다른 보고서 필터와 공집합 | 필터 전부 지우기 → 핵심 조건만 재적용 |
| 월별 차트에서 몇 달만 비어 보임 | 빈 셀 ‘틈’ 처리 | 빈 셀: 0 또는 선 연결 |
| 이전 분기 항목만 축에 남음 | 보존 항목 누적 | 보존 항목 없음 설정 후 2회 새로 고침 |
| 데이터 모델 사용, Blank 카테고리 | 관계 키 불일치 | 키 정비 또는 축을 팩트 키로 변경 |
| 숨김 열을 활용한 집계 | 차트가 숨김 데이터 무시 | 숨겨진 행과 열의 데이터 표시 체크 |
품질 보증 절차(배포 전 점검)
- 피벗 캐시 보존 항목 없음 설정 적용 여부 확인한다.
- 차트별 빈 셀 처리, 숨김 데이터 표시 정책을 문서화한다.
- 필터 조합 테스트 케이스를 최소 5개 이상 실행한다.
- 데이터 모델 관계 무결성 검사와 키 카디널리티 통계를 확인한다.
- 자동화 매크로로 초기화 → 수동 시나리오 재현 → 결과 캡처로 증빙한다.
자주 하는 실수와 예방 팁
- 피벗 테이블은 정상이지만 차트만 공백인 상황에서 원본을 의심하기 전에 차트의 숨김 및 빈 셀 설정을 먼저 확인한다.
- 보존 항목을 제거하지 않으면 과거 항목이 축을 차지해 스케일링이 왜곡된다.
- 보고서별 정책(0으로 표시 vs 실제 존재 데이터만 표시)을 구분하여 표준 운영 절차에 명시한다.
FAQ
피벗 차트가 아니라 일반 차트에서도 같은 문제가 생기나?
일반 차트도 빈 셀 처리와 숨김 데이터 표시 설정에 의해 공백이 생길 수 있다. 다만 보존 항목 개념은 피벗 캐시에 특화된 것이므로 일반 차트에서는 적용되지 않는다.
0으로 채우면 분석 왜곡이 생기지 않나?
지표 성격에 따라 다르다. 0이 의미 있는 부재값이면 0으로, 의미 없는 결측이면 선 연결 또는 결측 처리한다. 보고 목적에 맞춰 일관 규칙을 정한다.
슬라이서와 보고서 필터를 함께 써도 안전한가?
가능하다. 단, 공집합이 나오지 않도록 조합 설계를 단순화하고, 초기화 버튼을 마련하여 사용자가 쉽게 필터를 해제할 수 있게 한다.
보존 항목을 None으로 바꾸면 성능에 영향이 있나?
대부분의 보고서에서 성능이 오히려 개선된다. 다만 매우 큰 캐시를 자주 재사용하는 모델에서는 재계산 비용이 증가할 수 있다.
데이터 모델에서 Blank 카테고리를 완전히 제거하려면?
키 정합을 우선 해결하고, 측정치에서 BLANK를 0으로 변환하며, 필요 시 축 필드의 ‘데이터가 없는 항목 표시’를 끈다.