다른 부서에서 받은 파일 열자마자 한숨부터 나온다면

여러 사람이 나눠서 입력한 파일을 취합할 때마다 겪는 일입니다. 어떤 셀은 병합되어 있고, 어떤 열은 빈 행이 중간중간 섞여 있고, 숫자인 줄 알았던 값이 알고 보니 텍스트로 저장되어 있고요. 이런 파일은 함수를 아무리 잘 써도 제대로 작동하지 않습니다. 오늘은 본격적으로 데이터를 다루기 전에, 지저분하게 입력된 데이터를 정리하는 방법들을 정리해보겠습니다.

빈 행 한 번에 삭제하기

데이터 중간중간에 빈 행이 섞여 있으면 정렬이나 필터, 피벗테이블이 제대로 작동하지 않습니다. 하나씩 찾아서 지우는 대신, 한 번에 골라내는 방법이 있습니다.

1. 데이터 범위를 선택합니다.
2. Ctrl+G(이동) → [옵션] → [빈 셀]을 선택합니다.
3. 확인을 누르면 빈 셀들만 자동으로 선택됩니다.
4. 선택된 상태에서 마우스 오른쪽 클릭 → [삭제] → [행 전체]를 선택합니다.

이렇게 하면 데이터가 있는 행은 그대로 두고, 완전히 빈 행만 골라서 한 번에 지울 수 있습니다.

병합된 셀 해제하고 빈 셀 채우기

보고서용으로 예쁘게 병합해둔 파일을 데이터 원본으로 쓰려고 하면 문제가 생깁니다. 병합된 셀은 맨 위(또는 맨 왼쪽) 셀에만 값이 들어있고 나머지는 비어 있는 것으로 처리되기 때문에, 필터나 피벗테이블에서 제대로 인식되지 않습니다.

해제 순서

1. 병합된 범위를 선택합니다.
2. [홈] 탭 → [병합하고 가운데 맞춤] 버튼을 다시 눌러서 해제합니다.

이렇게 하면 병합이 풀리면서, 원래 병합되어 있던 칸들 중 첫 번째 셀에만 값이 남고 나머지는 빈 칸이 됩니다.

빈 칸을 바로 위 데이터로 채우기

병합 해제 후 생긴 빈 칸을 하나씩 채우는 대신, 이렇게 처리합니다.

1. 방금 해제한 범위를 다시 선택합니다.
2. Ctrl+G(이동) → [옵션] → [빈 셀]을 선택합니다.
3. 수식 입력줄에 = 를 입력하고, 바로 위 셀을 클릭한 다음 Ctrl+Enter를 누릅니다.

Ctrl+Enter로 입력하면 선택된 모든 빈 셀에 "바로 위 셀의 값"이라는 수식이 동시에 채워집니다. 이후 이 값들을 복사해서 [값으로 붙여넣기]를 하면, 수식이 아닌 실제 값으로 고정됩니다.

바꾸기 기능으로 숨은 문자 제거하기

눈에는 안 보이지만 셀 안에 섞여 있는 문자들 때문에 VLOOKUP이나 COUNTIF가 이상하게 작동하는 경우가 있습니다. 대표적으로 줄바꿈 문자나 특수한 공백이 있습니다.

실무 예시: 줄바꿈 문자 제거하기

다른 시스템에서 다운로드한 데이터에 눈에 안 보이는 줄바꿈이 섞여 있을 때가 있습니다. 이럴 땐 찾기 및 바꾸기를 열고, 찾을 내용 칸에서 Ctrl+J를 누릅니다. 화면에는 아무것도 안 보이지만 줄바꿈 문자가 입력된 것이고, 바꿀 내용은 비워둔 채로 [모두 바꾸기]를 누르면 숨어 있던 줄바꿈이 전부 제거됩니다.

앞서 다룬 TRIM 함수로도 일반적인 공백은 제거되지만, 줄바꿈처럼 TRIM으로 안 지워지는 특수 문자는 바꾸기 기능을 써야 하는 경우가 많습니다.

