엑셀 셀서식 및 사용자지정서식 사용법 및 실전예제 :: 엑셀 위키

엑셀 사용자 지정 서식 및 셀 서식 알아보기

실전예제 바로가기

목적

엑셀에서 사용되는 다양한 사용자 지정서식 및 셀서식을 알아본 뒤, 실무에서 사용되는 대표서식을 알아봅니다.

반드시 알아야할 대표서식

사용자 지정서식 포스트 상세 설명에 들어가기 전, 실무자라면 아래 서식만큼은 꼭! 기억하세요.

쉬운 예제로 12345.67 과 -54321.67 두개의 양수와 음수를 다양한 방법으로 표시해보겠습니다. 대표 서식인 [ #,##0 ] 만 기억하면 아래와같이 다양한 방법으로 사용자지정서식을 응용할 수 있습니다.

영상 강의

예제파일 다운로드

사용자 지정서식의 3가지 기본 원리

첫째. 사용자 지정서식을 제대로 활용하기 위한 기본규칙

아래 기본 규칙은 숫자를 표시하는 사용자 지정서식의 응용을 위해 반드시 숙지하는 것을 권장합니다.

둘째. 사용자 지정서식에 사용되는 기본 코드

아래 기본 코드는 꼭 암기하지 않아도 괜찮습니다. 이렇게 동작을 하는구나 정도로 알고 넘어가신 뒤, 이후에 필요할 때마다 몇번만 찾아서 사용해보시면 저절로 암기하게 됩니다. (기본코드 표는 예제파일에 같이 첨부하였습니다.)

셋째. 쌍따옴표 없이도 텍스트로 출력되는 기호들

사용자 지정서식에서 셀 안에 입력된 값 이외의 추가로 텍스트를 표기하려면 쌍따옴표(") 안에 값을 넣어줘야 하지만, 아래의 기호들은 쌍따옴표(") 없이 입력해도 바로 텍스트로 출력됩니다.

사용자 지정서식 실전 예제

1. 천단위, 십만단위 절삭하기

사용자지정서식 뒤에 쉼표(,) 만 더해주면 천단위/십만단위를 쉽게 절삭할 수 있습니다. 십만 이상 큰 단위의 숫자를 주로 사용한다면, 특히 보고서를 작성할 때 천단위 절삭 서식을 아주 유용하게 사용할 수 있습니다.

2. 달러/원 화폐기호 추가하기

엑셀은 이미 기본 서식으로 다양한 화폐기호를 제공합니다. 화폐기호를 제공하는 서식은 '통화' 와 '회계' 서식이며, 두개의 차이점은 아래와 같습니다.

3. 숫자 앞에 0 붙이기

재고고유번호 또는 사원번호 등을 표시할 경우, 상황에 따라 실제 입력된 숫자 앞부분에 0을 표시해야 할 때가 있습니다. 그럴 경우 아래의 사용자 지정서식을 사용합니다.

단 주의사항이 있습니다.

사용자지정서식을 사용할 경우 겉으로 표시되는값만 변경됩니다. 따라서 셀에 있는 값을 복사하여 다른곳에 붙여넣기하면 '0'이 빠진채로 붙여넣기가 됩니다. 만약 셀 안에 입력된 원본값을 변경하고자 할 경우 TEXT 함수를 사용하세요.

4. 사용자 지정서식으로 전화번호, 주민번호 표시하기

엑셀의 사용자 지정서식을 활용하여 전화번호/주민등록번호 형태의 값을 표시할 수 있습니다.

5. 음수 괄호안에 표시하기 (양수/음수 개별 서식 적용)

양수와 음수에 각각 다른 사용자지정서식을 적용하려면 세미콜론(;)을 사용합니다. 언더바(_) 를 사용자 지정서식에 사용하면, 언더바(_) 다음에 위치한 문자만큼 공백이 생깁니다. 예를 들어 _(0_) 를 사용자지정서식으로 입력할 경우, 숫자 앞뒤로 괄호() 만큼의 공백이 생깁니다.

6. 0 지우기 또는 0을 하이픈(-)으로 표시하기

엑셀 사용자 지정서식을 활용하면 아주 간단하게 0 을 숨길 수 있습니다. 0을 숨기기 위해서는 사용자지정서식 뒤에 세미콜론(;) 2개만 추가하면 됩니다.

7. 사용자 지정서식 - 퍼센트 기호 표시

사용자지정서식을 활용하면 별도의 계산을 하지 않아도 입력된 값을 퍼센트(%) 수치로 바로 변환할 수 있습니다. 기호 '0' 은 불필요한 자리수도 모두 표시하며, '#' 는 불필요한 0 의 자리수는 숨김처리합니다.

8. 엑셀 사용자 지정서식 - 날짜 서식 활용

엑셀에서는 날짜를 '1900년 1월 1일'을 기점으로 하여 1이 증가하면 하루가 늘어나는 형태로 계산됩니다. 따라서 날짜/시간 서식을 활용하기 위해서는 입력된 값이 반드시 '숫자'여야 합니다. 만약 입력된 값이 '텍스트' 형식일 경우 사용자지정서식이 적용되지 않습니다.

날짜서식에서 자주 사용되는 기호는 아래와 같습니다.

9. 사용자 지정서식으로 글꼴 색깔 설정하기

대괄호[]를 사용하면 글꼴에 다양한 색상을 적용할 수 있습니다. 색상은 [빨강], [파랑], [노랑],... 과 같이 색상명을 괄호안에 직접 입력할수도 있고 또는 아래와 같이 색상별 번호를 사용하여 다양한 색상을 표현할 수 있습니다.

사용자지정서식의 번호별 색상목록은 총 56가지이며 목록은 아래와 같습니다.

예를 들어 셀 안의 값이 양수일 경우 파란색, 음수일 경우 빨간색, 값이 0이나 텍스트일 경우 검정색으로 사용자 지정서식을 적용해보겠습니다.

10. 조건별 셀 서식을 다르게 적용하기

대괄호[]를 활용하여 사용자 지정서식을 조건마다 다르게 적용할 수 있습니다. 다만, 엑셀에서 기본적으로 제공되는'조건부서식'보다는 다소 제한적이며 셀의 배경색이나 테두리에는 서식을 적용할 수 없고 글꼴의 색상이나 표현식 등에만 서식을 적용할 수 있습니다. 엑셀 조건부서식에 대한 자세한 내용은 관련 포스트를 참고하세요.

11. 사용자 지정서식 - 원하는 문자열 반복하기

엑셀 사용자 지정서식에서 별표(*)를 활용하면 셀의 넓이에 상관없이 입력된 값 좌/우로 원하는 문자열을 쉽게 반복하여 표시할 수 있습니다. 만약 함수로 특정 문자열을 반복출력하고자 한다면 REPT 함수를 사용합니다.

12. 문자열 왼쪽/오른쪽 정렬하기

사용자 지정서식의 * (공백)을 활용하면 문자열을 좌/우로 강제 정렬할 수 있습니다. 예를들어 사용자지정서식 뒤에 * (공백)을 입력하면 값이 강제로 '좌측정렬' 됩니다.

13. 사용자 지정서식으로 줄바꿈 추가하기 (%기호 없이 백분율 값 표시하기)

사용자 지정 서식으로 셀에 줄바꿈을 자동으로 적용할 수도 있습니다. 하지만 이 방법은 키보드에 우측 키패드 (위쪽에 가로로 길게 나열된 숫자키가 아닙니다!) 가 있을경우에만 사용가능합니다. '나는 우측키패드가 없는데 꼭 이 기능을 사용해야 된다!' 하시는 분은 AutoHotkey 라는 프로그램으로 상단 숫자키를 강제로 변환해서 입력할 수도 있습니다 ^^*

오토핫키를 사용한 숫자키패드 입력 방법은 아래 포스트에서 자세히 설명드렸습니다.

쉬운 예제로 백분율 값을 퍼센트(%) 기호 없이 표시하는 방법을 알아보겠습니다. 우선 아래 값을 '사용자 지정서식'에 입력합니다.

0과 % 사이에 커서가 깜빡이도록 위치시킨 후, ALT키를 누른채로, 0 -> 0 -> 1 -> 0 을 차례대로 입력합니다. (숫자는 키보드 우측에 위치한 키패드로 입력합니다)

그럼 % 기호가 사용자지정서식의 입력칸 아래로 줄바꿈됩니다. (이 기능은 ALT 단축키를 응용합니다. 엑셀은 이런 꽤 괜찮은 기능을 왜 이스터에그처럼 숨겨놓았을까요..^^;;)

내용 보충합니다.^-^ ALT-0010 대신, 키보드 Ctrl + J 키로도 줄바꿈을 입력하실 수 있습니다.

이후 [맞춤] 탭으로 이동한 뒤, [자동줄바꿈] 에 체크합니다.

이제 백분율 값이 %기호 없이 입력됩니다.

14. 숫자를 문자로 (숫자를 한글로) 변환하기

사용자 지정 서식을 활용하면 숫자를 한글로 손쉽게 변환할 수 있습니다. 아래 서식을 TEXT 함수와 같이 응용할 경우, 계산서에 자주 사용되는 합계금액(예: 일금 24,700원(금이만사천칠백원)) 등의 다양한 서식으로 작성할 수 있습니다.

이 사용자 지정서식을 TEXT 함수와 같이 접목하면 아래와 같이 계산서의 총액을 숫자와 한글로 동시에 표시할 수도 있습니다. 아래 수식을 복사/붙여넣기 한 뒤, '셀'을 실제 셀 주소로 변경합니다.

15. 엑셀 양력 날짜를 음력으로 변환하기

사용자지정서식과 엑셀 로케일서식을 응용하면 양력날짜를 음력으로 아주 손쉽게 변경할 수 있습니다.

음력 변환뿐만 아니라, 로케일서식을 응용하여 각국의 다양한 언어와 달력으로도 날짜를 변환할 수도 있는데요. 예를들어, 대만의 중화민국력 또는 대한민국의 단군력 등으로도 변경할 수 있습니다. 또는 중국어로케일을 적용하면 아래와 같이 결과값을 한문으로도 표시할 수 있습니다.

로케일 서식에 대한 자세한 설명은 아래 관련 링크를 참고하세요.

16. 엑셀 숫자를 만단위 / 억단위 표시하기

서양권에서는 숫자를 매 천단위 기준으로 구분합니다. (1 Thousand(천), 1 Million(100만), 1 Billion(10억).. 등) 그리고 천단위 구분은 전세계 표준으로 사용되고 있는데요.

하지만 우리나라를 포함한 몇몇 동양권에서는 실생활에서 숫자를 '만단위' 기준으로 구분하여 사용합니다.. (1만원(10^4), 1억원(10^8), 1조(1^12)..)

그리고 엑셀은 전세계 표준인 '천단위' 절삭만 기본으로 지원하고 있는데요. 만약 엑셀에서 '만단위' 절삭이 필요할 경우 어떻게 해야 할까요?

셀서식의 '줄바꿈' 기능을 응용하면 손쉽게 만단위 절삭, 억단위 절삭을 구현할 수 있습니다.해당 서식에서 사용된 '셀서식 줄바꿈 기능'은 해당 게시글의 13번 예제, #사용자 지정서식으로 줄바꿈 추가하기를 참고하세요.

내용 보충합니다.^-^ ALT-0010 대신, 키보드 Ctrl + J 키로도 줄바꿈을 입력하실 수 있습니다.

동일한 원리로 아래 서식을 이용하면 '~만 ~천원' 으로 숫자를 표시할 수 있습니다.

만약 천만원 이상일 때, 천단위 구분기호를 표시해서 [ 1,321만원 ] 으로 나타내려면 어떻게 해야 할까요? 아래와 같이 응용할 수도 있습니다.

이 서식을 약간만 더 응용하면, 아래와 같이 억단위를 절삭하여 숫자를 표기할 수도 있습니다.