엑셀 피벗테이블은 데이터 범위 안의 셀 하나를 클릭하고 [삽입] → [피벗 테이블] → [확인]만 누르면 만들어진다. 여기에 원하는 항목을 행·열·값 칸에 끌어다 놓으면 합계·평균·개수가 자동으로 계산된다. 문제는 그다음부터인데, 원본 데이터가 늘어났을 때 범위를 어떻게 수정하는지, 필드 목록이 갑자기 안 보일 때 어떻게 하는지는 정작 잘 다뤄지지 않는다. 이 글에서는 엑셀 피벗테이블을 처음 만드는 법부터 그룹화, 범위 수정, 실무에서 막히는 지점까지 한 번에 정리했다.
엑셀 피벗테이블이란 정확히 뭔가요?
피벗테이블은 서랍에 뒤섞여 있는 물건을 종류별 칸으로 옮겨 정리하는 것과 비슷하다. 원본 데이터는 손대지 않고, 월별·지역별·담당자별처럼 원하는 기준으로 데이터를 다시 쌓아서 합계나 평균을 보여주는 요약표가 피벗테이블이다. “피벗(Pivot)“이라는 이름도 축을 돌린다는 뜻인데, 행과 열 자리를 자유롭게 바꿔가며 같은 데이터를 여러 각도로 볼 수 있다는 의미다.
수백 줄짜리 판매 데이터에서 “이번 달 담당자별 매출 합계”를 뽑으려면 함수를 여러 번 조합해야 하지만, 피벗테이블은 항목 두세 개를 마우스로 끌어다 놓는 것만으로 같은 결과가 나온다. 그래서 데이터를 자주 요약해야 하는 실무자에게 엑셀에서 가장 먼저 익혀야 할 기능으로 꼽힌다.
엑셀 피벗테이블 만드는 법
| 단계 | 하는 일 | 걸리는 시간 |
|---|---|---|
| 1. 범위 선택 | 데이터 안의 셀 하나 클릭(전체 선택 불필요) | 즉시 |
| 2. 삽입 탭 | [삽입] → [피벗 테이블] 클릭 | 즉시 |
| 3. 위치 확인 | 새 워크시트 또는 기존 시트 선택 후 [확인] | 즉시 |
| 4. 필드 배치 | 우측 필드 목록에서 행·열·값 칸으로 항목 끌어다 놓기 | 1~2분 |
4단계가 전부다. 헷갈리기 쉬운 부분은 1단계인데, 데이터 전체를 드래그해서 선택할 필요 없이 표 안의 셀 하나만 클릭해도 엑셀이 인접한 데이터 범위를 자동으로 인식한다. 단, 데이터 중간에 빈 줄이나 빈 열이 있으면 거기서 범위 인식이 끊기니 원본 데이터는 빈 줄 없이 깔끔하게 정리해두는 게 먼저다.
피벗 테이블 버튼을 누르면 곧바로 확인 창(대화상자)이 하나 뜬다. 범위는 이미 자동으로 채워져 있고 ‘새 워크시트’가 기본 선택돼 있어서, 그대로 확인만 누르면 2단계·3단계가 한 번에 끝난다.
확인을 누르면 우측에 필드 목록 창이 뜨는데, 여기서 체크한 항목을 아래 필터·행·열·값 네 칸 중 원하는 곳으로 끌어다 놓으면 4단계(필드 배치)가 끝난다.
지금까지 예시로 쓴 데이터(9/1 서울 김민지, 9/2 부산 이서준, 9/3 서울 김민지)를 행=담당자, 열=지역, 값=매출액 합계로 설정하면, 왼쪽 시트에는 이런 표가 바로 만들어진다.
그룹화·필터·정렬로 원하는 대로 정리하기
기본 피벗테이블이 완성되면 그다음은 “어떻게 더 보기 좋게 만드느냐”다. 날짜를 하루하루 다 보여주는 대신 월별·분기별로 묶고 싶다면 그룹화를 쓰면 된다. 그룹화하려는 항목(주로 날짜나 숫자)에서 마우스 오른쪽 클릭 → [그룹]을 선택하고 월·분기·연 단위를 고르면 끝이다.
- 필터: 필드 목록의 ‘필터’ 칸에 항목을 넣으면 특정 지역·담당자만 골라서 볼 수 있다
- 정렬: 값 위에서 오른쪽 클릭 → [정렬] → ‘숫자 내림차순 정렬’을 고르면 매출 높은 순으로 자동 정렬된다
- 값 필드 설정: 기본은 합계지만, 값 칸의 항목을 클릭해 평균·개수·최댓값 등으로 바꿀 수 있다
이 세 가지만 조합해도 “이번 분기 지역별 매출 상위 5개”처럼 구체적인 질문에 답하는 표를 몇 번의 클릭으로 만들 수 있다.
원본 데이터가 늘어났을 때, 범위 수정하는 법
피벗테이블을 쓰다 보면 가장 많이 막히는 지점이 바로 이것이다 — 새 데이터를 원본 시트 아래에 추가했는데 피벗테이블에는 반영이 안 되는 경우다. 이건 오류가 아니라, 피벗테이블이 처음 만들 때의 범위를 고정값으로 기억하고 있기 때문이다. 새로고침(F5)을 눌러도 범위 자체가 좁으면 새로 추가한 줄은 잡히지 않는다.
| 방식 | 새 데이터 추가 시 | 해야 할 일 |
|---|---|---|
| 일반 범위(고정) | 반영 안 됨 | 분석 탭 → 데이터 원본 변경 → 범위 다시 지정 |
| 표(Ctrl+T)로 변환 | 자동 반영 | 새로고침(Alt+F5)만 누르면 끝 |
해결 방법은 두 가지다. 당장은 피벗테이블 안을 클릭한 뒤 [분석] 탭 → [데이터 원본 변경]에서 범위를 새로 지정하면 되고, 근본적으로는 원본 데이터를 처음부터 ‘표’로 만들어두는 게 훨씬 편하다. 표로 만드는 방법과 이후 새로고침 자동화는 바로 아래에서 다룬다.
범위 수정 자체를 없애는 법과 자주 쓰는 함수
여기까지는 피벗테이블을 처음 쓰는 사람을 위한 내용이었고, 여기서부터는 매달 같은 데이터를 반복해서 다루는 실무자를 위한 팁이다.
1. 원본을 표(Table)로 만들면 범위 수정이 아예 사라진다. 원본 데이터 안의 셀을 클릭하고 Ctrl+T를 눌러 ‘표’로 변환한 다음 이 표를 기준으로 피벗테이블을 만들면, 표 아래에 새 줄을 입력하는 순간 표 범위가 자동으로 늘어난다. 피벗테이블에서는 새로고침(Alt+F5)만 눌러주면 새 데이터가 바로 반영된다. “범위 수정하는 법”을 매번 검색하지 않아도 되는 가장 확실한 방법이다.
2. GETPIVOTDATA 함수로 피벗테이블 값을 다른 셀에서 참조할 수 있다. 피벗테이블 바깥 셀에서 피벗테이블 안의 특정 값(예: “3월 담당자 A의 매출 합계”)을 가져오고 싶을 때, 셀 주소 대신 항목 이름으로 값을 찾아오는 함수다. 피벗테이블 구조가 바뀌어도(행 순서가 달라져도) 값을 제대로 찾아온다는 게 장점이다. 피벗테이블의 값 셀을 클릭한 채로 수식을 입력하면 자동으로 만들어진다.
3. 새로고침은 단축키로 습관화하는 게 좋다. 피벗테이블 하나만 새로고침할 땐 Alt+F5, 파일 안의 피벗테이블을 전부 한 번에 새로고침할 땐 Ctrl+Alt+F5다. 원본 데이터를 여러 개 관리한다면 매번 마우스로 새로고침 버튼을 찾는 대신 이 단축키를 쓰는 게 훨씬 빠르다.
자주 묻는 질문
Q. 엑셀 피벗테이블이란 정확히 뭔가요?
긴 데이터 목록을 행·열·값 기준으로 자동 재배열해 합계·평균·개수를 요약해주는 표다. 원본 데이터는 그대로 두고 요약본만 따로 만든다.
Q. 피벗테이블 필드 목록이 안 보이면 어떻게 하나요?
피벗테이블 안 아무 셀이나 클릭하면 우측에 필드 목록이 다시 뜬다. 그래도 안 보이면 분석 탭에서 ‘필드 목록’ 버튼을 누르면 된다.
Q. 원본 데이터가 늘어나면 범위를 매번 수정해야 하나요?
고정 범위로 만들었다면 그렇다. 원본을 표(Ctrl+T)로 바꿔서 피벗테이블을 만들면 행이 추가돼도 범위가 자동으로 늘어나 수정할 필요가 없다.
Q. 피벗테이블 새로고침은 어떻게 하나요?
피벗테이블 안을 클릭하고 Alt+F5를 누르면 해당 피벗만, Ctrl+Alt+F5를 누르면 파일 안의 피벗테이블 전체가 한 번에 새로고침된다.
Q. 피벗테이블을 삭제하려면?
피벗테이블 전체를 드래그해 선택한 뒤 Delete를 누르거나, 피벗테이블이 있는 시트 자체를 삭제하면 된다. 원본 데이터는 영향받지 않는다.
Q. 그룹화한 걸 다시 풀거나 요약(부분합)을 없애려면?
그룹화한 항목에서 마우스 오른쪽 클릭 후 ‘그룹 해제’를 누르면 되고, 부분합은 디자인 탭에서 ‘부분합 > 부분합 표시 안 함’을 선택하면 없앨 수 있다.
정리하면 엑셀 피벗테이블은 만드는 것 자체는 4단계로 끝나지만, 진짜 실력 차이는 원본 데이터를 표로 관리해서 범위 수정을 아예 안 해도 되게 만드는 습관에서 나온다. 요약하기 전에 원본에 중복값이 섞여 있지는 않은지 먼저 확인하면 집계 결과가 더 정확해지고, 개별 값을 하나씩 조회하고 싶을 땐 엑셀 브이룩업이 더 알맞다.