월말마다 판매 내역 수천 줄을 열어 놓고 지점별 합계를 손으로 더하던 시절이 있었어요. 필터 걸고, 복사하고, 계산기 두드리고, 다시 정리하다 보면 반나절이 훌쩍 지나가더군요. 그러다 동료가 "그거 피벗테이블 세 번만 클릭하면 나와요"라고 해서 반신반의하며 눌러 봤는데, 정말 몇 초 만에 표가 뚝딱 완성됐습니다. 이름이 거창해서 어려워 보이지만 원리는 의외로 단순해요. 이 글에서는 피벗테이블이 뭔지부터, 만드는 순서, 값 요약 방식 바꾸기, 그리고 초보가 꼭 만나는 실수까지 차근차근 풀어 보겠습니다.
피벗테이블은 "많은 데이터를 몇 번 클릭으로 요약"하는 도구입니다
피벗테이블은 수백, 수천 줄짜리 원본 데이터를 드래그 몇 번만으로 원하는 기준으로 묶어 요약해 주는 기능이에요. 예를 들어 "지점별 매출 합계", "월별 주문 건수", "상품별 평균 단가" 같은 집계를 함수 한 줄 없이 만들 수 있습니다. 사람이 하면 필터하고 더하고를 반복해야 하지만, 피벗테이블은 그 일을 한 번에 처리해요.
핵심은 "무엇을 기준으로(행), 어떻게 나눠서(열), 무슨 값을(값) 볼 것인가"를 정하는 겁니다. 이 세 가지만 이해하면 나머지는 마우스로 끌어다 놓는 일이 전부예요.
따라 하기: 삽입 > 피벗 테이블
먼저 원본 데이터를 준비합니다. 첫 행에 제목(머리글)이 있고, 그 아래로 데이터가 빈 줄 없이 이어지는 표면 충분해요. 그다음 순서대로 진행합니다.
- 표 안의 아무 셀이나 하나 클릭합니다. (범위를 미리 통째로 드래그해도 됩니다.)
- 상단 메뉴에서 삽입 > 피벗 테이블을 누릅니다.
- 대화상자가 뜨면 표/범위가 내 데이터 전체로 잡혔는지 확인합니다.
- 피벗 테이블을 놓을 위치를 고릅니다. 잘 모르겠으면 기본값인 새 워크시트를 그대로 두는 편이 깔끔해요.
- 확인을 누르면 빈 피벗테이블 틀과 오른쪽에 필드 목록 창이 나타납니다.
여기까지가 뼈대 만들기입니다. 아직 아무 값도 안 보이는 게 정상이에요. 이제 그 안을 채워 볼게요.
행·열·값 영역에 필드 끌어다 놓기
오른쪽 필드 목록을 보면 위쪽에는 내 표의 제목들(예: 지점, 날짜, 상품, 금액)이 나열돼 있고, 아래쪽에는 필터 / 열 / 행 / 값이라는 네 개의 영역 상자가 있습니다. 원하는 제목을 마우스로 잡아 이 상자로 끌어다 놓으면 표가 즉시 만들어져요.
- 행 영역: 세로로 나열할 기준입니다. 여기에 "지점"을 넣으면 지점 이름이 위에서 아래로 줄줄이 나옵니다.
- 값 영역: 실제로 계산할 숫자입니다. 여기에 "금액"을 넣으면 지점별 금액이 자동으로 더해져요.
- 열 영역: 가로로 나눌 기준입니다. "월"을 넣으면 지점(행) × 월(열) 교차표가 됩니다.
- 필터 영역: 표 위쪽에 걸어 두고 특정 조건만 골라 보는 스위치예요. 필수는 아닙니다.
처음엔 행에 한 개, 값에 한 개만 넣어 보세요. "지점별 매출 합계"처럼 가장 단순한 표부터 만들면 원리가 금방 눈에 들어옵니다. 익숙해지면 열과 필터를 하나씩 추가해 나가면 돼요.
값 요약 방식 바꾸기: 합계 → 개수 → 평균
값 영역에 숫자 필드를 넣으면 엑셀은 기본적으로 합계로 계산합니다. 그런데 "몇 건인지", "평균이 얼마인지"가 궁금할 때도 있잖아요. 이럴 땐 요약 방식만 바꾸면 됩니다.
- 값 영역에 있는 필드(예: "합계 : 금액")를 마우스 오른쪽 버튼으로 클릭합니다.
- 값 필드 설정을 선택합니다.
- 목록에서 합계 / 개수 / 평균 / 최대 / 최소 중 원하는 것을 고르고 확인을 누릅니다.
참고로 문자(텍스트) 필드를 값 영역에 넣으면 더할 수가 없으니 자동으로 개수가 잡힙니다. "주문이 몇 건인지" 셀 때 유용해요. 같은 필드를 값 영역에 두 번 넣어서 하나는 합계, 하나는 평균으로 나란히 보는 것도 가능합니다.
원본이 바뀌면 '새로 고침'을 잊지 마세요
피벗테이블에서 가장 많이 하는 착각이 바로 이 부분이에요. 원본 데이터의 숫자를 고치거나 줄을 추가해도, 피벗테이블은 자동으로 바뀌지 않습니다. 반드시 갱신을 해 줘야 해요.
피벗테이블 안을 클릭하면 상단에 나타나는 피벗 테이블 분석 탭에서 새로 고침 버튼을 눌러도 됩니다. 다만 주의할 점이 있어요. 원본에 행이 새로 늘어난 경우에는 단순 새로 고침만으로는 범위가 안 따라올 수 있습니다. 이럴 땐 같은 탭의 데이터 원본 변경에서 범위를 다시 잡아 주면 됩니다.
초보가 자주 하는 실수와 팁
피벗테이블이 이상하게 나오거나 아예 안 만들어질 때는 대개 원본 데이터에 문제가 있어요. 아래 표를 참고해 주세요.
| 흔한 실수 | 왜 문제가 되나 |
|---|---|
| 제목(머리글) 없는 표 | 필드 이름이 없어 영역에 끌어 놓을 항목이 뒤죽박죽 됩니다. |
| 중간에 빈 행·빈 열 | 엑셀이 표가 거기서 끝난 줄 알고 범위를 반쪽만 잡습니다. |
| 병합된 셀 | 한 칸에 값이 하나여야 하는데 그룹이 깨져 오류가 납니다. |
| 숫자가 텍스트로 저장됨 | 합계가 안 되고 개수만 잡혀 값이 이상하게 보입니다. |
가장 확실한 팁은 원본을 미리 표(Ctrl + T)로 만들어 두는 겁니다. 표로 지정해 두면 나중에 데이터를 추가해도 범위가 자동으로 늘어나서, 새로 고침만 눌러도 새 데이터가 반영돼요. 또 무엇부터 봐야 할지 막막하다면 삽입 > 추천 피벗 테이블을 눌러 엑셀이 제안하는 요약안을 골라 시작하는 것도 좋은 방법입니다.
메뉴 이름은 버전에 따라 조금씩 달라요. 엑셀 2016 이상과 마이크로소프트 365에서는 피벗 테이블 분석 탭이라고 부르고, 구버전에서는 옵션 탭으로 표기되기도 합니다. 위치와 기능은 거의 같으니 이름이 조금 달라도 당황하지 마세요.
- 만들기: 표 안 클릭 → 삽입 > 피벗 테이블 → 범위·위치 확인 → 확인.
- 필드 목록에서 행·열·값 영역으로 제목을 끌어다 놓으면 표가 완성.
- 값 요약은 우클릭 → 값 필드 설정에서 합계·개수·평균으로 변경.
- 원본이 바뀌면 반드시 새로 고침(Alt+F5) — 자동 갱신 안 됨.
- 원본은 제목 있고, 빈 행·병합 셀 없이 깔끔하게. 표(Ctrl+T)로 만들면 편함.