엑셀에서 자동 업데이트되는 목록 만드는 방법

📌 핵심요약

✔️ 표 기능 활용: Ctrl + T로 표 만들기 → 자동 확장
✔️ INDIRECT 함수 활용: 표와 연동된 목록 생성
✔️ OFFSET 함수 활용: 동적 범위로 자동 업데이트

---

엑셀에서 목록을 만들 때마다 새로운 데이터를 추가할 때마다 수동으로 범위를 변경하는 것은 비효율적이다. 특히 드롭다운 목록을 사용할 때마다 범위를 직접 수정해야 한다면 작업 시간이 늘어나고 실수도 발생할 가능성이 커진다. 이를 방지하려면 목록이 자동으로 확장되는 기능을 활용해야 한다. 엑셀에는 데이터를 추가할 때마다 자동으로 업데이트되는 여러 가지 방법이 있으며, 이 중 가장 효과적인 3가지 방법을 소개한다.

 

표 기능을 활용하여 자동 확장하기

엑셀의 표 기능을 사용하면 새로운 데이터를 추가할 때마다 범위가 자동으로 확장된다. 표로 변환된 데이터는 범위가 동적으로 적용되므로, 드롭다운 목록이나 피벗 테이블에서도 자동으로 업데이트된다. 사용 방법은 간단하다. 먼저 목록 데이터를 선택한 후, Ctrl + T를 눌러 표로 변환하면 된다. 이후 [테이블 디자인] 탭에서 표의 이름을 변경하면 더욱 관리가 쉬워진다. 표 기능을 활용하면 단순히 목록이 자동으로 확장될 뿐만 아니라, 정렬과 필터 기능까지 함께 제공되어 데이터 관리가 훨씬 편리해진다.

 

✅ 사용 방법:

1. 목록으로 사용할 데이터를 선택
2. 단축키 Ctrl + T를 눌러 표 만들기 실행
3. [확인] 버튼 클릭 후, 표 변환 완료
4. [테이블 디자인] 탭에서 표 이름을 변경 (예: 목록데이터)

 

INDIRECT 함수를 사용하여 목록 연동하기

표 기능과 INDIRECT 함수를 함께 사용하면 목록을 더욱 유연하게 관리할 수 있다. 먼저, 표로 변환한 데이터를 활용한 후, 데이터 유효성 검사의 ‘원본’ 입력란에 =INDIRECT("표이름[열이름]")을 입력하면 된다. 이렇게 설정하면 표에 새로운 값이 추가될 때마다 드롭다운 목록도 자동으로 확장된다. INDIRECT 함수를 활용하면 표의 특정 열과 연결되어 있기 때문에, 목록의 구조가 바뀌어도 자동으로 반영된다는 장점이 있다.

 

✅ 사용 방법:

1. 목록을 적용할 셀을 선택
2. [데이터] 탭 → [데이터 유효성 검사] 클릭
3. [제한 대상]을 "목록"으로 설정
4. [원본]에 다음과 같이 INDIRECT 함수 입력

=INDIRECT("목록데이터[머릿글]")

 

(여기서 목록데이터는 표 이름, 머릿글은 표의 열 제목)

 

    5. [확인] 버튼 클릭 후 완료

 

 

 

 

OFFSET 함수를 활용한 동적 범위 설정

OFFSET 함수를 사용하면 표 기능을 사용하지 않아도 자동으로 확장되는 목록을 만들 수 있다. 먼저 [이름 관리자]에서 새로운 범위를 생성한 후, 참조 대상으로 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)을 입력하면 된다. 이 수식은 A열에서 값이 입력된 개수만큼 범위를 자동으로 설정하는 원리다. 이후 데이터 유효성 검사에서 원본을 =목록범위로 지정하면, 새로운 데이터가 추가될 때마다 자동으로 업데이트된다. OFFSET 함수를 활용하면 특정 열에 대한 동적 범위를 지정할 수 있어, 목록을 더욱 유연하게 활용할 수 있다.

 

✅ 사용 방법:

1. [수식] 탭 → [이름 관리자] 클릭
2. 새로운 이름을 추가 (목록범위)
3. 참조 대상에 다음 OFFSET 함수 입력
    

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

 

(A1부터 비어 있지 않은 셀 개수만큼 범위를 자동 설정)

 

 

   5. 데이터 유효성 검사에서 원본을 =목록범위로 지정

 

목록 자동 업데이트로 업무 효율을 높이자

엑셀에서 목록을 수동으로 변경하는 것은 비효율적인 작업이다. 표 기능을 활용하면 범위가 자동으로 확장되고, INDIRECT 함수는 특정 표와 연동하여 자동으로 업데이트되는 목록을 제공한다. 또한 OFFSET 함수는 특정 열을 기반으로 동적으로 범위를 설정할 수 있어 더욱 유연한 관리가 가능하다. 이러한 자동 업데이트 기능을 활용하면 데이터를 추가할 때마다 번거롭게 수정할 필요 없이, 항상 최신 데이터가 반영된 목록을 유지할 수 있다. 이제 불필요한 수작업을 줄이고 더욱 스마트하게 목록을 관리해 보자.