직장인의 70%가 모르는 엑셀 자동채우기 모든 것

우리가 매일 사용하지만 막연하게만 알고 있던 엑셀 자동채우기 기능의 동작 원리 및 다양한 설정 사용법을 알아봅니다.

홈페이지 » 직장인의 70%가 모르는 엑셀 자동채우기 모든 것 총정리!

엑셀 자동채우기 모든 것, 동작원리부터 설정방법까지 샅샅이 살펴보자!

엑셀 자동채우기 모든 것 목차 바로가기
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [기초입문] 엑셀 자동채우기의 모든 것
    예제파일

자동채우기란 무엇인가요?

자동채우기는 단어 그대로 셀 안에 입력된 값을 드래그하면 정해진 패턴에 따라 값을 자동으로 채워주는 기능입니다. 엑셀을 자주사용한다면, 특히 많은 양의 데이터를 다루는 실무자라면, 반드시 숙지해야 할 엑셀의 필수! 필수! 기능이라고 할 수 있는데요.

그럼에도 불구하고 대부분의 실무자가 자동채우기 기능에 대해서 막연하게만 알고 있을 뿐, 다양한 상황에 어떻게 적용하는지 자세한 내용을 경우가 많습니다.

아마도 너무나 당연하게 생각되는 쉬운 기능이다 보니, 무심코 지나가게 되는 것이 아닐까요?^^;;

엑셀 자동채우기 기능은 어떻게 사용하나요?

자동채우기를 사용하는 방법은 아주 간단합니다. 데이터가 입력된 셀을 선택한 뒤, 마우스를 셀의 오른쪽 아래로 이동 그리고 원하는 방향으로 쭈욱~ 드래그 하면되는데요. 각 단계별로 그림과 함께 살펴보겠습니다.

  1. 엑셀에서 셀 또는 범위를 선택하면 오른쪽 아래에 네모난 점이 표시되는데요. 이 점을 '자동채우기 핸들'이라고 부릅니다.

    자동채우기 핸들
    셀 오른쪽 아래 네모난 점은 '자동채우기 핸들'이라고 합니다.

  2. 마우스 커서를 자동채우기 핸들 위로 이동하면, 커서가 검은색 십자가 모양으로 변경됩니다.

    자동채우기 커서 검은 십자가
    마우스를 자동채우기 핸들 위로 이동하면 커서가 검은색 십자가로 변경됩니다.

  3. 그 상태에서 원하는 방향으로 마우스를 쭈욱~ 드래그하면 입력된 값이 정해진 패턴에 따라 자동채우기 됩니다.

    엑셀 자동채우기
    마우스를 아래로 쭉 드래그하면 자동채우기 됩니다.

자동채우기 단축키 사용법 및 주의사항

자동채우기는 마우스로 드래그 하는 것 외에 키보드 단축키로도 구현할 수 있습니다.

  • 아래 방향 자동채우기 : Ctrl + D
    (D는 Down의 약자입니다)
  • 오른쪽 방향 자동채우기 : Ctrl + R
    (R은 Right의 약자입니다)

단, 자동채우기를 단축키로 하는 것과 마우스로 드래그 하는 것에는 약간의 차이점이 있는데요.

단축키로 자동채우기 할 경우, 셀에 입력된 값이 수식(=셀 참조)일 경우를 제외하고,
그 외에 값은 패턴에 상관없이 범위의 첫번째 값을 그대로 복사/붙여넣기 합니다.

이 차이점은 아래 예제를 살펴보면 쉽게 이해할 수 있습니다.

  1. 예제파일의 [1] 시트로 이동한 뒤, J17:K18 범위를 선택 후 아래방향으로 자동채우기 합니다. 그럼 셀 안에 입력된 숫자 패턴을 인식하여 1과 2씩 증가된 숫자로 자동채우기 되는 것을 볼 수 있습니다.

    엑셀 자동채우기 드래그
    숫자 패턴을 인식하여 증가하는 형태로 자동채우기 됩니다.

  2. 이번에는 N17:O22 범위를 선택한 뒤, 키보드 Ctrl + D 를 눌러 단축키로 자동채우기 해 보겠습니다. 단축키로 자동채우기 할 경우, 범위 숫자 패턴에 상관없이 첫번째 셀의 값이 그대로 복사/붙여넣기 되어 자동채우기 됩니다.

    엑셀 자동 채우기 단축키
    범위 첫번째 값이 그대로 복사되어 자동채우기 됩니다.

  3. 단 수식의 경우 마우스/단축키에 상관없이 올바르게 자동채우기 되는 것을 볼 수 있습니다. 따라서, 자동채우기 단축키는 첫번째 셀의 값을 그대로 채워야하는 특별한 상황을 제외하면 '수식'이 입력된 셀에서만 사용하는 것이 좋습니다.

    셀 참조 자동 채우기
    셀 참조는 마우스/단축키 동일하게 자동채우기 됩니다.

