엑셀 데이터 정리 방법｜복잡한 자료를 깔끔하게 만드는 기능 8가지

다른 부서에서 받은 자료나 여러 사람이 함께 작성한 엑셀 파일을 열어보면 같은 항목이 중복되거나 한 셀에 여러 정보가 섞여 있는 경우가 있습니다. 사람마다 입력한 표현이 달라서 같은 항목인데도 서로 다른 데이터로 인식되기도 합니다.

이런 자료는 바로 계산하거나 집계하기보다 먼저 데이터의 형태를 일정하게 정리해야 합니다. 엑셀에는 복잡한 수식을 사용하지 않아도 중복 자료를 삭제하고, 한 셀의 내용을 여러 열로 나누고, 입력할 값을 제한할 수 있는 기능이 있습니다.

이번 글에서는 지난번에 소개한 정렬, 필터, SUM, IF, COUNTIF, 조건부 서식과 겹치지 않는 엑셀 데이터 정리 기능 8가지를 알아보겠습니다.

메뉴 위치와 단축키는 Windows용 Microsoft 365 엑셀을 기준으로 설명합니다. 사용하는 버전에 따라 메뉴 이름이나 위치가 조금 다를 수 있습니다.

1. 중복된 항목을 한꺼번에 제거하기

여러 파일의 자료를 하나로 합치면 같은 거래처나 직원, 업무 내역이 중복으로 들어갈 수 있습니다. 자료가 많을 때는 눈으로 확인하기보다 ‘중복된 항목 제거’ 기능을 사용하는 것이 편리합니다.

먼저 중복 여부를 확인할 데이터 범위를 선택합니다. 메뉴를 실행하면 어떤 열을 기준으로 중복을 판단할지 선택할 수 있습니다.

예를 들어 거래처 목록에 다음 열이 있다고 가정해 보겠습니다.

거래처명
사업자등록번호
담당자
연락처

거래처명이 같더라도 서로 다른 회사일 수 있으므로 거래처명만 기준으로 삭제하는 것은 위험합니다. 비교적 고유한 정보인 사업자등록번호를 기준으로 선택하는 것이 더 정확합니다.

여러 열을 동시에 선택하면 선택한 열의 값이 모두 동일한 행만 중복으로 판단합니다.

중복 제거 기능은 찾은 행을 표시하는 것이 아니라 실제로 삭제합니다. 실행하기 전에 원본 시트를 복사하거나 파일을 별도로 저장해 두는 것이 좋습니다. 결과가 잘못되었다면 저장하기 전에 Ctrl + Z를 눌러 실행을 취소할 수 있습니다.

메뉴 위치: 데이터 → 데이터 도구 → 중복된 항목 제거
실행 취소: Ctrl + Z

2. 한 셀의 내용을 여러 열로 나누기

한 셀에 부서명, 이름, 직급이 함께 입력되어 있다면 각각의 정보를 기준으로 집계하거나 관리하기 어렵습니다.

예를 들어 A열에 다음과 같이 입력되어 있다고 가정해 보겠습니다.

재무팀 김민수 과장

항목 사이가 공백으로 구분되어 있다면 ‘텍스트 나누기’ 기능을 이용해 재무팀, 김민수, 과장을 각각 다른 열로 나눌 수 있습니다.

텍스트 나누기에는 두 가지 방식이 있습니다.

구분 기호로 분리: 쉼표, 공백, 탭, 하이픈처럼 일정한 기호를 기준으로 나눕니다.
너비가 일정함: 화면에서 구분선을 직접 지정하여 일정한 위치를 기준으로 나눕니다.

자료가 서울,강남구,역삼동처럼 입력되어 있다면 쉼표를 구분 기호로 선택합니다. 2026-08-28에서 연도와 월, 일을 나누려면 하이픈을 기준으로 사용할 수 있습니다.

나눈 데이터는 원래 데이터의 오른쪽 열에 표시됩니다. 오른쪽에 기존 자료가 있다면 덮어쓸 수 있으므로 먼저 빈 열을 충분히 만들어 두세요.

메뉴 위치: 데이터 → 데이터 도구 → 텍스트 나누기

3. 빠른 채우기로 일정한 형태 만들기

빠른 채우기는 사용자가 입력한 예시를 바탕으로 일정한 규칙을 인식하여 나머지 셀을 자동으로 채우는 기능입니다.

예를 들어 A열에 이름, B열에 부서명이 입력되어 있을 때 C열에 다음과 같이 하나의 예시를 입력할 수 있습니다.

김민수_재무팀

그 아래 셀에서 빠른 채우기를 실행하면 엑셀이 입력 형태를 인식하여 나머지 이름과 부서명도 같은 방식으로 결합합니다.

다음과 같은 작업에 활용할 수 있습니다.

성과 이름 분리하기
이름과 부서명 결합하기
전화번호 사이에 하이픈 넣기
이메일 주소에서 아이디만 추출하기
코드의 앞부분이나 뒷부분만 가져오기
영문 이름의 대문자와 소문자 형태 맞추기

