엑셀 사용자 지정 셀서식으로 보고서 숫자와 색상을 자동 표시하는 방법

TL;DR

Ctrl+1의 사용자 지정 표시형식을 활용하면 원본값을 바꾸지 않고 천·백만 단위, 소수점, 백분율, 날짜·시간, 전화번호 형식을 일괄 적용할 수 있습니다. 0과 #, ?, @, *, 밑줄, 세미콜론, 대괄호 조건을 조합하면 불필요한 0과 여백을 제어하고 양수·음수·특정 범위의 색상까지 구분해 PPT용 보고서 편집을 줄일 수 있습니다.

Transcript

자 오늘은 업무에서 이렇게 방대한 양의 보고 양식 데이터가 있을 때 백만원 단위로 숫자 끊어읽기 또는 +/- 값에 따라서 다른 색깔로 칠하기 값이 특정 범위 안에 들어올 때 서로 다른 색깔로 표시하기와 같은 서식을 손으로 일일이 지정하고 계신 분들을 위해서 엑셀 셀서식을 정복하는 시간을 가져보려고 합니다 먼저 영상 아래 본문을 펼쳐서 엑셀파일을 다운로드 받아 주시 고요 자 먼저 가장 많이 쓰이는 숫자 서식을 알아 보고요 그다음 텍스트와 날짜/시간을 다루는 방법도 알아볼게요 자 여기 원본값이 있습니다 다 숫자고요 여기서 f2키를 누르면 왼쪽 값을 따오도록 이미 수식을 지정해 놓은 상태입니다 자 여기서 우리가 표시형식을 바... Read More

Key Insights

• 0은 지정한 자릿수의 0을 유지하지만 #은 불필요한 0을 숨기므로, 값 0과 공란을 구분하려면 마지막 자리에 0을 사용해야 한다.
• 사용자 지정 표시형식은 셀의 원본 숫자나 수식을 변경하지 않고 화면 표시만 바꾸므로, 같은 값을 보고서 목적에 맞게 다양하게 표현할 수 있다.
• 상단의 쉼표 표시형식은 뒤쪽에 공백을 만들 수 있어 PPT로 복사한 뒤 수정이 필요할 수 있으므로, 사용자 지정 형식을 쓰는 편이 제시된다.
• 숫자 형식의 쉼표 하나는 표시 단위를 천만큼 줄이고 쉼표 두 개는 다시 천만큼 줄여, 1000000을 1백만처럼 표현할 수 있다.
• 0.0% 같은 백분율 형식을 적용하면 0.1234가 12.3%로 표시되며, 소수점 아래 0의 개수로 표시 자릿수를 조절할 수 있다.
• @는 입력된 텍스트 자체를 뜻하므로 @ 뒤에 ‘님’을 붙이거나 @를 반복해도 원본 텍스트는 그대로 유지된다.
• 세미콜론으로 구분한 세 구간은 차례로 양수, 음수, 0의 표시형식이며, 각 구간에 부호와 대괄호 색상을 따로 지정할 수 있다.
• 날짜와 시간은 일련번호로 저장되므로 y·m·d, aaa·aaaa, h·m·s, AM/PM을 조합해 연월일과 요일, 시간을 선택적으로 표시한다.
• 🎬 이 채널의 다른 영상:
  • 소기업은 어떻게 업무 매뉴얼로 신입 인수인계를 자동화할까?
  • COUNTIF·SUMIF·VLOOKUP과 피벗테이블로 업무 보고서를 만드는 방법
  • 직원이 스스로 움직이게 하는 업무 요청 시스템은 어떻게 설계할까?
• 📺 이 채널: 공여사들

Questions & Answers

Q: 엑셀에서 0과 # 사용자 지정 표시형식은 어떻게 다른가요?

0은 지정된 자릿수를 반드시 표시하고, #은 불필요한 0을 표시하지 않습니다. 예를 들어 값이 0일 때 #만 사용하면 값이 사라져 공란처럼 보일 수 있지만, 마지막 자리에 0을 둔 숫자 형식을 사용하면 실제 0을 화면에서도 0으로 구분할 수 있습니다.

Q: 엑셀 숫자를 천 단위로 구분하면서 0도 표시하려면 어떻게 하나요?

Ctrl+1을 눌러 셀 서식의 사용자 지정으로 이동한 뒤, 천 단위 구분과 마지막 0이 포함된 숫자 형식을 적용합니다. 마지막 자리를 #이 아니라 0으로 두면 값이 0인 셀도 빈칸처럼 사라지지 않으며, 여러 범위에는 서식 복사를 이용해 같은 형식을 적용할 수 있습니다.

Q: 엑셀 숫자를 천 또는 백만 단위로 축약해 표시하는 방법은 무엇인가요?

사용자 지정 숫자 형식에서 끝부분에 쉼표를 하나 넣으면 천 단위만큼, 두 개 넣으면 다시 천 단위만큼 표시 규모를 줄일 수 있습니다. 뒤에 k나 백만 같은 문자를 붙이면 원본 숫자 1000000을 1000k 또는 1백만처럼 보여줄 수 있으며, 원본값은 변하지 않습니다.

Q: 엑셀에서 전화번호의 맨 앞 0을 유지하려면 어떻게 하나요?

사용자 지정 표시형식에 000-0000-0000을 입력하면 됩니다. 0은 해당 자릿수를 반드시 표시하므로 숫자로 입력하면서 사라진 맨 앞자리도 화면에서는 복원되어, 1022223333을 010-2222-3333 형태로 표시할 수 있습니다.

