엑셀 피벗 테이블과 슬라이서로 실시간 인터랙티브 대시보드 만드는 법

엑셀은 데이터 분석에 있어 가장 강력한 도구 중 하나입니다. 특히 피벗 테이블(PivotTable)과 슬라이서(Slicer)를 조합하면 클릭 한 번으로 데이터가 실시간으로 필터링되고 자동 업데이트되는 동적 대시보드를 손쉽게 구축할 수 있습니다.

이 글에서는 피벗 테이블과 슬라이서를 활용해 엑셀에서 인터랙티브 대시보드를 만드는 과정을 단계별로 살펴보겠습니다.

1단계: 데이터 준비하기

대시보드를 만들기 전에 원본 데이터를 올바르게 구조화하는 것이 가장 중요합니다. 데이터 형태에 따라 이후 모든 단계의 품질이 좌우되기 때문입니다.

데이터 세트 요건:

• 표 형식이어야 합니다(병합된 셀이 없어야 함).
• 각 열에는 고유하고 명확한 머리글이 있어야 합니다.
• 빈 행이나 빈 열이 없어야 합니다.
• 모든 열에 적절한 서식을 지정합니다:
  • Date(날짜) 열 → 간단한 날짜 형식
  • Unit Price(단가), Total Sales(총 매출) → 통화 형식
  • Units Sold(판매 수량) → 소수점 없는 숫자 형식
• 엑셀 표로 변환합니다:
  • 전체 데이터를 선택하거나 Ctrl+A를 누릅니다.
  • Ctrl+T를 누르거나 삽입 탭 >> 표를 선택합니다.
  • '머리글 포함' 옵션에 체크합니다.
  • 확인을 클릭합니다.

• 표 이름을 지정합니다:
  • 표 안의 아무 셀이나 선택한 상태에서
  • 테이블 디자인 탭 >> 테이블 이름을 SalesData로 변경합니다.

2단계: 첫 번째 피벗 테이블 만들기

지역별·제품 카테고리별 매출을 보여주는 피벗 테이블을 만들어 보겠습니다.

• SalesData 표 안의 아무 곳이나 클릭합니다.
• 삽입 탭 >> 피벗 테이블을 선택합니다.
• 표 범위 또는 이름이 올바른지 확인한 뒤 새 워크시트를 선택합니다.
• 확인을 클릭합니다.

• 피벗 테이블 필드 창에서:
  • Region(지역) 필드를 행 영역으로 끌어다 놓습니다.
  • Product Category(제품 카테고리) 필드를 열 영역으로 끌어다 놓습니다.
  • Total Sales(총 매출) 필드를 값 영역으로 끌어다 놓습니다.
• 이제 첫 번째 피벗 테이블이 지역별로 각 제품 카테고리가 창출하는 매출을 보여줍니다.

3단계: 슬라이서와 타임라인 추가하기

이번에는 슬라이서와 타임라인을 추가해 데이터를 대화형으로 필터링할 수 있도록 만들겠습니다.

슬라이서 삽입:

• 피벗 테이블의 아무 셀이나 선택합니다.
• 피벗 테이블 분석 탭 >> 슬라이서 삽입을 선택합니다.
• Sales Rep(영업 담당자)와 Date(날짜)를 체크합니다.
• 확인을 클릭합니다.

타임라인 삽입:

• 피벗 테이블의 아무 셀이나 선택합니다.
• 피벗 테이블 분석 탭 >> 타임라인 삽입을 선택합니다.
• Date(날짜)를 선택합니다.
• 확인을 클릭합니다.

• 슬라이서와 타임라인을 피벗 테이블 옆에 배치합니다.

영업 담당자를 클릭해 보면서 피벗 테이블이 자동으로 업데이트되는지 직접 확인해 보세요!

4단계: 추가 피벗 테이블 만들기

대시보드를 더 풍성하게 만들기 위해 두 개의 피벗 테이블을 추가하겠습니다.

피벗 테이블 2: 월별 매출 추이

• SalesData 표 안의 아무 곳이나 클릭합니다.
• 삽입 탭 >> 피벗 테이블을 선택합니다.
• 표 범위가 올바른지 확인한 뒤 새 워크시트를 선택합니다.
• 확인을 클릭합니다.

• 피벗 테이블 필드 창에서:
  • Date(날짜)를 행 영역으로 드래그합니다. 그러면 다음과 같이 표시됩니다.
    • Months(Date) — 월
    • Days(Date) — 일
    • Date — 날짜
  • Total Sales(총 매출)를 값 영역으로 드래그합니다.

피벗 테이블 3: 판매 수량 기준 인기 제품

• 같은 워크시트에 피벗 테이블을 하나 더 만듭니다.
• 피벗 테이블 필드 창에서:
  • Product Name(제품명)을 행 영역으로 드래그합니다.
  • Units Sold(판매 수량)를 값 영역으로 드래그합니다.

5단계: 여러 피벗 테이블을 같은 슬라이서에 연결하기

기존 슬라이서를 모든 피벗 테이블에 연결해 보겠습니다:

