이 글의 목적은 엑셀에서 사용자 정의 목록을 생성·관리·배포하는 전 과정을 체계적으로 설명하고, 자동 채우기와 정렬 순서, 데이터 유효성 검사, VBA 자동화 등 실무에서 바로 쓰는 활용법과 문제 해결 절차를 제공하는 것이다.
1. 사용자 정의 목록의 개념과 작동 원리
사용자 정의 목록(Custom List)이란 반복적으로 사용하는 값들의 고정된 순서를 엑셀에 등록하여 자동 채우기(필 핸들)와 정렬 순서에 그대로 반영하도록 하는 기능이다. 예를 들어 “상·중·하”, “우선순위 높음·보통·낮음”, “서울·경기·인천 …” 같은 고정 시퀀스를 등록해두면 채우기 핸들로 연속 입력을 빠르게 수행하고, 정렬에서도 사전식이 아니라 지정한 순서대로 정렬이 가능해진다.
핵심 특성은 다음과 같다.
- 입력 보조 기능이다. 셀 값 자체를 강제하거나 검증하지 않는다.
- 적용 범위는 사용자 계정의 엑셀 환경 설정에 국한된다. 기본적으로 파일에 저장되지 않는다.
- 자동 채우기와 정렬 대화상자에서만 직접적으로 사용된다.
- VBA를 통해 목록 생성·조회·삭제가 가능하다.
주의 : 사용자 정의 목록은 데이터 유효성 검사와 다르다. 유효성 검사는 허용 가능한 값을 제한하지만, 사용자 정의 목록은 채우기·정렬의 편의 기능에 가깝다.
2. 사용자 정의 목록 만들기(Windows)
- 파일 > 옵션 > 고급으로 이동한다.
- 고급 옵션 하단의 일반 섹션에서 사용자 지정 목록 편집을 클릭한다.
- 목록 항목 상자에 항목을 한 줄에 하나씩 입력한다.
예: 상↵
중↵
하 - 추가를 눌러 저장한다.
- 확인으로 모든 대화상자를 닫는다.
3. 범위에서 가져와 목록 만들기(Windows)
- 시트에 항목을 연속 셀로 입력한다(세로 또는 가로 배열 모두 가능하다).
- 파일 > 옵션 > 고급 > 사용자 지정 목록 편집을 연다.
- 가져오기 목록에서 가져오기 버튼을 누른다. 선택 범위가 목록으로 등록된다.
팁 : 현업에서 사용하는 지역·제품군·우선순위·단계(예: Draft, Review, Approved)를 업무 표준 시퀀스로 확정해 범위에서 한 번에 가져오면 팀 전체 입력 품질이 올라간다.
4. Mac에서 사용자 정의 목록 만들기
- Excel > 설정으로 이동한다.
- 고급 또는 사용자 지정 목록 항목을 연다(버전에 따라 명칭이 다르다).
- 목록 항목에 값을 줄바꿈으로 구분해 입력하거나, 시트 범위를 선택하여 가져온다.
- 추가로 저장 후 대화상자를 닫는다.
5. 자동 채우기에서의 활용
등록한 항목 중 하나를 셀에 입력한 후 셀 우하단 핸들을 끌면 목록 순서대로 값이 확장된다.
- 순환 동작: 목록 끝에 도달하면 다시 첫 항목으로 순환한다.
- 부분 시작: 목록 중간 항목에서 시작해도 이후가 연속되어 채워진다.
- 가로·세로 모두 지원한다.
주의 : 채우기 핸들이 보이지 않거나 작동하지 않으면 파일 > 옵션 > 고급 > 편집 옵션에서 채우기 핸들과 셀 끌어서 놓기 사용을 활성화해야 한다.
6. 사용자 정의 목록을 이용한 정렬
- 정렬할 영역을 선택한다.
- 데이터 > 정렬을 연다.
- 정렬 기준 열을 선택하고 순서 드롭다운에서 사용자 지정 목록을 선택한다.
- 적용할 목록을 지정한 뒤 확인을 누른다.
이렇게 하면 “상·중·하”처럼 비알파벳·비수치적 순서가 그대로 정렬 순서가 된다.
7. 사용자 정의 목록과 데이터 유효성 검사의 결합
사용자 정의 목록은 유효성 검사 소스로 직접 참조되지 않는다. 그러나 다음 절차로 유사 효과를 만든다.
- 빈 영역에 사용자 정의 목록과 동일한 항목 테이블을 만든다.
- 이 범위를 이름 정의로 고정한다(예:
rngPriority). - 입력 셀에 데이터 유효성 검사 > 목록을 설정하고 원본에
=rngPriority를 지정한다.
이 방식은 드롭다운에 동일 항목을 제공하고, 채우기·정렬에서는 사용자 정의 목록 순서를 유지할 수 있다.
8. 팀 배포 전략
사용자 정의 목록은 기본적으로 개인 환경에 저장되므로 팀 표준화를 위해 다음 중 하나를 선택한다.
- 공유 통합문서에 범위 보관 후 각자 가져오기로 등록한다.
- VBA 초기화 매크로로 첫 실행 시 목록을 자동 생성한다.
- 템플릿(xltx, xlsm)에 초기화 코드를 포함한다.
주의 : PC 교체, 프로필 초기화, 버전 교체 시 사용자 정의 목록이 사라질 수 있다. 템플릿 또는 초기화 매크로로 재생성 절차를 준비해두는 것이 안전하다.
9. 업무 표준 예시 목록
| 업무 영역 | 예시 목록 | 활용 |
|---|---|---|
| 우선순위 | 높음, 보통, 낮음 | 정렬로 티켓 보드 우선순위 정렬 |
| 진행상태 | 요청, 검토, 진행, 완료, 보류 | 자동 채우기와 정렬 동시 활용 |
| 품질등급 | 상, 중, 하 | 보고서 표준 순서 유지 |
| 지역 | 서울, 경기, 인천, 부산, 대구, 광주, 대전, 울산 | 지역 리포트 일관 정렬 |
| 요일 | 월, 화, 수, 목, 금, 토, 일 | 캘린더 시트 자동 채우기 |
10. 우수한 목록 설계 원칙
- 불변 순서: 분기마다 변하는 값은 목록이 아닌 데이터 테이블로 관리한다.
- 간결성: 항목은 3~9개 사이가 가장 작업 효율이 높다.
- 명확성: 유사 의미 중복을 피한다(예: “대기” vs “보류”).
- 국문·영문 표기 분리: 혼합 표기는 정렬·검색 모두에서 혼선을 준다.
11. 자동 채우기 품질을 좌우하는 옵션
| 옵션 | 위치 | 권장 설정 | 영향 |
|---|---|---|---|
| 채우기 핸들과 끌어서 놓기 사용 | 파일 > 옵션 > 고급 > 편집 옵션 | 사용 | 핸들이 표시되고 자동 채우기 가능 |
| 셀에 입력할 때 자동으로 채우기 사용 | 파일 > 옵션 > 고급 | 상황별 | 패턴 예측 채움과의 상호작용 |
주의 : 패턴 예측 기반의 자동 채움과 사용자 정의 목록 채움이 혼합될 수 있다. 원하는 결과가 아닐 때 채우기 옵션 스마트태그에서 값만 채우기 또는 서식 없이 채우기를 선택한다.
12. 사용자 정의 목록과 정렬의 실전 패턴
12.1 단일 기준 정렬
- 상태 열을 기준으로 정렬한다.
- 순서에서 사용자 지정 목록을 선택하고 “요청, 검토, 진행, 완료, 보류”를 지정한다.
12.2 다중 기준 정렬
- 첫 기준: 우선순위, 순서: “높음, 보통, 낮음”.
- 두 번째 기준: 상태, 순서: “진행, 검토, 보류, 완료”.
- 세 번째 기준: 마감일, 순서: 오름차순.
이 조합으로 긴급·진행 중 업무가 상단으로 정렬된다.
13. 수식과의 연계 아이디어
사용자 정의 목록의 순서를 모사하려면 별도 참조 테이블을 만들고 MATCH로 순위를 도출한다.
-- 순서 표(예: A2:A4 = {높음;보통;낮음}) =IFERROR(MATCH([@우선순위], $A$2:$A$4, 0), 1E9) 이 값으로 보조 열을 만든 뒤 일반 정렬을 수행하면 사용자 정의 목록과 동일한 효과를 낼 수 있다. 파일 공유 시 상대방 PC에 목록이 없어도 일관 정렬이 보장된다.
14. VBA로 목록 자동 생성·관리
팀 템플릿에서 초기화 매크로로 사용자 정의 목록을 배포할 수 있다.
'모듈: CustomListInit.bas Option Explicit
Sub EnsureCustomLists()
Dim lists As Variant
lists = Array( _
Split("높음,보통,낮음", ","), _
Split("요청,검토,진행,완료,보류", ","), _
Split("월,화,수,목,금,토,일", ",") _
)
Dim i As Long, exists As Boolean
For i = LBound(lists) To UBound(lists)
exists = CustomListExists(lists(i))
If Not exists Then
Application.AddCustomList ListArray:=lists(i)
End If
Next i
End Sub
Function CustomListExists(arr As Variant) As Boolean
Dim n As Long, j As Long
n = Application.CustomListCount
For j = 1 To n
If CompareList(Application.GetCustomListContents(j), arr) Then
CustomListExists = True
Exit Function
End If
Next j
End Function
Function CompareList(a As Variant, b As Variant) As Boolean
Dim i As Long
If UBound(a) - LBound(a) <> UBound(b) - LBound(b) Then Exit Function
For i = LBound(a) To UBound(a)
If CStr(a(i)) <> CStr(b(i)) Then Exit Function
Next i
CompareList = True
End Function
Sub RemoveCustomListByFirstItem(firstItem As String)
Dim n As Long, j As Long
n = Application.CustomListCount
For j = n To 1 Step -1
Dim c As Variant
c = Application.GetCustomListContents(j)
If UBound(c) >= 0 Then
If CStr(c(0)) = firstItem Then
Application.DeleteCustomList ListNum:=j
End If
End If
Next j
End Sub
팁 :
Application.AddCustomList는 배열뿐 아니라 Source 인수로 시트 범위를 직접 받아 등록할 수 있다. 예: Application.AddCustomList Source:=Range("A2:A8") 형태로 사용한다.15. 목록 백업·이전 전략
- VBA 스크립트 백업: 위 초기화 매크로를 표준으로 삼아 템플릿에 포함한다.
- 범위 기반 백업: 모든 목록을 한 시트에 표로 정리해두고 필요할 때 가져오기 한다.
- 문서화: 표준 목록의 소유자, 변경 절차, 승인 흐름을 문서로 남긴다.
16. 품질 보증 체크리스트
| 항목 | 체크 방법 | 합격 기준 |
|---|---|---|
| 핸들 채우기 활성화 | 옵션 확인 후 테스트 채우기 | 목록 순서대로 채워짐 |
| 정렬 순서 일치 | 정렬 대화상자에서 목록 지정 | 지정 순서와 동일 |
| 파일 공유 호환 | 받는 측 PC에서 정렬 테스트 | VBA/보조열로 일관성 확보 |
| 변경 관리 | 목록 변경 이력 기록 | 버전·날짜·승인자 명시 |
17. 자주 발생하는 문제와 해결
17.1 핸들 채우기가 일반 복사만 될 때
- 옵션에서 채우기 핸들과 셀 끌어서 놓기 사용이 꺼져 있지 않은지 확인한다.
- 스마트태그로 채우기 옵션이 복사로 고정되어 있지 않은지 확인한다.
17.2 정렬 대화상자에 사용자 지정 목록이 보이지 않을 때
- 정렬 대상 열의 데이터 형식이 병합·공백 혼합으로 인식 장애를 일으키지 않는지 확인한다.
- 정렬 대화상자의 순서 드롭다운에서 사용자 지정 목록을 열어 목록을 재선택한다.
17.3 팀원이 같은 결과를 재현하지 못할 때
- 상대 PC에 동일 사용자 정의 목록이 없는 경우가 많다. VBA 초기화 또는 보조열
MATCH방식으로 파일 자체에 순서 정의를 포함한다.
17.4 목록 항목 오타·중복
- 오타가 있으면 자동 채우기 중간에서 예기치 않은 분기 발생이 가능하다. 목록을 편집해 정정한 후 테스트 시리즈를 재생성한다.
18. 설계 대안: 사용자 정의 목록 vs 테이블 기반 제어
| 기능 | 사용자 정의 목록 | 테이블·수식·검증 |
|---|---|---|
| 입력 편의 | 최상 | 중간 |
| 정렬 일관성(공유) | PC 의존 | 파일에 내장 |
| 검증·제한 | 불가 | 가능 |
| 배포 용이성 | 개인 설정 필요 | 파일만 공유 |
| 유지보수 | 간단 | 변경 시 수식·검증 동반 수정 |
입력 속도는 사용자 정의 목록이 우수하고, 공유 일관성은 테이블·수식 기반이 우수하다. 혼합 설계를 권장한다.
19. 현장 베스트 프랙티스
- 표준 목록 관리자를 1명 지정하여 변경 요청을 수집·승인한다.
- 모든 목록을 한 시트에 모아 버전·날짜를 기록한다.
- 템플릿 열기 이벤트에서 EnsureCustomLists를 호출해 목록을 자동 보정한다.
- 정렬 규칙을 피벗테이블·차트 생성 절차서에 명시한다.
20. 실무 예시: 프로젝트 칸반 시트 초기화
- 시트 Config에 표준 목록 표를 만든다(우선순위, 상태, 요일).
- 개발 탭의 Visual Basic에서 초기화 모듈을 추가하고 위 VBA를 붙여 넣는다.
- Workbook_Open 이벤트에서
EnsureCustomLists를 호출한다. - 작업 시트에서는 우선순위·상태 열 정렬 시 사용자 지정 목록을 선택한다.
Private Sub Workbook_Open() On Error Resume Next EnsureCustomLists End Sub 21. 테스트 시나리오
- “보통”을 입력한 셀을 아래로 6칸 끌어 “보통→낮음→높음→보통…” 순서를 확인한다.
- 100행 데이터에 우선순위·상태를 임의 배정하고 사용자 지정 목록 정렬을 적용해 상단 우선순위 집합이 기대대로 정렬되는지 확인한다.
- 상대 PC에서 파일을 열어 보조열 방식 정렬이 동일하게 재현되는지 검증한다.
FAQ
사용자 정의 목록을 파일과 함께 공유할 수 있나?
기본적으로 환경 설정이므로 파일과 함께 이동하지 않는다. VBA 초기화 또는 보조열·테이블 방식으로 대체한다.
목록 길이 제한이 있나?
실무에서 사용하는 수십 항목 수준은 무리가 없다. 너무 긴 목록은 자동 채우기 효율이 떨어지므로 테이블·데이터 유효성 검사를 권장한다.
대소문자·띄어쓰기가 다른 값은 같은 항목으로 취급되나?
입력 값은 문자열 일치가 기준이다. 표준 표기법을 문서화해 오타·변형 표기를 줄여야 한다.
피벗테이블에서도 사용자 지정 목록 정렬이 되나?
피벗 항목 정렬 시 옵션에서 사용자 지정 목록 순서를 사용할 수 있다. 피벗 필드의 정렬 대화상자에서 해당 설정을 확인한다.
목록을 수정하면 과거 시트에 자동 반영되나?
아니다. 기존 값은 변하지 않는다. 정렬·채우기 동작만 변경 후부터 새 규칙을 따른다.