엑셀 파일 만들 때 지켜야 할 데이터 구조 7가지

피벗테이블이 안 만들어지고, 필터가 중간에서 끊기고, VLOOKUP이 엉뚱한 값을 가져옵니다. 매번 다른 문제처럼 보이지만 원인은 대개 하나입니다. 표가 계산에 맞는 모양이 아니라는 것입니다.

엑셀의 기능들은 표가 특정한 형태일 것이라는 전제 위에 만들어져 있습니다. 그 전제를 깨면 어떤 함수를 써도 우회에 우회를 거듭하게 됩니다. 이 글은 그 전제 일곱 가지를 정리한 것입니다.

1. 한 셀에는 한 값만 넣는다

서울시 강남구 / 010-1234-5678처럼 한 칸에 두 정보를 넣으면, 나중에 지역별로 집계하거나 연락처만 뽑을 때 반드시 다시 쪼개야 합니다.

쪼개는 건 합치는 것보다 훨씬 어렵습니다. 합치는 건 =A2&" "&B2 한 줄이면 되지만, 쪼개려면 구분자가 일정해야 하고 예외를 일일이 처리해야 합니다.

의심스러우면 나눠서 저장하세요. 붙여 보여주는 건 언제든 할 수 있습니다.

2. 병합 셀을 쓰지 않는다

병합 셀은 정렬, 필터, 피벗테이블을 전부 막습니다. 병합된 범위가 있으면 피벗테이블은 아예 만들어지지 않고, 정렬은 오류 메시지를 냅니다.

가운데 정렬이 목적이라면 병합 대신 선택 영역의 가운데로를 쓰세요. Ctrl + 1 → 맞춤 탭 → 텍스트 맞춤의 가로에서 선택합니다. 보기에는 병합과 똑같지만 셀은 그대로 남습니다.

3. 머리글은 한 줄, 중복 없이

표 맨 위에 제목이나 작성일을 넣으면 첫 행이 머리글로 인식되지 않습니다. 머리글은 데이터 바로 위 한 줄이어야 합니다.

제목을 꼭 넣어야 한다면 별도 시트로 빼거나, 최소한 표와 빈 행 하나 이상 띄워두세요.

머리글이 두 줄로 나뉜 경우(1분기 아래 1월 2월 3월)도 문제입니다. 이럴 땐 1분기_1월처럼 한 줄로 합치세요.

빈 머리글과 중복된 머리글도 피해야 합니다. 피벗테이블은 빈 머리글이 있으면 만들어지지 않고, 중복 머리글은 자동으로 이름이 바뀌어 혼란을 일으킵니다.

4. 빈 행이나 빈 열로 구역을 나누지 않는다

보기 좋으라고 부서 사이에 빈 행을 넣는 경우가 많은데, 엑셀은 빈 행을 표의 끝으로 인식합니다. Ctrl + A로 표 전체를 잡으려 해도 빈 행 앞까지만 선택됩니다.

구역을 구분해야 한다면 구분 열을 하나 추가하세요. 빈 행 대신 부서 열에 값을 채우는 방식입니다. 이러면 필터로 원하는 부서만 볼 수 있어서 오히려 편합니다.

5. 한 열에는 한 가지 형식만 넣는다

같은 열에 숫자와 텍스트가 섞이면 SUM이 일부를 건너뛰고, VLOOKUP이 값을 못 찾습니다.

특히 흔한 경우가 수량 열에 '미정'이나 '-'를 적는 것입니다. 값이 없다면 빈칸으로 두거나 0을 넣으세요. 상태를 표시해야 한다면 별도 열을 만드는 게 맞습니다.

날짜 열에 2026-01-04와 1월 4일이 섞이는 것도 같은 문제입니다.

6. 소계와 합계를 데이터 중간에 넣지 않는다

부서별 소계를 데이터 중간에 끼워 넣으면, 피벗테이블이나 SUM이 소계까지 데이터로 세어 이중 계산합니다. 금액이 두 배로 나오는데 원인을 못 찾는 경우가 여기서 나옵니다.

원본 시트에는 순수한 거래 기록만 쌓고, 소계와 합계는 피벗테이블에 맡기세요. 피벗은 소계를 자동으로 만들어주고, 원본을 건드리지 않습니다.