빠른 채우기가 원하는 규칙을 인식하지 못한다면 예시를 두세 개 입력한 후 다시 실행해 보세요. 원본 데이터의 형식이 일정하지 않으면 잘못된 결과가 나올 수 있으므로 채운 후에는 일부 항목을 반드시 확인해야 합니다.

빠른 채우기로 만든 결과는 수식이 아니라 고정된 값입니다. 원본 데이터가 바뀌어도 결과가 자동으로 변경되지 않는다는 점에 주의하세요.

메뉴 위치: 데이터 → 데이터 도구 → 빠른 채우기
단축키: Ctrl + E

4. 드롭다운 목록으로 입력값 통일하기

여러 사람이 같은 파일을 작성하면 같은 의미의 내용을 서로 다르게 입력할 수 있습니다.

예를 들어 업무 상태를 다음처럼 입력할 수 있습니다.

진행 중
진행중
진행
처리 중

사람은 같은 의미로 이해할 수 있지만 엑셀에서는 서로 다른 값으로 인식됩니다. 이런 차이는 집계나 검색 결과에 영향을 줄 수 있습니다.

드롭다운 목록을 만들면 사용자가 정해진 항목 중 하나만 선택하도록 할 수 있습니다.

먼저 빈 공간이나 별도의 시트에 사용할 항목을 입력합니다.

접수
진행 중
검토 중
완료

드롭다운을 적용할 셀을 선택한 뒤 데이터 유효성 검사에서 제한 대상을 ‘목록’으로 설정합니다. 원본 항목이 입력된 셀 범위를 지정하면 선택한 셀에 화살표가 나타납니다.

항목 수가 적다면 원본 칸에 접수,진행 중,검토 중,완료처럼 쉼표로 구분해 직접 입력할 수도 있습니다.

메뉴 위치: 데이터 → 데이터 도구 → 데이터 유효성 검사 → 설정 → 제한 대상 ‘목록’
목록 열기: 드롭다운이 적용된 셀에서 Alt + ↓

5. 데이터 유효성 검사로 잘못된 입력 막기

데이터 유효성 검사는 드롭다운 목록을 만드는 것뿐 아니라 셀에 입력할 수 있는 숫자, 날짜, 글자 수를 제한할 때도 사용할 수 있습니다.

예를 들어 다음과 같이 설정할 수 있습니다.

수량은 1 이상의 정수만 입력
비율은 0%에서 100% 사이만 입력
날짜는 해당 연도 안에서만 입력
사번은 정해진 글자 수만 입력
금액에는 음수를 입력하지 못하도록 제한

잘못된 값을 입력했을 때 표시할 안내 문구도 설정할 수 있습니다.

예를 들어 집행일자 입력란에는 다음과 같은 오류 메시지를 표시할 수 있습니다.

“2026년 1월 1일부터 12월 31일까지의 날짜를 입력해 주세요.”

오류 스타일을 ‘중지’로 설정하면 기준에 맞지 않는 값의 입력을 차단할 수 있습니다. ‘경고’ 또는 ‘정보’를 선택하면 안내 메시지를 보여주면서 입력은 허용할 수 있습니다.

이미 입력된 자료에 유효성 검사를 적용해도 기존 오류가 자동으로 수정되지는 않습니다. 적용하기 전에 기존 데이터가 기준에 맞는지 함께 확인하는 것이 좋습니다.

메뉴 위치: 데이터 → 데이터 도구 → 데이터 유효성 검사
주요 설정: 설정 → 제한 대상 / 설명 메시지 / 오류 메시지

6. 일반 범위를 표로 변환하기

엑셀에서 ‘표’는 단순히 셀에 테두리를 넣는 기능과 다릅니다. 일반 데이터 범위를 표로 변환하면 새로운 자료가 추가될 때 표의 범위와 서식이 자동으로 확장됩니다.

표로 변환하면 다음 기능을 활용할 수 있습니다.

머리글에 필터 버튼 자동 생성
행이 추가될 때 서식 자동 적용
계산식 자동 확장
줄무늬 행으로 자료 구분
표 이름을 이용한 수식 작성
요약 행 추가

표로 만들 데이터 안의 셀 하나를 선택한 후 Ctrl + T를 누릅니다. 범위가 올바르게 선택되었는지 확인하고 첫 번째 행이 제목이라면 ‘머리글 포함’을 선택합니다.

다만 중간에 완전히 빈 행이나 열이 있으면 엑셀이 데이터 범위를 정확하게 인식하지 못할 수 있습니다. 표로 변환하기 전에 빈 행과 열을 확인하세요.

표의 디자인이 필요하지 않다면 표 안의 셀을 선택한 뒤 ‘표 디자인’에서 일반 범위로 다시 변환할 수 있습니다.

메뉴 위치: 삽입 → 표
단축키: Ctrl + T
일반 범위로 변경: 표 디자인 → 도구 → 범위로 변환

7. 빈 셀만 한꺼번에 선택해 정리하기

