엑셀에서 숫자나 날짜를 원하는 형태로 표시하려면, 예를 들어
- 숫자 10000을 10,000으로 표시하거나
- 6/3을 입력하고 2019-06-03으로 표시하려면
셀서식을 지정하거나 TEXT 함수를 사용하는 방법이 있습니다. 이번 글에서는 셀서식을 사용해서 원하는 형태로 값을 표시하는 방법을 알아보겠습니다.
셀 서식은 다음 세 가지 방법으로 지정할 수 있습니다.
- 리본메뉴 표시형식에서 셀서식 지정
- 셀서식 대화상자에서 셀서식 지정
- 사용자지정 서식을 만들어서 셀서식 지정
1. 리본메뉴 표시형식에서 셀서식 지정
리본메뉴의 표시형식을 이용하면 가장 간단하게 셀서식을 지정할 수 있습니다.
01 ① 값이 입력된 셀 범위 [C3:E6]을 선택한 후
② [홈] 탭 → [표시 형식] 그룹에서 '일반'으로 표시된 목록 상자의 내림 버튼을 눌러 원하는 서식을 선택합니다.
여기에서는 [회계]를 선택했습니다.
02 ‘회계’ 표시 형식의 결과가 표시됩니다.
'회계' 형식 이외에도 다음과 같은 표시 형식이 있습니다.
- 일반 - 특정 서식 없음
- 숫자 - 숫자로 표시
- 간단한 날짜 - "2021-06-09" 형태로 표시
- 자세한 날짜 - "2021-06-09 일요일" 형태로 표시
- 시간 - "오전 12:00:00" 형태로 표시
- 백분율
- 분수
- 지수
- 텍스트
셀서식을 지정하는 목록 상자 밑에는 각국 통화표시, 백분율표시,천단위콤마표시,소수점자리수 늘리기/줄이기 기능이 있으므로 값 선택 후 클릭하면 바로 적용됩니다.
2. 셀서식 대화상자에서 셀서식 지정
리본 메뉴의 [표시 형식]에서 셀 서식을 간단하게 바로 적용할 수 있지만, 좀 더 복잡한 경우(예를 들어 숫자가 음수일 때 빨간색으로 표시하는 경우)에는 지정할 수 없는 단점이 있습니다. 이런 경우 셀 서식을 지정하는 대화상자에서 선택하면 됩니다.
01 ① 값이 입력된 셀 범위 [C3:E6]을 선택한 후 ② [Ctrl+1]을 누르거나 마우스 오른쪽 버튼을 눌러 셀 서식을 선택합니다.
③ [범주]에서 숫자를 선택하고 ④ 오른쪽 [음수]에서 -1,234를 선택합니다.
02 음수 표시 형식의 결과가 표시됩니다.
3. 사용자지정 서식을 만들어서 셀서식 지정
만약 [셀 서식] 대화상자에 원하는 셀서식이 없다면 사용자지정 서식을 수정하거나 직접 만들어서 적용할 수 있습니다.
다음 그림과 같이 ① [셀 서식] 대화상자의 [범주]에서 '사용자 지정'을 선택하고 ② 오른쪽에 표시되는 형식 중에 하나를 선택하면 목록 위에 ③ '#,##0_' 와 같이 난해한 문자들이 보입니다.
이 문자를 '서식코드'라고 부르는데 이 서식코드를 수정하여 원하는 형태의 '사용자지정 서식'을 만들 수 있습니다.
간단한 서식코드 예시
셀에 숫자 10000을 입력하고 [셀 서식] 대화상자에서 다음과 같이 서식코드를 입력하면 각각의 서식코드에 해당하는 형태로 값이 표시됩니다.
서식코드 구조
위의 예에서는 간단한 서식코드를 살펴보았는데 서식코드는 다음의 예와 같이 4개의 섹션으로 구성됩니다. 섹션과 섹션사이는 세미콜론(;)으로 구분합니다.
4개의 섹션을 전부 적용하면 4가지의 경우(입력된 값이 양수일 때, 음수일 때, 0일 때, 문자일 때)가 한 번에 적용되어 마치 조건부 서식을 이용하는 것처럼 값의 상태에 따라 모양을 다르게 표시할 수 있습니다.
위의 서식코드 "[파랑]_-* #,##0_-;[빨강]* -#,##0_-;_-* "0"_-;_-@"고객님"_-" 을 적용하면 다음과 같이 각각의 입력 값에 따라 4가지 경우가 표시됩니다.
이 암호 같은 서식을 풀이해 보면 각 섹션의 의미는 다음과 같습니다.
섹션 | 서식코드 | 의미 |
섹션1 | [파랑]_-* #,##0_- | 값이 양수일때 파란색으로, 천단위 콤마를 표시 |
섹션2 | [빨강]* -#,##0_- | 값이 음수일때 빨간색으로, 음수기호와 함께 천단위 콤마를 표시 |
섹션3 | _-* "0"_- | 값이 0일때 0으로 표시하되 맨 앞에는 _(밑줄)너비 만큼의 공백을 넣고 우측정렬하고, 맨 뒤에는 _(밑줄)너비 만큼의 공백을 넣어서 표시 |
섹션4 | _-@"고객님"_- | 값이 문자일 때 맨 앞에는 _(밑줄)너비 만큼의 공백을 넣고 입력된 값에 '고객님'이라는 값을 붙이고 맨 뒤에는 _(밑줄)너비 만큼의 공백을 넣어서 표시 |
서식코드를 네 개 다 쓸 필요는 없고 필요에 따라 한두 개만 써도 됩니다.
- 한 개의 코드 섹션만 지정할 경우 이 섹션은 모든 숫자에 적용됩니다.
- 두 개의 코드 섹션만 지정할 경우 첫 번째 섹션은 양수 및 0에 적용되고 두 번째 섹션은 음수에 적용됩니다.
- 특정 코드 섹션을 건너뛰고 그다음 코드 섹션을 포함하려면 건너뛸 부분에 세미콜론(;)을 사용합니다.
실무사례)판매 실적 상승률에 세모 증감 표시
다음과 같이 %로 표시되는 숫자에 세모 증감 표시는 조건부 서식의 '아이콘 집합'을 이용할 수도 있지만 셀 서식을 지정하여 표시할 수도 있습니다.
<조건부 서식의 아이콘 집합으로 증감 표시>
<셀 서식으로 증감 표시>
① 서식을 적용할 '상승률’의 셀 범위 [E5:E12]를 선택하고 ② [Ctrl+1]을 누르거나 마우스 오른쪽 버튼을 눌러 셀 서식을 선택합니다.
① [셀 서식] 대화상자의 [범주]에서 사용자 지정을 선택하고 ② [형식]에 다음 서식 코드를 입력합니다.
[파랑]▲_-* 0.0%_-;[빨강]▼* -0.0%_-;_-* 0.0%_-;
서식이 적용되면 다음과 같이 증감 표시가 됩니다.
서식 풀이 :
사용된 서식코드는 다음과 같이 3개의 섹션으로 구성되어 있습니다.
섹션 | 서식코드의미 | 의미 |
섹션1 | [파랑]▲_-* 0.0%_- | 값이 양수일때 파란색으로 표시하되 ▲를 앞에 붙이고 숫자는 소수점 첫째 자리까지 %로 표시 |
섹션2 | [빨강]▼* -0.0%_- | 값이 음수일때 빨간색으로 표시하되 ▼를 앞에 붙이고 숫자는 음수로 소수점 첫째 자리까지 %로 표시 |
섹션3 | _-* 0.0%_- | 값이 0일때 0.0%으로 표시하되 맨 앞에는 _(밑줄)너비 만큼의 공백을 넣고 우측정렬하고, 맨 뒤에는 _(밑줄)너비 만큼의 공백을 넣어서 표시 |
서식코드 종류
숫자 및 문자표시용 서식코드
숫자 및 문자를 표시할 때 사용하는 서식코드는 다음과 같습니다. 단독으로 사용할 수도 있지만 다른 코드 값과 조합하여 많이 사용됩니다.
문자 | 의미 |
# | 숫자 하나 또는 여러 개를 의미, 유효 자릿수만 표시(#으로 표시한 서식의 길이보다 입력한 값이 짧은 경우에는 값의 길이만큼 표시됨) |
0 | 숫자 하나를 의미, 0으로 표시한 서식의 길이보다 입력한 값이 짧은 경우 나머지는 0으로 채움 |
? | 숫자 하나를 의미, ?로 표시한 서식의 길이보다 입력한 값이 짧으면 나머지는 공백으로 채움, 소수점을 고정된 위치에 표시할 때 사용할 수 있다. |
, | 천 단위 표시용 쉼표(콤마) |
_(밑줄) | 숫자에서 공백을 표시할 때 사용, 밑줄 다음의 문자와 같은 너비의 공백을 만들 때 사용(예: “_)”을 입력하면 “)”의 문자크기만큼 공백이 생김) |
@ | 문자를 그대로 출력, @을 2번 반복하면 입력된 문자를 2번 반복해서 출력 |
* | * 뒤의 문자를 셀 너비에 맞게 반복 |
< 문자별 서식코드 적용 예 >
날짜/시간 표시용 서식코드
날짜 및 시간을 표시할 때 사용하는 서식코드입니다.
- y,m,d : 년,월,일을 표시
- a : 요일을 표시
- h : 시간을 표시
- m : 분(h(시간) 또는 s(초)와 함께 사용해야 분으로 표시, 단독으로 사용시 월로 표시됨)
- s : 초를 표시
- AM/PM, A/P : 오전/오후를 표시
< 날짜/시간 서식코드 적용 예 >
< 날짜/시간 서식코드 조합하여 적용한 예 >
분수 표시
분수는 숫자 서식코드를 조합하여 표시합니다.
- # : 숫자에서 1이상인 부분을 표시
- / : 분수 표시 문자
- ? : 숫자에서 1보다 작은 부분을 표시
서식코드를 조합하여 분수를 입력할 수도 있지만 [셀 서식] 대화상자에서 [범주]를 '분수'로 선택해서 편리하게 입력할 수 있습니다.
기타(주민번호,한글/한자 숫자 등) 표시
다음과 같이 우편번호, 전화 번호, 주민번호, 숫자 한글, 숫자 한자 표시를 할 수 있습니다.
- 00000 : 우편번호
- [<=999999]####-####;(0##) ####-#### : 전화 번호(국번 4자리)
- [<=9999999]###-####;(0##) ###-#### : 전화 번호(국번 3자리)
- 000000-0000000 : 주민등록번호
- [DBNum4][$-ko-KR]G/표준 : 숫자 한글
- [DBNum1][$-ko-KR]G/표준 : 숫자 한자
- [DBNum2][$-ko-KR]G/표준 : 숫자 한자-갖은자
위와 같이 서식코드를 조합하여 입력할 수도 있지만 [셀 서식] 대화상자에서 [범주]를 '기타'로 선택해서 편리하게 입력할 수 있습니다.
단, '우편 번호'는 과거에 사용하던 6자리 서식으로 되어 있으므로 위의 예처럼 5자리 서식으로 바꾸어서 사용해야 합니다.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
- 엑셀에서 값을 입력하고 표시하는 원리
- 셀서식을 지정하여 원하는 형태로 표시하기
- 엑셀에서 복사하고 붙여넣기의 모든 것
- 엑셀 자동 채우기의 모든 것
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
안녕하세요? 좋은 강의해주셔서 많이 배우고 있습니다.
궁금한 사항이 있어서 질문드리면 위의 서식코드 중
[파랑]_-* #,##0_-;[빨강]* -#,##0_-; 여기에서 음수의 앞 표시기호로 -는 이해합니다만, 그외에 -*, *, _-는 무슨 의미이며 왜 필요한가요?
숫자앞에 '_'뒤의 '-' 만큼의 공백을 만들되 '*' 뒤의 문자(즉, 공백)만큼 반복 -> 따라서 숫자 앞에 공백을 만들어 줍니다.
이렇게 하면 우측정렬, 좌측 정렬 관계 없이 무조건 숫자가 우측 정렬됩니다(숫자 앞에 공백이 만들어 지므로).
'_-* '를 지우고 숫자를 좌측 정렬해 보면 숫자가 좌측으로 정렬되고, '_-* '를 그대로 유지하고 좌측 정렬해도 아무런 변화없이 우측으로 정렬되는 것을 확인할 수 있습니다.
본문에 기호별 자세한 설명있으니 참고하세요.
안녕하세요. 좋은 글 보고 궁금즘이 생겨 글 남깁니다.
단순히 양수표시를 * #,##0.00_-라고 써도 _-* #,##0.00_-와 차이점이 없는지 궁금합니다.
굳이 _-를 넣어 -만큼 공백을 만들어두는 이유가 무엇인지 알고싶습니다.
안녕하세요. _-를 넣어 -만큼 공백을 만들어두는 이유는 공백이 없으면 다른 셀의 값과 너무 근접해 있으므로 가독성이 떨어지므로 잘 구분하기 위해 넣습니다.
이해하기 쉽게 설명해주셔서 감사합니다.