들어가기 전에
Excel에는 수많은 도구와 기능이 있어서 좋은 반면, 자주 사용하지 않는 유용한 기능들을 자칫 간과하기 쉽습니다. Excel 사용 방식을 완전히 바꿔줄 수 있는, 잘 알려지지 않은 숨겨진 팁 네 가지를 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 4 Hidden Microsoft Excel Tools You Probably Didn’t Know
- URL: https://www.howtogeek.com/microsoft-excel-hidden-tools-didnt-know/
1. 장기 계획을 위한 목표값 찾기
Excel의 잘 알려지지 않은 목표 찾기 도구는 최종 목표는 알지만 달성 방법을 모를 때 도움이 됩니다. 2년 후에 새 집을 구입할 계획이라고 가정해 보겠습니다. 현재 연 2.5%의 이자율로 매월 400달러를 저축하고 있으므로 미래 가치(FV) 기능을 통해 계산한 결과 2년 동안 9833.55달러를 저축할 수 있습니다.
=FV(B1/12, B2, -B3)
여기서 B1/12는 연 이자율(이율, B1 셀)을 월 이자율로, B2는 결제 횟수(nper)를, B3는 결제 금액(pmt)을 음수로 표시합니다(일반 계좌에서 빠져나가는 것이므로 음수).
그러나 보증금과 이사할 때 불가피하게 발생하는 기타 비용을 충당하기 위해 총 15,000달러를 모으는 것이 목표이므로 이 목표를 달성하려면 2년간 매달 얼마를 저축해야 하는지 계산해야 합니다. 이때 Excel의 목표 찾기 도구가 유용합니다.
먼저 계산이 포함된 셀(이 경우 B4 셀)을 선택하고 [데이터] 탭 - [가상 분석] - [목표값 찾기]를 클릭합니다. 총 값은 계산이어야 합니다. 이 값을 수동으로 입력하면 목표 찾기가 작동하지 않습니다.
나타나는 목표 찾기 대화 상자에는 세 개의 필드가 있습니다.
- 수식 셀: 현재 합계 계산이 포함된 셀이며, 이 예시에서는 B4 셀입니다. 대화 상자를 시작하기 전에 이 셀을 선택했으므로 자동으로 채워졌습니다.
- 찾는 값: 최종 목표를 달성하기 위해 도달해야 하는 가치입니다. 이 경우에는 15,000입니다.
- 값을 바꿀 셀: 이 계산에서 변경할 수 있는 변수는 월별 저축액(셀 B3)입니다.
[확인]을 클릭하면 Excel에서 앞서 언급한 "값을 바꿀 셀"을 조정하기 전에 여러 번의 계산 반복을 수행합니다. 이 경우, 목표 달성을 위해 2년 동안 매달 610.16달러를 저축해야 한다는 메시지가 표시됩니다.
하지만 이 시점에서 매달 그 금액을 저축할 여유가 없다는 것을 깨달았습니다. 대신 매달 400달러를 계속 저축하기로 하고, 15,000달러 목표액에 도달하면 이사하기로 했습니다. 그래서 이번에는 Excel에서 얼마나 걸릴지 계산해 달라고 요청합니다.
먼저 B3 셀의 목표값 (PMT)을 $400으로 수동으로 재설정합니다. 그런 다음 최종 값 계산이 포함된 셀을 선택하고 [데이터] 탭 - [가상 분석] - [목표값 찾기]를 클릭합니다. '값을 바꿀 셀'은 B4이고, "찾는 값"은 여전히 15000이지만, 변경할 수 있는 셀은 월 셀인 B2입니다.
[확인]을 클릭하면 2.5%의 이자율로 매달 400달러를 지불하여 15,000달러를 절약하는 데 36개월이 조금 넘게 걸린다는 것을 알 수 있습니다.
Excel의 목표 찾기 도구는 저축을 돕는 것뿐만 아니라, 목표를 달성하기 위해 얼마나 많은 제품을 판매해야 하는지, 또는 대출을 감당할 수 있도록 어떤 이자율이 필요한지 알아내는 데에도 도움이 됩니다.
2. 데이터 스냅샷을 촬영하기 위한 카메라 도구
Excel의 카메라 도구를 사용하면 일부 데이터의 스냅샷을 찍어 이미지로 붙여넣을 수 있습니다. 이 이미지는 동적이므로 원본 데이터의 변경 사항을 반영하여 자동으로 업데이트됩니다.
이 도구는 통합 문서의 여러 워크시트에서 데이터를 가져오는 대시보드를 만들 때 유용할 수 있으며 , 대용량 데이터 세트를 작업하면서 주요 정보를 한눈에 보고 싶을 때도 유용합니다. 데이터가 이미지 형태로 복제되므로 스프레드시트 레이아웃에 영향을 주지 않고 Excel 셀 위에 표시됩니다.
이 가이드에서 다루는 모든 도구 중에서 카메라는 리본 메뉴를 통해 바로 접근할 수 없기 때문에 가장 잘 알려지지 않은 도구 중 하나입니다. 대신 빠른 실행 도구 모음 (QAT)에 추가해야 합니다.
리본 오른쪽에 있는 아래쪽 화살표를 클릭합니다. [빠른 실행 도구 모음 감추기]가 표시되면 QAT가 이미 활성화된 것입니다. 하지만 [빠른 실행 도구 모음 표시]가 표시되면 이 옵션을 클릭하여 활성화합니다.
그런 다음 Excel 창 상단의 QAT에서 아래쪽 화살표를 클릭하고 [기타 명령]을 선택합니다.
다음으로, "명령 선택" 드롭다운 메뉴에서 "모든 명령"을 클릭하고 "카메라"로 스크롤하여 선택하세요. 그런 다음 "추가"를 클릭하여 QAT에 추가하고 "확인"을 클릭하여 대화 상자를 닫습니다.
이제 카메라 도구가 QAT에 설치되어 사용할 준비가 되었습니다. Excel 표에 입력된 데이터의 스냅샷을 찍어야 한다고 가정해 보겠습니다. 스냅샷을 찍으려면 해당 데이터가 포함된 셀을 선택하고 QAT에서 [카메라] 아이콘을 클릭합니다.
복사한 데이터의 이미지 버전을 복제할 셀을 클릭합니다. 데이터가 이미지로 복제되므로 핸들을 클릭하고 끌어서 그래픽 크기를 조정할 수 있으며 리본의 그림 서식 탭을 사용하여 스냅샷의 서식을 지정할 수 있습니다.
카메라 도구는 차트나 도형 등 Excel의 다른 요소에도 적용됩니다. 개체 뒤와 주변의 셀을 선택하고 QAT에서 카메라 도구를 클릭하세요. 데이터를 이미지로 붙여넣은 후, 이미지를 잘라 가장자리의 불필요한 공간을 제거하세요.
3. 대규모 데이터 관리를 위한 데이터 그룹
많은 열이 포함된 Microsoft Excel 스프레드시트에서 작업하는 경우, 데이터를 읽기 위해 좌우로 반복해서 스크롤하는 것이 지겨울 수 있습니다. 또한 특정 열이 항상 표시되어야 할 필요가 없는 경우도 있습니다. 다양한 매장의 월별 판매 수치가 포함된 통합 문서가 있고, 가장 오른쪽 열에 연간 총계가 있다고 가정해 보겠습니다.
연간 데이터를 분석하고 수치를 더 쉽게 읽을 수 있도록 B열부터 Q열까지를 숨기면 워크시트에서 A열과 R열이 나란히 배치됩니다. B열부터 Q열까지 선택하고, 선택한 열 머리글 중 하나를 마우스 오른쪽 버튼으로 클릭한 후 "숨기기"를 클릭하고 싶을 수 있습니다.
하지만 이 열들이 숨겨져 있다는 사실을 잊기 쉽고, 다시 숨기기를 해제하는 과정도 번거롭습니다. 대신, 열 B의 헤더를 클릭하고 선택 영역을 Q열로 끌어서 B열부터 Q열까지 선택합니다.
다음으로, [데이터] 탭 - [개요]를 클릭하고 [그룹] 버튼의 상단 절반을 선택합니다.
이제 해당 열 위에 있는 빼기 기호나 그룹 표시줄을 클릭하여 해당 그룹을 숨길 수 있습니다. 각 하위 그룹이 공백이나 합계 열로 구분되어 있는 경우 주요 그룹 내에 하위 그룹을 만들 수도 있습니다.
위와 같은 예를 사용하여 월별 데이터도 축소하고 분기별 및 연간 합계만 표시하려고 한다고 가정해 보겠습니다. 이는 E, I, M, Q 열이 각 하위 그룹 간의 경계 역할을 하기 때문에 가능합니다.
Q1에 대한 하위 그룹을 만들려면 B열부터 D열까지 선택하고 [데이터] 탭에서 [그룹]을 클릭합니다.
이제 B:Q열을 포함하는 기본 그룹이 있을 뿐만 아니라, 스프레드시트에는 B:D열을 포함하는 하위 그룹도 있습니다.
모든 하위 그룹에 개별 그룹 표시선과 빼기 기호가 생길 때까지 나머지 분기에 대해 이 과정을 반복합니다. 그런 다음 4개 하위 그룹의 빼기 기호를 클릭하여 분기별 및 연간 합계만 표시하고, 더하기 기호를 클릭하면 다시 확장할 수 있다는 점을 기억하세요.
빼기 기호와 더하기 기호를 클릭하여 그룹이나 하위 그룹을 축소하거나 확장하는 것 외에도, 그룹 표시줄 왼쪽에 있는 숫자 버튼을 클릭할 수 있습니다. 가장 높은 숫자를 클릭하면 그룹화된 모든 데이터가 표시되고, 가장 낮은 숫자를 클릭하면 생성된 가장 작은 하위 그룹으로 데이터가 축소됩니다.
4. 빠른 데이터 시각화를 위한 사용자 정의 숫자 형식
사용자 지정 숫자 서식 도구는 Excel에서 필자가 가장 좋아하는 숨겨진 기능 중 하나입니다. 시간을 가장 많이 절약해 주기 때문입니다.
새 스프레드시트의 모든 셀에는 일반 숫자 서식이 적용됩니다. 즉, 셀에 너무 큰 10진수 숫자나 0으로 시작하는 숫자를 제외하고는 입력한 대로 숫자가 정확하게 표시됩니다. 그러나 날짜나 백분율과 같은 특정 유형의 숫자를 셀에 삽입하면 Excel에서 이를 인식하고 숫자 서식을 그에 따라 업데이트합니다.
숨겨진 숫자 서식 중 하나는 사용자 지정으로, Excel의 표준 숫자 서식에 나열되지 않은 방식으로 셀 서식을 지정할 수 있습니다. 입력하려는 서식을 적용할 셀을 선택하고 [홈] 탭 - [표시 형식] 그룹의 모서리에 있는 대화 상자 시작 관리자를 클릭합니다. Ctrl + 1키를 눌러도 됩니다.
유형 필드에 입력하는 정보는 서식을 지정하려는 데이터 유형에 따라 달라집니다.
첫 번째 인수는 양수를, 두 번째 인수는 음수를, 세 번째 인수는 0을, 네 번째 인수는 텍스트를 지정합니다. 세미콜론으로 인수를 구분하는 방법에 유의하세요.
양수;음수;0;텍스트
만약 다음과 같이 입력하면, 이는 어떤 유형의 데이터에 대해서도 서식을 지정하지 않았으므로 셀을 비어 있게 표시하라는 것을 Excel에 알려줍니다.
;;;
반면에 다음을 입력해 보세요.
[녹색]#.00;[빨강]#.00;[파랑]G/표준;
양수는 소수점 두 자리까지 표현된 녹색 값으로, 음수는 소수점 두 자리까지 표현된 빨간색 값으로, 0은 파란색 값으로 변환하고, 텍스트 값은 빈 셀로 생성합니다.
Excel의 조건부 서식 도구를 사용하면 사용자 지정 숫자 서식보다 리본에서 더 눈에 띄기 때문에 동일한 결과를 얻는 데 익숙할 수 있습니다. 하지만 조건부 서식은 다양한 규칙을 입력해야 하고 훨씬 더 많은 시간이 소요됩니다.
검정, 빨강, 파랑, 노랑, 초록, 하양, 청록, 자홍 이외의 색상을 사용하여 값의 서식을 지정하려면 색상 이름 대신 대괄호 안에 색상 코드를 입력합니다.
셀의 사용자 지정 숫자 서식에 기호를 추가할 수도 있습니다. 예를 들어 다음과 같이 입력해 보세요.
▲;▼;"=";
양수 대신 위쪽 화살표를 표시하고, 음수 대신 아래쪽 화살표를 표시하며, 0에는 등호를 표시합니다. 참고로, Windows 운영 체제를 사용하는 경우 ▲의 경우 Alt+30을 누르고, ▼의 경우 Alt+31을 누르면 해당 기호를 입력할 수 있습니다.
위의 모든 코드를 조합하여 깔끔하게 서식이 지정된 셀을 만들고 데이터의 시각적인 매력을 향상시킬 수 있습니다. 한 가지 예를 들면 다음과 같습니다.
[녹색]▲#%;[빨강]▼#%;"-";
D2:D12 셀의 사용자 지정 서식을 적용하면 다음과 같은 결과가 생성됩니다.
마치며
Excel의 단축키는 워크플로우 속도를 크게 높여주는 또 다른 숨겨진 기능입니다. Ctrl + C나 Ctrl + V 같은 잘 알려진 것 외에도 다양한 단축키도 알아두면 도움이 됩니다. 꼭 알아두어야 할 엑셀 단축키는 [여기]를 참고하세요.
'Excel' 카테고리의 다른 글
| Excel을 잘 사용하려면 수학 능력이 필요할까? (0) | 2025.09.22 |
|---|---|
| 오늘(9/20 토) 오후에 새로운 콘텐츠가 오픈됩니다 (0) | 2025.09.20 |
| Excel에서 더블클릭을 활용하는 11가지 멋진 팁 (0) | 2025.09.17 |
| Excel의 MOD 함수를 사용하여 실제 문제를 해결하는 방법 (0) | 2025.09.15 |
| [예고] 지저분한 데이터 한 번에 청소! 함수 없이 3분 만에 끝내는 방법 (0) | 2025.09.13 |
