Custom Format
(Mode 2에서 작성 예정)
목차
왜 엑셀 계산이 안 되나 했더니 ‘이것’ 때문?
안녕하세요. 여러분의 엑셀 스트레스를 0으로 만들어 드리는 직장인 엑셀 꿀팁 저장소입니다!
오늘은 엑셀 데이터 시각화의 핵심인 사용자 지정 표시 형식에 대해 알아보겠습니다.
지난 포스팅에서는 목표값 찾기와 시나리오 관리자로 비즈니스 예측 모델을 만드는 법을 배웠습니다.
그런데 아무리 좋은 모델을 만들어도, 기초 데이터가 엉망이면 엑셀은 작동하지 않습니다
혹시 견적서를 작성할 때 “10,000원”이라고 직접 타이핑하고 계신가요? 엑셀 입장에서 이건 숫자가 아니라 ‘문자(Text)’입니다.
그래서 SUM 함수로 합계를 내려고 하면 0이 나오거나 에러가 납니다.
오늘은 데이터의 알맹이(값)는 그대로 둔 채, 겉모습(디자인)만 자유자재로 바꾸는 엑셀의 가면무도회, 사용자 지정 표시 형식(Custom Number Format)의 모든 비밀을 파헤쳐 드립니다.
이 기능을 알면 “10000”이라고 치고 엔터만 쳐도 “금 10,000 원정”으로 자동 변신하는 마법을 부릴 수 있습니다.
표시 형식의 절대 원칙: “화장만 고쳤을 뿐”
본격적인 기술에 앞서 가장 중요한 개념을 잡고 가야 합니다. 엑셀의 데이터는 두 가지 얼굴을 가집니다.
- 실제 값(Value): 수식 입력줄에 보이는 진짜 데이터. (계산에 사용됨)
- 표시 형식(Format): 셀에 보여지는 껍데기. (눈에만 보임)
단축키 [Ctrl + 1]을 눌러 [셀 서식] 창을 띄우고 [표시 형식] 탭으로 가보세요. 여기서 우리는 실제 값을 건드리지 않고, 엑셀에게 “이 숫자를 날짜처럼 보여줘”, “이 숫자를 돈처럼 보여줘”라고 명령을 내릴 수 있습니다.
기초 코드: 0과 #의 차이점 완벽 정리
[사용자 지정] 메뉴에 들어가면 알 수 없는 기호들이 가득합니다. 가장 기초적인 숫자 코드 3대장부터 정리합니다.
1. 코드 ‘0’ (자릿수 채우기)
숫자가 없어도 무조건 자리를 지킵니다. ‘000’이라고 서식을 지정하고 숫자 5를 입력하면 005로 표시됩니다. 사번이나 제품 코드(001, 002)를 만들 때 필수입니다.
2. 코드 ‘#’ (유효 숫자만 표시)
숫자가 없으면 아무것도 표시하지 않습니다. ‘###’이라고 지정하고 5를 입력하면 그냥 5가 나옵니다. 불필요한 0을 보여주기 싫을 때 사용합니다.
3. 코드 ‘,’ (쉼표와 천 단위 구분)
숫자 3자리마다 콤마를 찍어줍니다. 보통 #,##0이라는 코드를 가장 많이 씁니다. (의미: 천 단위마다 콤마를 찍되, 값이 0이면 ‘0’으로 표시해라)
더 다양한 서식 코드가 궁금하다면 MS 공식 사용자 지정 서식 가이드를 참고하세요.
실전 1단계: 숫자 뒤에 단위(원, 개, 명) 붙이기
이제 실무에 적용해 봅시다. 숫자 뒤에 단위를 붙이면서 계산도 되게 하려면?
- 셀을 선택하고 Ctrl + 1을 누릅니다.
- [표시 형식] – [사용자 지정]을 클릭합니다.
- ‘형식(T)’ 입력칸에 이렇게 적습니다.
#,##0"원" - 확인을 누릅니다.
이제 10000만 입력해도 화면에는 10,000원으로 보입니다.
하지만 수식 입력줄을 보면 여전히 10000이라는 숫자만 들어있기 때문에 덧셈, 뺄셈이 완벽하게 작동합니다. “원” 대신 “개”, “명”, “Set” 무엇이든 큰따옴표(“”) 안에 넣으면 됩니다.
실전 2단계: 양수 파랑, 음수 빨강! 4구역의 비밀
사용자 지정 서식에는 세미콜론(;)으로 구분된 4개의 구역이 있습니다. 이 구조만 알면 여러분은 엑셀 마스터입니다.
양수 서식 ; 음수 서식 ; 0일 때 서식 ; 텍스트 서식
예를 들어 [파랑]#,##0;[빨강]-#,##0;"-";@ 라고 입력하면 어떻게 될까요?
- 양수: 파란색으로 천 단위 콤마 표시
- 음수: 빨간색으로 마이너스 기호와 함께 표시
- 0: 숫자 0 대신 하이픈(-) 기호 표시 (깔끔한 보고서를 위해 많이 씁니다)
- 텍스트: 입력된 텍스트 그대로(@) 표시
조건부 서식을 쓰지 않아도, 서식 코드만으로 데이터의 색상을 자동으로 바꿀 수 있는 고급 기술입니다.
고수의 기술: 천 단위 절사(K, M)와 투명 인간 만들기
보고서 단위가 커서 ‘1,000,000원’을 ‘1백만 원’이나 ‘1M’으로 줄여서 보여주고 싶을 때가 있죠? 쉼표(,)의 비밀을 활용하면 됩니다.
1. 천 단위 절사 (뒤에 000 떼기)
서식 코드 맨 뒤에 쉼표를 하나 찍으면 1,000으로 나눈 효과를 냅니다.
#,##0,-> 1,000,000 입력 시 -> 1,000 표시#,##0,,"백만원"-> 1,000,000 입력 시 -> 1백만원 표시 (쉼표 2개는 100만 단위 절사)
2. 데이터 숨기기 (투명 인간)
셀에 데이터는 있는데 화면에는 안 보이게 하고 싶다면? 4구역을 모두 비워버리면 됩니다.
;;;(세미콜론 3개)
이걸 입력하면 셀 안에 무슨 값을 넣어도 화면에는 아무것도 나타나지 않습니다. 비밀번호나 계산용 임시 데이터를 숨길 때 유용합니다.
자주 쓰는 공식 모음 (전화번호, 날짜 요일)
외워두면 퇴근이 빨라지는 필수 코드들입니다.
- 전화번호 자동 하이픈:
01012345678입력 시010-1234-5678로 변환
코드:0##-####-#### - 날짜와 요일 함께 표시:
2024-01-01입력 시1월 1일 (월)로 변환
코드:m"월" d"일" (aaa)(aaaa는 ‘월요일’) - 한글 숫자 표시: 견적서 합계 금액
코드:[DBNum4]G/표준 "원정"-> 일금 일만 원정
주의사항: 눈에 보이는 값이 진짜가 아닙니다
사용자 지정 표시 형식은 강력하지만, ‘착시’를 일으킬 수 있습니다. 화면에는 “100만 원”으로 보이지만 실제 값은 “1000000”일 수도 있고 “999500”(반올림됨)일 수도 있습니다.
정확한 데이터 검증이 필요할 때는 반드시 수식 입력줄을 확인하는 습관을 들이세요. 보이는 것이 전부가 아닙니다.
마무리하며
오늘은 엑셀 데이터의 화장법, 사용자 지정 표시 형식의 기초부터 고급 트릭까지 알아봤습니다.
이제 텍스트로 ‘원’을 적는 하수는 졸업하고, 숫자 형식을 자유자재로 다루는 고수가 되어보세요.
다음 시간에는 고정된 표가 아니라 데이터가 추가될 때마다 차트도 자동으로 늘어나는 마법
동적 범위의 핵심, OFFSET과 INDIRECT 함수로 유동적인 차트 만들기 편으로 찾아오겠습니다. 여러분의 칼퇴를 엑셀 꿀팁 저장소가 응원합니다!