7. 원본 시트와 보고 시트를 분리한다

원본 시트에는 색칠, 테두리, 병합, 큰 글씨를 넣지 마세요. 데이터를 담는 곳과 보여주는 곳은 목적이 다릅니다.

원본 시트 — 행이 계속 쌓이는 곳. 서식 없음
보고 시트 — 피벗테이블이나 수식으로 원본을 참조. 서식은 여기서

이렇게 나눠두면 보고서 양식이 바뀌어도 원본은 그대로고, 원본이 늘어나도 보고서는 새로고침만 하면 됩니다.

자주 막히는 지점 세 가지

① 이미 병합된 파일을 받았을 때

병합을 풀면 빈 셀이 생깁니다. 이걸 위쪽 값으로 채우는 방법이 있습니다.

범위 선택 후 홈 → 병합하고 가운데 맞춤을 눌러 병합 해제
범위를 선택한 채 F5 → 옵션 → 빈 셀 → 확인
빈 셀이 선택된 상태에서 =를 입력하고 위쪽 방향키를 누릅니다
Ctrl + Enter

모든 빈 셀이 바로 위 값으로 한 번에 채워집니다. 마지막에 열 전체를 복사해서 값만 붙여넣기로 수식을 없애주세요.

② 월이 열로 퍼져 있는 표(크로스탭)

품목 | 1월 | 2월 | 3월 형태는 보고서로는 좋지만 데이터로는 다루기 어렵습니다. 월별 필터도 안 되고, 4월이 추가되면 열을 늘려야 합니다.

파워쿼리로 세로 형태로 바꿀 수 있습니다. 데이터 → 테이블/범위에서 → 월 열들을 모두 선택 → 변환 탭 → 열 피벗 해제.

품목 | 월 | 금액 형태로 바뀝니다. 이 상태에서 피벗테이블을 만들면 원하는 어떤 형태로도 다시 펼칠 수 있습니다.

③ 표(Ctrl+T)로 만들어야 하는 이유

위 규칙을 지킨 표는 Ctrl + T로 표로 변환해두세요. 행이 늘어나면 수식·피벗·차트 범위가 자동으로 따라오고, 조건부 서식도 새 행에 적용됩니다.

표로 만들어지지 않는다면 위 일곱 가지 중 어딘가를 어긴 것입니다. 표 변환은 구조 점검 도구로도 쓸 수 있습니다.

자주 묻는 질문

Q. 상사가 병합된 양식을 요구하면요?

원본과 제출본을 나누세요. 원본은 규칙대로 쌓고, 제출용 시트에서 원본을 참조해 원하는 모양으로 만듭니다. 매달 제출본을 손으로 다시 만드는 것보다 훨씬 빠릅니다.

Q. 이미 만들어진 파일을 전부 고쳐야 하나요?

당장 문제가 없다면 그대로 두셔도 됩니다. 다만 그 파일을 기반으로 새 작업을 시작하거나, 데이터를 계속 쌓아야 한다면 지금 고치는 편이 저렴합니다. 나중에 고치는 비용이 훨씬 큽니다.

Q. 행이 몇 개까지 괜찮나요?

엑셀 한 시트는 약 104만 행까지 지원하지만, 실무에서는 수십만 행부터 반응이 느려집니다. 그 규모라면 데이터베이스나 파워쿼리 데이터 모델을 고려할 시점입니다.

Q. 구글 시트도 같은 규칙인가요?

동일합니다. 오히려 구글 시트의 QUERY 함수는 표 구조에 더 엄격해서, 이 규칙을 지키지 않으면 아예 작동하지 않습니다.

정리

한 셀 한 값, 병합 금지, 머리글 한 줄
빈 행 대신 구분 열로 나누기
소계는 원본이 아니라 피벗에 맡기기
원본과 보고 시트를 분리
Ctrl + T가 되면 구조가 정상이라는 신호

관련 글

거래처별 매출 집계, SUMIF 말고 피벗테이블로 하는 법
남이 만든 엑셀 파일 받았을 때, 5분 점검 루틴
여러 시트 한 장으로 합치기, 복사 붙여넣기 말고 3가지 방법
엑셀 숫자가 텍스트로 저장됐을 때, 원인과 해결법 4가지