엑셀 자동채우기 패턴 & 규칙 살펴보기

자동채우기는 셀에 입력된 데이터 형식에 따라 특정한 패턴과 규칙으로 실행되는데요. 각 데이터형식별 자동채우기 규칙은 아래 표와 같습니다.

셀 참조방식에 대한 보다 자세한 설명은 아래 셀 참조방식의 모든 것 영상강의를 참고해주세요.

데이터 종류 자동채우기 조건 설명 예제
문자데이터 모든 상황 선택된 셀(범위)의 값을 반복해서 출력 사과,배 -> 사과,배,사과,배,...
숫자데이터 셀 1개 숫자를 반복해서 출력
(Ctrl 키와 동시에 드래그 시 1씩 증가)
1 -> 1,1,1, ...
셀 2개 이상 패턴을 인식하여 자동채우기
(Ctrl 키와 동시에 드래그 시 반복 출력)
1,3 -> 1,3,5,7, ...
날짜데이터 셀 1개(월) 월 단위 증가 1월 -> 1월, 2월, 3월, ...
셀 1개(일) 일 단위 증가 1월 1일 -> 1월 1일, 1월 2일, 1월 3일, ...
셀 2개이상 패턴을 인식하여 자동채우기 1월, 3월 -> 1월, 3월, 5월, 7월, ...
시간데이터 셀 1개 시간 단위 증가 10:00 -> 10:00, 11:00, 12:00, ...
셀 2개 이상 패턴을 인식하여 자동채우기 10:01, 10:02 -> 10:01, 10:02, 10:03, ...
셀 참조 상대참조 자동채우기 방향으로 참조되는 셀 같이 이동 A1, B2, C3, ...
혼합참조 행 또는 열만 고정되어 이동 $A1, $A2, $A3, ...
절대참조 참조되는 셀 이동하지 않고 항상 고정 $A$1, $A$1, $A$1, ...
문자/숫자 혼합 숫자 1개 문자는 반복되면서 숫자만 변경 박스1 -> 박스2, 박스3, ...
숫자 2개 이상 마지막 숫자만 변경 1학년 1반 -> 1학년 2반, 1학년 3반, ...
그 외 목록 사용자지정목록 사용자 지정 목록에 입력된 순서대로 자동채우기 1사분기, 2사분기, 3사분기, 등...

자동채우기를 더 편리하게! - 자동채우기 옵션 활용방법

자동채우기를 사용하다보면 종종 아래와 같은 상황이 발생하곤 합니다.

자동 채우기 서식 깨짐
자동채우기를 사용하면 종종 기존의 서식이 깨지는 문제가 발생합니다.

위와 같이 자동채우기를 할 때 서식이 망가지는 문제를 해결하려면 어떻게 해야 할까요? 해결 방법은 아주 간단한데요. 바로 '자동채우기 옵션'을 활용하는 것 입니다. 자동채우기를 하고나면 자동채우기 한 범위의 오른쪽 아래에 조그만 버튼이 표시되는데요. 이 버튼을 '자동채우기 옵션' 이라고 합니다.

엑셀 자동 채우기 옵션
자동채우기 옵션을 활용할 수 있습니다.

이 옵션을 클릭하면 아래 다양한 목록이 표시되는데요. 여기서 '서식 없이 채우기'를 선택하면 서식 없이 셀 안의 값(또는 수식)만 자동채우기 할 수 있습니다.

엑셀 서식 없이 채우기
'서식 없이 채우기'를 클릭하면 값만 자동채우기됩니다.

그 외에도 채우기 옵션은 다양한 기능을 지원하는데요. 채우기 옵션에서 선택가능한 목록은 아래와 같습니다.

항목 설명
셀 복사 첫 번째 셀의 내용을 그대로 복사하여 자동채우기 합니다.
연속 데이터 채우기 패턴을 인식하여 자동채우기 합니다. (기본값)
서식만 채우기 기존의 값은 유지한 채 서식만 자동채우기 합니다.
서식 없이 채우기 기존 서식은 유지한 채 값만 자동채우기 합니다.
일 단위 채우기(날짜) 셀에 입력된 값이 날짜일 경우, 일 단위로 증가합니다.
평일 단위 채우기(날짜) 주말은 제외하고 평일만 일단위로 증가합니다.
월 단위 채우기(날짜) 월 단위로 증가합니다.
연 단위 채우기(날짜) 연 단위로 증가합니다.
빠른 채우기 자동채우기 한 범위의 왼쪽 데이터를 참고하여 비슷한 형태의 데이터를 추출(데이터 분할)하는 기능입니다.