Q: 엑셀에서 소수점 자릿수를 맞추되 불필요한 0을 표시하지 않으려면 어떻게 하나요?

표시 목적에 따라 0, #, ?를 구분해 사용합니다. 0은 부족한 자릿수를 0으로 채우고, #은 불필요한 0을 숨기며, ?는 숫자를 채우지 않고 해당 자릿수만큼 공간을 남깁니다. 따라서 값의 유효 자릿수를 과장하지 않으면서 소수점 위치를 맞추려면 ?가 적합합니다.

Q: 엑셀에서 양수와 음수를 서로 다른 색과 부호로 표시하려면 어떻게 하나요?

사용자 지정 표시형식을 세미콜론으로 나누어 양수, 음수, 0의 형식을 차례로 작성합니다. 양수 구간에는 파랑과 + 부호를, 음수 구간에는 빨강과 - 부호를 대괄호 색상과 함께 지정하고, 마지막 구간에는 0을 입력하면 세 종류의 값을 자동으로 구분할 수 있습니다.

Q: 엑셀에서 특정 증감률 범위에만 색상을 적용하려면 어떻게 하나요?

사용자 지정 형식의 대괄호 안에 값 조건과 색상을 지정하면 됩니다. 제시된 사례에서는 -0.1보다 작은 값은 빨강, 0.1보다 큰 값은 파랑으로 설정하고 나머지는 일반 0.0%로 표시합니다. 다만 이런 조건식이 복잡하다면 조건부 서식을 사용하는 방법이 더 간편할 수 있습니다.

Q: 엑셀 셀에서 부호와 숫자를 양쪽으로 벌려 정렬하려면 어떻게 하나요?

부호 다음에 *와 공백을 넣어 남은 셀 너비를 공백으로 채우면 됩니다. *는 바로 다음 문자를 빈 영역 전체에 반복하는 기호이므로, 셀 너비가 달라져도 부호는 앞쪽에 두고 숫자는 뒤쪽에 배치하는 보고서 형태를 유지할 수 있습니다.

Q: 엑셀 텍스트 뒤에 ‘님’ 같은 문자를 자동으로 붙이려면 어떻게 하나요?

사용자 지정 표시형식에서 원본 텍스트를 뜻하는 @ 뒤에 공백과 ‘님’을 입력합니다. 그러면 실제 셀 값은 김과장처럼 그대로 유지되지만 화면에는 김과장 님으로 보입니다. @를 여러 번 배치하면 같은 원본 텍스트를 반복해 표시하는 것도 가능합니다.

Q: 엑셀 날짜와 요일을 원하는 형식으로 표시하려면 어떻게 하나요?

날짜 일련번호에 y, m, d와 요일 기호를 조합한 사용자 지정 형식을 적용합니다. yy는 두 자리 연도, yyyy는 네 자리 연도이며, mm과 dd는 한 자리 값 앞의 0도 표시합니다. 한국어 요일은 aaa로 한 글자, aaaa로 토요일 같은 전체 이름을 표시할 수 있습니다.

Q: 엑셀 시간에 오전과 오후를 함께 표시하려면 어떻게 하나요?

사용자 지정 형식에서 h와 m으로 시와 분을 지정하고 뒤에 AM/PM을 추가합니다. h, m, s는 각각 시, 분, 초를 나타내며, 필요한 항목만 조합할 수 있습니다. 예시의 오전 10시 15분 값에는 시간과 분 뒤에 AM이 표시됩니다.

Q: 엑셀 서식을 적용한 셀을 PPT에 붙여 넣으면 어떻게 표시되나요?

메모장이나 PPT로 복사하면 일반적으로 화면에 보이는 표시형식에 따라 값이 전달됩니다. 따라서 백만 단위 축약, 날짜 형식, 부호와 공백도 보고서에 반영될 수 있습니다. 특히 기본 쉼표 표시형식의 뒤쪽 공백은 함께 붙을 수 있으므로 사용자 지정 형식을 미리 설계하는 방법이 제시됩니다.

Summary & Key Takeaways

• 숫자 표시의 핵심은 0과 #의 차이다. 0은 해당 자릿수를 반드시 표시하지만 #은 불필요한 0을 숨긴다. 따라서 값 0과 빈칸을 구분하며 천 단위로 읽히게 만들 때는 마지막 자리를 0으로 둔 형식을 사용하고, 소수점 자릿수 정렬에는 0, #, ?를 목적에 맞게 선택한다.

• 사용자 지정 표시형식은 값 자체가 아니라 화면에 보이는 모양을 바꾼다. 쉼표를 추가해 천 또는 백만 단위 이하를 생략하고, %, 전화번호 구분자, @ 뒤의 호칭, 날짜의 y·m·d, 시간의 h·m·s와 AM/PM 등을 조합할 수 있다. *, 밑줄은 반복 문자나 공백 정렬에 활용된다.

• 실무 보고서에서는 세미콜론으로 양수, 음수, 0의 형식을 나누고 대괄호로 색상이나 값 조건을 지정한다. 판매실적은 백만 단위로 축약하고, 증감은 부호와 색상을 구분하며, 증감률은 -10% 미만과 10% 초과를 다른 색으로 표시할 수 있다. 복잡한 조건은 조건부 서식이 더 간편할 수 있다.