텍스트 나누기로 한 셀에 뭉쳐 있는 데이터 나누기

"홍길동,영업팀,과장"처럼 여러 정보가 한 셀에 쉼표나 공백으로 뭉쳐서 들어있는 경우, 이전 글에서 다룬 FIND와 LEFT를 조합해서 나눌 수도 있지만, 데이터 양이 많고 형식이 일정하다면 텍스트 나누기 기능이 훨씬 빠릅니다.

사용 순서

1. 나눌 데이터가 있는 열을 선택합니다.
2. [데이터] 탭 → [텍스트 나누기]를 클릭합니다.
3. '구분 기호로 분리됨'을 선택하고 다음으로 넘어갑니다.
4. 쉼표, 공백, 탭 등 실제 구분 기호를 체크합니다.
5. 미리보기에서 나눠지는 모습을 확인하고 마침을 누릅니다.

수식 없이 몇 번의 클릭만으로 한 열에 뭉쳐 있던 데이터가 여러 열로 깔끔하게 나뉩니다.

유효성 검사로 애초에 잘못된 입력을 막기

지금까지는 이미 잘못 입력된 데이터를 정리하는 방법이었다면, 유효성 검사는 애초에 잘못된 값이 입력되지 않도록 예방하는 기능입니다. 여러 사람이 같이 입력하는 파일이라면, 데이터가 들어오는 시점부터 형식을 통일해두는 게 나중에 정리하는 수고를 줄여줍니다.

실무 예시: 정해진 목록 안에서만 선택하게 하기

부서명을 입력하는 열에 "영업팀"을 어떤 사람은 "영업 팀", 어떤 사람은 "영업1팀"처럼 제각각 입력하면 나중에 집계할 때 전부 다른 항목으로 인식됩니다. 이걸 막으려면 이렇게 합니다.

1. 부서명을 입력할 범위를 선택합니다.
2. [데이터] 탭 → [데이터 유효성 검사]를 클릭합니다.
3. 제한 대상에서 '목록'을 선택하고, 원본에 허용할 부서명들을 쉼표로 구분해서 입력하거나 미리 만들어둔 목록 범위를 지정합니다.

이렇게 설정하면 해당 셀을 클릭했을 때 드롭다운 목록이 나타나서, 정해진 값 중에서만 선택할 수 있게 됩니다. 직접 타이핑해서 오타가 생기는 것 자체를 원천적으로 막을 수 있습니다.

자주 하는 실수 정리

• 병합 해제 후 빈 칸 채우기를 건너뛰고 바로 함수를 쓰는 경우: 병합만 풀고 빈 칸을 안 채우면 여전히 절반은 빈 셀로 남아 있어서 집계가 틀어집니다.
• Ctrl+J로 줄바꿈을 찾을 때 화면에 아무것도 안 보인다고 잘못 입력했다고 착각하는 경우: 원래 눈에 안 보이는 문자를 지정하는 것이므로 정상입니다.
• 유효성 검사 목록을 걸어놓고 기존에 이미 잘못 입력된 데이터는 그대로 두는 경우: 유효성 검사는 앞으로 새로 입력되는 값만 제한합니다. 기존 데이터는 별도로 정리해야 합니다.
• 텍스트 나누기를 하기 전에 원본을 따로 백업해두지 않는 경우: 텍스트 나누기는 원본 열을 직접 덮어쓰기 때문에, 옆에 빈 열을 미리 확보해두거나 원본을 복사해둔 뒤 실행하는 게 안전합니다.

마치며

지저분한 데이터를 정리하는 작업은 겉으로는 티가 잘 안 나지만, 사실 함수나 피벗테이블보다 먼저 되어 있어야 하는 기본기입니다. 아무리 좋은 함수를 알아도 데이터 자체가 엉망이면 원하는 결과를 얻을 수 없으니, 새로운 데이터를 받았다면 본격적으로 분석하기 전에 오늘 다룬 방법들로 먼저 점검해보시길 추천드립니다.