자료가 많은 시트에서 빈 셀을 하나씩 찾기 어려울 때는 ‘이동 옵션’을 이용해 빈 셀만 선택할 수 있습니다.

먼저 확인할 데이터 범위를 선택한 뒤 이동 창을 엽니다. ‘옵션’을 선택하고 ‘빈 셀’을 지정하면 선택한 범위 안에서 값이 없는 셀만 한꺼번에 선택됩니다.

선택된 빈 셀에 같은 값을 넣고 싶다면 다음 순서로 진행합니다.

빈 셀이 선택된 상태에서 입력할 내용을 작성합니다.
Enter만 누르지 않고 Ctrl + Enter를 누릅니다.
선택된 모든 빈 셀에 같은 값이 입력됩니다.

예를 들어 빈 셀을 미제출, 해당 없음, 0과 같은 값으로 채울 수 있습니다.

빈 셀에 무조건 0이나 특정 문구를 넣어서는 안 됩니다. 빈칸이 ‘자료 없음’을 의미하는지, ‘아직 입력하지 않음’을 의미하는지 먼저 확인해야 합니다.

수식이 빈 문자열 ""을 반환하는 셀은 겉으로 비어 보여도 완전한 빈 셀로 선택되지 않을 수 있습니다.

메뉴 위치: 홈 → 편집 → 찾기 및 선택 → 이동 옵션 → 빈 셀
이동 창 열기: Ctrl + G 또는 F5
선택한 셀에 같은 값 입력: 내용 입력 후 Ctrl + Enter

8. 행과 열을 바꾸어 붙여넣기

자료를 작성한 후 행과 열의 방향이 반대였다는 것을 알게 되는 경우가 있습니다.

예를 들어 월별 실적이 가로 방향으로 길게 작성되어 있지만, 다른 시스템에 입력하려면 세로 방향이 필요할 수 있습니다. 이때 자료를 다시 입력하지 않고 ‘행/열 바꿈’ 기능을 사용할 수 있습니다.

먼저 변경할 범위를 복사한 뒤 결과를 넣을 빈 셀을 선택합니다. 붙여넣기 옵션에서 ‘행/열 바꿈’을 선택하면 가로 방향의 데이터가 세로 방향으로, 세로 방향의 데이터는 가로 방향으로 변경됩니다.

잘라내기보다 복사하기를 사용해야 하며, 결과가 들어갈 공간에 기존 값이 없는지 확인하세요.

행과 열을 바꾸어 붙여넣은 결과는 원본과 연결되지 않은 복사본입니다. 원본 값이 바뀌어도 결과는 자동으로 변경되지 않습니다. 원본과 연결된 결과가 필요하다면 TRANSPOSE 함수를 사용할 수 있지만, 단순한 일회성 정리에는 붙여넣기 기능이 더 간단합니다.

메뉴 위치: 홈 → 클립보드 → 붙여넣기 ▼ → 행/열 바꿈
복사 단축키: Ctrl + C

엑셀 자료를 정리할 때 확인할 사항

기능을 실행하기 전후로 다음 항목을 확인하면 데이터가 잘못 삭제되거나 변경되는 실수를 줄일 수 있습니다.

원본 파일 또는 원본 시트를 별도로 보관했는가
데이터의 첫 번째 행이 머리글인지 확인했는가
중복을 판단할 기준 열을 정확하게 선택했는가
텍스트를 나눌 오른쪽 공간이 비어 있는가
빠른 채우기 결과를 일부라도 원본과 비교했는가
빈 셀이 어떤 의미인지 확인했는가
숫자와 날짜가 문자 형식으로 저장되지 않았는가
정리 후 전체 행 수가 예상과 맞는가

특히 중복 제거와 텍스트 나누기는 원본 데이터에 직접 영향을 줄 수 있습니다. 처음 사용하는 기능이라면 복사한 시트에서 먼저 실행해 보는 것이 안전합니다.

일정한 입력 기준이 자료 정리 시간을 줄입니다

엑셀 데이터 정리는 자료가 모두 입력된 뒤에만 하는 작업이 아닙니다.

드롭다운 목록과 데이터 유효성 검사를 미리 설정하면 잘못된 값이 입력되는 것을 줄일 수 있습니다. 일반 범위를 표로 변환하면 새로운 행을 추가할 때마다 서식과 계산식을 다시 복사하는 작업도 줄어듭니다.

이미 작성된 자료에는 중복 제거, 텍스트 나누기, 빠른 채우기와 빈 셀 선택 기능을 활용할 수 있습니다. 다만 자동으로 처리된 결과가 항상 정확한 것은 아니므로 실행 전 원본을 보관하고 결과를 확인하는 과정이 필요합니다.

처음부터 8가지 기능을 모두 사용할 필요는 없습니다. 반복해서 정리하는 자료가 있다면 드롭다운 목록과 표 만들기부터 적용해 보세요. 입력 기준이 일정해질수록 이후의 검색과 집계도 편해집니다.