• Sales Rep 슬라이서를 마우스 오른쪽 버튼으로 클릭합니다.
• 보고서 연결(Report Connections)을 선택합니다.

• 목록에서 모든 피벗 테이블에 체크합니다.
• 확인을 클릭합니다.

• Date 슬라이서에도 동일하게 반복합니다.

이제 특정 영업 담당자나 날짜 범위를 클릭하면 세 개의 피벗 테이블이 모두 동시에 업데이트됩니다!

6단계: 피벗 차트 만들기

피벗 테이블을 시각적인 차트로 변환해 보겠습니다.

지역/카테고리 피벗 테이블용:

• 피벗 테이블 안의 아무 곳이나 클릭합니다.
• 피벗 테이블 분석 탭 >> 피벗 차트를 선택합니다.
• 묶은 세로 막대형(Clustered Column) 차트를 선택합니다.
• 확인을 클릭합니다.
• 차트 서식을 지정합니다:
  • 차트 제목을 추가합니다.
  • 축을 사용자 지정합니다.
  • 불필요한 요소(예: 눈금선, 필요 없는 범례)를 제거합니다.

월별 매출 추이용:

• 피벗 테이블 안의 아무 곳이나 클릭합니다.
• 피벗 테이블 분석 탭 >> 피벗 차트를 선택합니다.
• 표식이 있는 꺾은선형(Line with Markers) 차트를 선택합니다.
• 확인을 클릭합니다.

인기 제품용:

• 피벗 테이블 안의 아무 곳이나 클릭합니다.
• 피벗 테이블 분석 탭 >> 피벗 차트를 선택합니다.
• 가로 막대형(Bar) 차트를 선택합니다.
• 확인을 클릭합니다.

피벗 테이블에 발생하는 모든 변화는 피벗 차트에 자동으로 반영됩니다.

7단계: 대시보드 레이아웃 디자인하기

이제 모든 요소를 하나의 통합된 대시보드로 정리하겠습니다.

• 대시보드 워크시트 이름을 Sales Dashboard로 변경합니다.
• 차트를 배치합니다:
  • 지역/카테고리 차트 → 왼쪽
  • 월별 추이 차트 → 가운데
  • 인기 제품 차트 → 오른쪽
• 슬라이서는 접근성이 좋도록 상단에 배치합니다.
• 텍스트 상자를 이용해 제목을 추가합니다: Sales Performance Dashboard.

8단계: 서식으로 완성도 높이기

대시보드를 시각적으로 더 돋보이게 만들어 보겠습니다.

• 지역/카테고리 피벗 테이블에 조건부 서식을 적용합니다:
  • 데이터 셀을 선택합니다.
  • 홈 탭 >> 조건부 서식 >> 색조에서 녹색-노란색-빨간색을 선택합니다.
• 월별 추이 차트 서식을 지정합니다:
  • 차트를 클릭합니다.
  • 차트 디자인 >> 스타일 5(또는 선호하는 스타일)를 선택합니다.
  • 차트 제목을 추가합니다: Monthly Sales Trend.
• 인기 제품 차트 서식을 지정합니다:
  • 데이터 레이블을 추가합니다.
  • 차트 디자인 >> 차트 요소 추가 >> 데이터 레이블을 선택합니다.
  • 내림차순으로 정렬합니다.
  • 차트 제목을 추가합니다: Top Products.

인터랙티브 기능 확인하기:

문제 해결 팁

• 날짜가 제대로 그룹화되지 않는다면, 원본 데이터에서 해당 열이 '날짜' 형식으로 지정되어 있는지 확인하세요.
• 슬라이서가 일부 피벗 테이블만 업데이트한다면, 보고서 연결 설정을 다시 점검하세요.
• 대용량 데이터 세트에서 성능 문제가 발생하면 피벗 테이블 옵션의 '레이아웃 업데이트 지연' 기능을 활용해 보세요.

심화 활용 기법

마진률을 계산하는 계산 필드 만들기:

• 피벗 테이블을 클릭합니다.
• 피벗 테이블 분석 탭 >> 필드, 항목 및 집합 >> 계산 필드를 선택합니다.
• 이름을 Profit으로 입력하고 아래 수식을 넣습니다.
• 추가와 확인을 클릭합니다.

선택 항목에 따라 바뀌는 동적 제목 추가하기:

• GETPIVOTDATA() 함수를 사용해 현재 필터링된 총액을 불러옵니다.
• 아래 수식을 입력합니다.

="Sales Dashboard: "&TEXT(GETPIVOTDATA("Total Sales",$A$3),"$#,##0")

워크북 다운로드

결론

위 단계를 따라 하면 사용자가 매출 데이터를 손쉽게 분석할 수 있는 전문적이고 인터랙티브한 대시보드를 완성할 수 있습니다. 피벗 테이블로 데이터를 요약·분석하고, 피벗 차트로 시각화하며, 슬라이서와 타임라인으로 데이터를 즉석에서 필터링할 수 있습니다. 새로운 데이터가 추가되어도 엑셀 대시보드는 손쉽게 갱신되므로, 매출 보고서, KPI 관리, 재고 분석 등 다양한 업무에 활용하기에 최적입니다.