엑셀에서 거래처명, 상품 분류, 주문 상태, 담당자 이름 등을 입력하다 보면 같은 항목을 반복해서 작성해야 하는 경우가 많습니다. 이때 드롭다운 목록을 만들면 셀을 클릭해 미리 정해둔 항목 중 하나를 선택할 수 있어 입력 시간을 줄이고 오타도 예방할 수 있습니다.
예를 들어 주문 상태를 관리할 때 ‘배송중’, ‘배송 중’, ‘배송진행’처럼 서로 다르게 입력하면 정확한 집계가 어려워집니다. 드롭다운 목록을 사용하면 모든 직원이 동일한 항목을 선택하도록 관리할 수 있습니다.
이번 글에서는 챗GPT를 활용해 엑셀 드롭다운 목록을 만드는 방법부터 목록 수정, 자동 확장, 오류 해결까지 실제 업무 예제로 알아보겠습니다.
1. 엑셀 드롭다운 목록이란?
드롭다운 목록은 엑셀의 데이터 유효성 검사 기능을 이용해 셀에서 선택할 수 있는 값을 미리 지정하는 기능입니다.
셀을 선택하면 오른쪽에 작은 화살표가 나타나고, 화살표를 클릭하면 등록된 항목을 선택할 수 있습니다.
| 활용 업무 | 드롭다운 선택 항목 |
|---|---|
| 주문 관리 | 주문접수, 상품준비, 배송중, 배송완료 |
| 재고 관리 | 정상, 부족, 품절 |
| 고객 관리 | 신규, 상담중, 계약완료 |
| 업무 관리 | 예정, 진행중, 완료, 보류 |
| 상품 분류 | 의류, 가전, 식품, 생활용품 |
특히 여러 사람이 같은 엑셀 파일을 사용하거나 매일 반복적으로 데이터를 입력하는 업무에서 효과적입니다.
2. 챗GPT로 드롭다운 목록 항목 만들기
드롭다운 목록을 만들기 전에 어떤 항목이 필요한지 정해야 합니다.
이 과정에서 챗GPT를 활용하면 업무에 적합한 목록을 빠르게 구성할 수 있습니다.
챗GPT에 다음과 같이 입력합니다.
“온라인 쇼핑몰 주문 관리에 사용할 엑셀 드롭다운 목록을 만들어줘. 주문 상태를 5단계로 구분하고, 엑셀에 바로 입력할 수 있도록 정리해줘.”
챗GPT가 제안하는 목록의 예시는 다음과 같습니다.
| 번호 | 주문 상태 | 의미 |
|---|---|---|
| 1 | 주문접수 | 고객 주문이 접수된 상태 |
| 2 | 결제완료 | 결제가 확인된 상태 |
| 3 | 상품준비 | 상품 포장 및 출고 준비 |
| 4 | 배송중 | 택배 배송이 진행 중 |
| 5 | 배송완료 | 고객에게 배송 완료 |
이렇게 정리한 항목을 엑셀에 등록하면 됩니다.
실제 업무에서는 취소, 반품, 교환 등의 항목도 필요에 따라 추가할 수 있습니다.
3. 엑셀에서 드롭다운 목록 만드는 방법
가장 간단한 방법은 데이터 유효성 검사에서 항목을 직접 입력하는 것입니다.
먼저 다음과 같은 주문 관리표를 준비합니다.
| A열 주문번호 | B열 상품명 | C열 주문상태 |
|---|---|---|
| 1001 | 여성 블라우스 | |
| 1002 | 남성 셔츠 | |
| 1003 | 청바지 | |
| 1004 | 운동화 |
이제 C열에 드롭다운 목록을 설정하겠습니다.
- 엑셀에서 C2부터 C100까지 선택합니다.
- 상단 메뉴에서 ‘데이터’를 클릭합니다.
- ‘데이터 유효성 검사’를 선택합니다.
- 설정 탭의 ‘제한 대상’에서 ‘목록’을 선택합니다.
- ‘원본’ 입력창에 다음 항목을 입력합니다.
주문접수,결제완료,상품준비,배송중,배송완료- ‘셀 안에 드롭다운 표시’가 선택되어 있는지 확인합니다.
- ‘확인’을 클릭합니다.
이제 C2부터 C100까지 각 셀을 선택하면 주문 상태를 선택할 수 있습니다.
일부 엑셀 환경에서는 목록 구분자로 쉼표 대신 세미콜론을 사용해야 할 수 있습니다. 항목이 하나로 표시된다면 시스템의 목록 구분 기호를 확인합니다.
4. 다른 셀에 목록을 만들어 연결하는 방법
항목이 많거나 나중에 수정할 가능성이 있다면 별도의 셀에 목록을 만들어 연결하는 것이 편리합니다.
예를 들어 E열에 다음과 같이 입력합니다.
| E열 | 주문 상태 목록 |
|---|---|
| E1 | 주문상태 |
| E2 | 주문접수 |
| E3 | 결제완료 |
| E4 | 상품준비 |
| E5 | 배송중 |
| E6 | 배송완료 |
설정 방법은 다음과 같습니다.
- C2 범위를 선택합니다.
- 데이터 → 데이터 유효성 검사로 이동합니다.
- 제한 대상을 ‘목록’으로 선택합니다.
- 원본에 다음 수식을 입력합니다.
=$E$2:$E$6- 확인을 클릭합니다.
이제 E2에 입력된 값이 드롭다운 목록에 표시됩니다.
기존 목록의 글자를 변경하면 드롭다운에도 변경된 내용이 반영됩니다.
다만 E7에 새로운 항목을 추가하는 경우에는 기존 범위에 포함되지 않으므로 원본 범위를 늘려야 합니다.
5. 드롭다운 목록을 자동으로 확장하는 방법
상품 종류나 거래처가 계속 늘어나는 업무에서는 목록이 자동으로 확장되도록 설정하면 편리합니다.
Microsoft 365 등 동적 배열을 지원하는 엑셀에서는 다음과 같은 방법을 사용할 수 있습니다.
먼저 별도의 ‘목록관리’ 시트 A2에 주문 상태를 입력합니다.
A2부터 시작하는 목록을 엑셀 표로 변환합니다.
- A1에 ‘주문상태’라는 제목을 입력합니다.
- A1 범위를 선택합니다.
- Ctrl + T를 누릅니다.
- ‘머리글 포함’을 확인하고 표를 만듭니다.
- 표 이름을 ‘주문상태표’로 변경합니다.
그다음 이름 관리자에서 새 이름을 등록합니다.
이름: 주문상태목록
참조 대상:
=주문상태표[주문상태]주문 관리 시트에서 드롭다운을 설정할 셀을 선택하고 데이터 유효성 검사 원본에 다음과 같이 입력합니다.
=주문상태목록이제 주문상태표의 마지막 행에 새로운 항목을 추가하면 표 범위와 드롭다운 목록이 함께 확장됩니다.
표 이름과 열 제목은 실제 엑셀에서 지정한 이름과 정확하게 일치해야 합니다.
6. 드롭다운 목록과 COUNTIF 함수 함께 사용하기
드롭다운 목록을 사용하면 주문 상태별 건수를 자동으로 계산할 수 있습니다.
다음과 같은 주문 관리표가 있다고 가정하겠습니다.
| 주문번호 | 상품명 | 주문상태 |
|---|---|---|
| 1001 | 블라우스 | 배송완료 |
| 1002 | 셔츠 | 배송중 |
| 1003 | 청바지 | 상품준비 |
| 1004 | 운동화 | 배송완료 |
| 1005 | 재킷 | 주문접수 |
| 1006 | 원피스 | 배송완료 |
주문 상태가 C2에 입력되어 있다면 배송완료 건수는 다음과 같이 계산합니다.
=COUNTIF(C2:C7,"배송완료")계산 결과는 3건입니다.
배송중인 주문 건수는 다음 수식으로 계산합니다.
=COUNTIF(C2:C7,"배송중")결과는 1건입니다.
전체 주문 대비 배송완료 비율도 계산할 수 있습니다.
=IFERROR(COUNTIF(C2:C7,"배송완료")/COUNTA(A2:A7),0)결과 셀에 백분율 서식을 적용하면 50%로 표시됩니다.
이 계산은 A열의 주문번호가 모든 주문에 빠짐없이 입력되어 있다는 조건을 사용합니다.
드롭다운 목록과 COUNTIF 함수를 결합하면 주문 처리 현황, 업무 진행 상황, 재고 상태 등을 빠르게 집계할 수 있습니다.
7. 드롭다운 목록에서 잘못된 입력 방지하기
드롭다운을 설정했다고 해서 사용자가 반드시 목록에서만 선택해야 하는 것은 아닙니다.
데이터 유효성 검사의 오류 메시지 설정에 따라 직접 입력한 값이 허용될 수도 있습니다.
목록에 없는 값을 입력하지 못하도록 하려면 다음과 같이 설정합니다.
- 드롭다운 셀을 선택합니다.
- 데이터 → 데이터 유효성 검사로 이동합니다.
- ‘오류 메시지’ 탭을 선택합니다.
- ‘유효하지 않은 데이터를 입력하면 오류 메시지 표시’를 활성화합니다.
- 스타일을 ‘중지’로 설정합니다.
오류 메시지는 다음과 같이 작성할 수 있습니다.
제목: 입력 오류
내용: 주문 상태는 드롭다운 목록에서 선택해 주세요.
이렇게 설정하면 일반적인 직접 입력 과정에서 목록에 없는 값을 차단할 수 있습니다.
다만 붙여넣기 등 일부 작업은 데이터 유효성 검사 제한을 우회할 수 있으므로 중요한 관리표는 정기적으로 잘못된 값을 검사하는 것이 좋습니다.
공식 기능과 설정 방법은 마이크로소프트 지원 문서에서도 확인할 수 있습니다.
확인하기 : Microsoft 엑셀 드롭다운 목록 만들기
8. 드롭다운 목록이 작동하지 않을 때 해결 방법
드롭다운이 표시되지 않거나 선택 항목이 제대로 나오지 않는 경우에는 다음 사항을 확인합니다.
| 발생 문제 | 원인 및 해결 방법 |
|---|---|
| 화살표가 보이지 않음 | 해당 셀을 선택하고 ‘셀 안에 드롭다운 표시’ 확인 |
| 항목이 하나로 표시됨 | 쉼표 또는 시스템 목록 구분 기호 확인 |
| 새로운 항목이 표시되지 않음 | 원본 범위를 확장하거나 표와 이름 정의 사용 |
| 목록에 빈칸이 표시됨 | 원본 범위에 빈 셀이 포함되어 있는지 확인 |
| 목록을 수정할 수 없음 | 시트 보호 또는 편집 권한 확인 |
| 다른 시트의 목록 연결 오류 | 이름 정의를 사용해 원본 범위 연결 |
특히 다른 시트에 있는 목록을 연결할 때는 원본에 다른 시트의 셀 주소를 직접 입력하는 방식보다 이름 정의를 사용하는 방법이 호환성과 관리 측면에서 편리합니다.
9. 챗GPT로 업무별 드롭다운 목록 자동 설계하기
챗GPT는 엑셀 드롭다운 목록의 항목뿐 아니라 관리표 구성과 필요한 계산식까지 함께 설계하는 데 활용할 수 있습니다.
예를 들어 다음과 같이 요청합니다.
“엑셀로 온라인 쇼핑몰 상품 관리표를 만들려고 해. 상품번호, 상품명, 상품분류, 재고상태, 판매상태 열을 구성하고, 상품분류와 재고상태, 판매상태에는 드롭다운 목록을 적용할 수 있도록 항목과 설정 방법을 알려줘.”
이 요청을 바탕으로 다음과 같은 관리표를 구성할 수 있습니다.
| 상품번호 | 상품명 | 상품분류 | 재고상태 | 판매상태 |
|---|---|---|---|---|
| P001 | 블라우스 | 여성의류 | 정상 | 판매중 |
| P002 | 셔츠 | 남성의류 | 부족 | 판매중 |
| P003 | 운동화 | 신발 | 품절 | 판매중 |
| P004 | 가방 | 잡화 | 정상 | 판매중 |
드롭다운 목록은 다음과 같이 구분합니다.
상품분류: 여성의류, 남성의류, 신발, 잡화
재고상태: 정상, 부족, 품절
판매상태: 판매중, 판매중지, 판매종료
이렇게 구성하면 상품을 등록할 때마다 동일한 기준으로 데이터를 관리할 수 있습니다.
추가로 재고상태가 ‘품절’인 상품만 추출하거나 판매중인 상품 수를 계산하는 기능도 결합할 수 있습니다.
10. 엑셀 드롭다운 목록을 효율적으로 사용하는 방법
드롭다운 목록은 단순히 항목을 선택하는 기능이지만 다른 엑셀 기능과 함께 사용하면 활용도가 높아집니다.
조건부 서식을 적용하면 ‘품절’은 빨간색, ‘부족’은 노란색처럼 상태별로 구분할 수 있습니다.
COUNTIF 함수를 사용하면 상태별 상품 수를 계산할 수 있고, 필터 기능을 사용하면 특정 상태의 상품만 확인할 수 있습니다.
또한 원본 목록을 별도로 관리하면 여러 관리표에서 동일한 기준을 사용할 수 있습니다.
처음에는 간단한 주문 상태 목록부터 만들어 보고, 익숙해지면 상품 분류, 거래처, 재고 관리 등으로 확장하는 것이 좋습니다.
엑셀 드롭다운 목록을 챗GPT와 함께 설계하면 반복 입력을 줄이고 데이터의 일관성을 높일 수 있습니다. 특히 주문 관리, 재고 관리, 고객 관리처럼 정확한 입력이 필요한 업무에서 유용하게 활용할 수 있습니다.
답글 남기기