직장인이 꼭 알아야 할 엑셀 실무 꿀팁 7가지
엑셀 시간단축 팁 7가지 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 실무자가 꼭 알아야 할 핵심 스킬 7가지예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
표 이미지 → 텍스트 변환하기
이미지로 된 중요 자료나 메신저로 전달받은 그림으로 된 표를 엑셀이나 워드로 옮겨야 할 때, 손으로 하나하나 타이핑하는 작업을 이제 '윈도우 기본 기능'으로 손쉽게 자동화 할 수 있습니다. 윈도우 11 이후 버전에서 기본으로 제공되는 '캡처 도구(스크린 샷)' 을 사용하면 이미지 속의 텍스트를 매우 정확하고 편리하게 추출할 수 있습니다.
- 먼저 텍스트가 포함된 이미지를 화면에 띄운 후, Win + Shift + S 를 눌러 스크린 캡처를 실행합니다. 스크린 캡처가 실행되면 그림이 있는 영역을 드래그합니다.
Win + Shift + S 를 눌러 스크린캡처를 실행합니다. - 영역을 드래그하면 윈두우 화면 오른쪽 아래에 '스크린샷이 크립보드에 복사됨' 이라는 캡처도구 알림이 표시됩니다. 알림을 선택하면 캡처도구가 실행됩니다.
캡처도구 알림이 나오면 버튼을 클릭합니다. - 만약 캡처도구 알림이 나오지 않는다면, 윈도우 검색에서 '캡처'를 검색해 캡처도구를 직접 실행한 후 [새 캡처] 버튼을 클릭해 직접 스크린 캡처를 할 수 있습니다.
알림이 나오지 않을 경우, 검색에서 캡처도구를 실행합니다. - 캡처 도구가 실행되면 중간에 '텍스트 작업' 버튼을 클릭합니다. 그러면 그림에 포함된 모든 텍스트가 자동으로 인식됩니다. 이후, [테이블로 복사] 버튼을 클릭하면 텍스트를 표 형식으로 복사할 수 있습니다.
캡처도구에서 '텍스트 작업' → 테이블로 복사를 클릭합니다. - 이제 복사한 텍스트를 엑셀에 붙여넣기 하면 깔끔하게 표 형식으로 텍스트가 추출 된 것을 확인할 수 있습니다.
복사한 데이터를 엑셀에 붙여넣기 합니다.
여러 범위 복/붙 '클립보드' 활용법
실무에서 데이터를 정리하다 보면, 여러 곳에 흩어진 데이터를 한 곳으로 모아야 하는 상황이 종종 발생합니다. 대부분은 '필터'를 활용해 손쉽게 데이터를 필터링하고 옮길 수 있지만, 무작위로 흩어진 데이터를 옮겨야 한다면 Ctrl 키를 누른 채로 범위를 하나씩 선택해 '복사-붙여넣기' 작업을 반복해야 하는데요.
이럴 때, 엑셀의 '클립보드' 기능을 사용하면 여러 곳에 흩어진 데이터를 편리하게 옮길 수 있습니다.
- [홈] 탭에서 클립보드 그룹의 왼쪽 아래에 있는 작은 화살표를 클릭합니다. 그러면 왼쪽에 '클립보드' 패널이 열리게 됩니다.
홈 - 클립보드에서 화살표 아이콘을 클릭합니다. - 클립보드 패널이 열린 상태에서, 범위를 하나씩 복사하면 클립보드 영역에 항목이 하나씩 추가됩니다.
데이터를 복사하면 클립보드 영역에 데이터가 하나씩 추가됩니다. - 모든 데이터를 복사한 후, 붙여넣을 셀을 클릭하고 [모두 붙여넣기] 버튼을 클릭하면 데이터가 한 번에 옮겨집니다.
모두 붙여넣기를 클릭해 한 번에 붙여넣기합니다.
화면에 보이는 셀만 복사
엑셀로 작업하다 보면 숨겨진 데이터가 범위에 포함되어 복사-붙여넣기를 하면, 원치않는 데이터가 따라올 때가 있습니다. 이럴 때, '화면에 보이는 셀만 복사' 하는 Alt + 세미콜론(;) 단축키를 사용하면 편리합니다.
- 숨겨진 행을 포함한 범위를 선택한 후, Alt + ;(세미콜론)을 누르면 선택한 범위가 쪼개지면서 화면에 보이는 셀만 선택됩니다.
Alt + ; 를 눌러 화면에 보이는 셀만 복사합니다. - 그 상태에서 범위를 복사 - 붙여넣기하면 화면에 보이는 셀만 편리하게 복사할 수 있습니다.
복사한 데이터를 붙여넣습니다.
필터 범위만 빠르게 옮기는 단축키
엑셀에서 필터 기능은 정말 자주 사용하는 기능 중 하나입니다. 하지만 필터링 된 데이터만 참조하고자 필터링 된 범위를 우측 영역으로 복사 - 붙여넣기하면, 범위가 하나의 덩어리로 복사되는 문제가 있습니다. 이럴 때, Ctrl + R 단축키를 사용하면 편리합니다.
- 먼저 필터링 된 범위에서 우측으로 복사할 데이터가 작성된 범위를 선택합니다.
복사할 필터링 범위를 선택합니다. - 그 상태에서 Ctrl 키를 누른 상태로 붙여넣을 범위를 이어서 선택합니다.
Ctrl 키를 누른 상태로 붙여넣을 범위를 선택합니다. - 두 범위를 선택한 후, Ctrl + R 을 누르면 필터링 된 범위만 우측으로 복사됩니다.
필터링 범위가 우측으로 복사됩니다. - 이제 범위에서 필터를 해제하면, 필터가 적용됐던 데이터만 우측으로 복사된 것을 확인할 수 있습니다.
필터를 해제하면 복사된 데이터를 확인할 수 있습니다.
대량 순번/날짜 일괄 생성
엑셀로 보고서나 명부를 작성할 때, 수백/수천개의 순번을 입력하기 위해 자동채우기를 사용하곤 합니다. 하지만 수천개의 순번을 자동채우기로 드래그해서 채우는 것은 비효율적일 수 있는데요. 엑셀 2021 이후 버전을 사용중이라면 SEQUENCE 함수를 사용하거나, 엑셀 이전 버전에서는 '계열' 기능을 사용해 대량의 순번이나 날짜를 편리하게 생성할 수 있습니다.
- 엑셀 2021 이후 버전을 사용중이라면, SEQUENCE 함수를 사용해 대량의 순번을 편리하게 만들 수 있습니다. 비어있는 셀에 다음과 같이 SEQUENCE 함수를 작성합니다.
=SEQUENCE(1200)
SEQUENCE 함수를 입력합니다. - 함수를 실행하면 1200번까지 순번이 한 번에 생성됩니다.
대량의 순번이 바로 생성됩니다. - 이 SEQUENCE 함수를 활용하면 실시간으로 업데이트 되는 만년 달력도 편리하게 만들 수 있습니다. 자동 만년 달력을 만드는 방법은 아래 1분 영상에서 알기 쉽게 정리했으니 확인해보세요!👇
- 엑셀 이전 버전 사용자는 '계열' 기능으로 대량의 순번을 빠르게 생성할 수 있습니다. 먼저 비어있는 시작값을 입력합니다. 이번 예제에서는 시작값으로 1을 입력했습니다.
순번의 시작값을 입력합니다. - 시작값이 입력된 셀을 선택한 후, [홈] 탭 - [채우기] - [계열]을 실행합니다.
홈 - 채우기 - 계열을 실행합니다. - [계열] 창이 실행되면 방향은 '열'을 선택하고, 종료값으로 1200을 입력한 후 [확인] 버튼을 클릭합니다.
방향과 종료값을 입력한 후, 확인 버튼을 클릭합니다. - 그러면 1200번까지 순번이 한 번에 생성됩니다. 계열 기능을 사용하면 시작일과 종료일까지 날짜도 편리하게 채울 수 있습니다.
빠른 채우기
이 기능 하나면, 실무에서 필요한 대부분의 텍스트 가공 작업을 단축키 하나로 편리하게 해결할 수 있습니다. 동일한 패턴의 데이터를 가공해야 할 때, 빠른채우기(Ctrl + E) 기능을 꼭 사용해보세요!
빠른채우기 기능의 더 다양한 활용 예제는 아래 기초 입문 강의에서 자세히 정리했습니다.
- 먼저 휴대폰 번호의 가운데 4자리를 별표(*)로 마스킹하는 예제를 살펴보겠습니다. 휴대폰 번호가 작성된 셀 우측에 별표(*)로 마스킹 처리가 된 데이터 패턴을 하나 입력합니다.
인접한 데이터를 참고해서 채울 패턴을 입력합니다. - 이제 Ctrl + E 를 눌러 빠른채우기를 실행하면, 나머지 마스킹 된 휴대폰 번호가 한 번에 채워집니다.
Ctrl + E 로 빠른채우기를 실행합니다. - 빠른채우기를 사용하면 다음과 같이 이메일 아이디 마스킹, 괄호 안 데이터 추출, 숫자만 추출 등 다양한 상황에 활용할 수 있습니다.
· SeminKim@gmail.com → Sem***@gmail.com
· 라이온 킹(The Lion King) → The Lion King
· OBS-9011-FILE-021 → 9011021
빠른 채우기는 다양한 상황에 활용됩니다.
셀 병합 오류 완벽 해결법
실무에서는 엑셀 보고서를 깔끔하게 정리하기 위해 셀 병합을 종종 사용하곤 합니다. 셀 병합을 사용하면 겉으로 보기엔 예쁘고 정리가 잘 된 것처럼 보이지만, 실무에서는 치명적인 문제를 일으키곤 하는데요. 필터를 걸었을 때 첫 번째 데이터만 표시되거나, SUMIF 함수/피벗테이블로 데이터 집계 시 누락되는 문제가 발생하곤 합니다.
그럴 때, 겉으로는 셀 병합처럼 보이지만 실제로는 모든 셀에 데이터가 완벽하게 채워진 범위를 만드는 방법을 알아보겠습니다.
- 먼저 셀 병합이 적용된 범위를 다른 곳에 임시로 복사-붙여넣기합니다.
셀 병합된 범위를 임시 열에 붙여넣기합니다. - 이후, 셀 병합이 적용된 범위를 선택한 후 [홈] 탭 - 병합하고 가운데 맞춤을 클릭해 셀 병합을 해제합니다.
셀 병합이 적용된 기존 범위의 병합을 해제합니다. - 병합이 풀린 범위를 선택한 상태로 Ctrl + G 를 눌러 [이동]을 실행하고 [옵션]을 클릭해 이동 옵션으로 이동합니다.
병합을 해제한 후, 이동 옵션을 실행합니다. - [이동 옵션] 에서 '빈 셀'을 선택하고 [확인] 버튼을 클릭하면 범위에서 '빈 셀'이 한 번에 선택됩니다.
이동 옵션에서 빈 셀을 선택합니다. - 빈 셀이 선택된 상태로 등호(=)와 위쪽 방향키를 눌러 선택된 셀 기준 윗 셀을 선택하고, Ctrl + Enter 를 누르면 비어있는 셀이 한 번에 채워집니다.
등호(=)와 윗쪽 방향키를 눌러 윗 셀을 선택한 후, Ctrl + Enter로 실행합니다. - 마지막으로 첫번째 단계에서 임시로 복사한 셀 병합 범위를 선택하고 [홈] 탭 - [서식 복사]를 클릭해 서식을 복사한 후 기존 범위의 시작셀을 선택하면, 겉으로는 셀 병합처럼 보이지만 실제로는 모든 데이터가 완벽하게 채워진 셀 병합 범위가 완성됩니다.
임시 열에 복사했떤 셀 병합 범위의 서식을 복사한 후, 데이터가 채워진 범위의 시작셀을 클릭해 서식을 적용합니다.
- 먼저 텍스트가 포함된 이미지를 화면에 띄운 후, Win + Shift + S 를 눌러 스크린 캡처를 실행합니다. 스크린 캡처가 실행되면 그림이 있는 영역을 드래그합니다.