빠른채우기는 매우 다양한 방법으로 응용할 수 있는데요. 이에 대한 내용은 별도의 강의에서 자세히 다룰 예정입니다.

나만의 자동채우기 목록 만들기 - 사용자 지정 목록

사용자 지정 목록을 활용하면 나만의 자동채우기 패턴을 생성하여, 반복 입력하는 데이터를 보다 손 쉽게 입력할 수 있습니다. 예를 들어, "동, 서, 남, 북" 이나 "기, 승, 전, 결" 과 같이 반복해서 입력하는 데이터를 사용자 지정 목록에 추가할 수 있습니다.

  1. [파일] - [옵션] - [고급] 으로 이동한 뒤, 스크롤을 아래로 이동하여 '일반' 계열의 [사용자 지정 목록 편집] 버튼을 클릭합니다.

    엑셀 고급 사용자 지정 목록 편집
    파일 - 옵션 - 고급 - 사용자 지정 목록 편집으로 이동합니다.

  2. [목록 항목]에서 자동채우기에 적용할 목록을 직접 입력한 뒤 [추가] 버튼을 누르거나 또는 [목록 가져올 범위]에 목록이 입력된 범위를 지정한 뒤, [가져오기] 버튼으로 사용자 지정 목록을 추가합니다. [확인] 버튼을 눌러 마무리하면 사용자 지정 목록 추가가 완료됩니다.

    사용자 지정 목록 추가
    자동채우기 할 나만의 목록을 직접 추가할 수 있습니다.

  3. 이후 값을 입력한 뒤, 자동채우기하면 사용자 지정 목록에 추가된 값이 순서대로 자동채우기 되는 것을 확인할 수 있습니다.

    엑셀 나만의 자동채우기 목록
    나만의 자동채우기 목록이 추가되었습니다.

  4. 단, 사용자 지정 목록을 '목록 항목'에서 직접 입력하여 추가할 경우에는 최대 글자수가 255자로 제한되므로 255자 이상의 값을 사용자 지정목록에 추가할 경우에는 '목록 가져올 범위'로 범위를 지정하여 추가하는 것에 주의합니다.

    사용자 목록 가져오기
    255자 이상의 여러 항목을 추가해야 할 경우, 범위를 직접 지정하여 목록에 추가합니다.

5 7 투표
게시글평점
guest
15 댓글
Inline Feedbacks
모든 댓글 보기
미성
미성
2020년 10월 30일 4:57 오후
게시글평점 :
     

몰랐던 새로운 것을 또 다시 배우고 가네요
항상 감사합니다

mf21
mf21
2020년 11월 2일 1:33 오후
게시글평점 :
     

역시, 따봉입니다!!!

tacondia
tacondia
2020년 12월 8일 2:12 오전
게시글평점 :
     

정말로 필요했던것인데 이렇게 쉽게 무료로 도와주시니 감사합니다

sola gratia
sola gratia
2020년 12월 26일 10:47 오전
게시글평점 :
     

잘 보고 갑니다.

70469
70469
2021년 1월 26일 10:24 오후
게시글평점 :
     

오늘도 많이 배우고 갑니다
감사합니다

바보야
바보야
2021년 2월 18일 11:43 오전
게시글평점 :
     

재미있습니다.

opt
opt
2021년 3월 15일 4:30 오후
게시글평점 :
     

감사합니다

군림천하
군림천하
2021년 4월 16일 10:16 오전
게시글평점 :
     

감사합니다.

pyou****
pyou****
2021년 9월 25일 12:47 오전
게시글평점 :
     

자동채우기 쉬우면서도 잘 몰랐는데 감사합니다.

나도_엑셀
나도_엑셀
2021년 10월 6일 2:53 오후
게시글평점 :
     

감사합니다.

tmddm****
tmddm****
2021년 10월 8일 7:15 오전
게시글평점 :
     

자동채우기 정복~!!
감사합니다~~~

태백
태백
2021년 11월 6일 10:51 오후
게시글평점 :
     

잘 배우겠습니다.

jwy****
jwy****
2021년 11월 8일 12:35 오전
게시글평점 :
     

감사합니다.

하유네비
하유네비
2021년 11월 20일 4:51 오후
게시글평점 :
     

감사합니다

토백이
토백이
2021년 11월 27일 9:58 오후
게시글평점 :
     

기본에 충실한 강의 고맙습니다^^

15
0
여러분의 생각을 댓글로 남겨주세요.x