이 글에서는 엑셀(Excel)에서 색상을 활용해 데이터 유효성 검사(Data Validation)를 사용하는 방법을 알아보겠습니다. 데이터 유효성 검사는 셀에 입력할 수 있는 값을 제어하는 엑셀의 기능으로, 잘못된 정보 입력 시 오류 메시지를 표시하도록 설정할 수도 있습니다. 여기에 색상을 함께 활용하면 작업 속도와 정확도가 크게 향상됩니다.
예제로는 일련번호, 응시자 이름, 취득 점수, 생년월일, 응시자 코드로 구성된 데이터셋을 사용합니다. 각 열에 서로 다른 방법을 적용하여 엑셀에서 색상과 함께 데이터 유효성 검사를 구현하는 과정을 살펴보겠습니다.
색상과 함께 데이터 유효성 검사를 사용하는 4가지 방법
방법 1. 정수 조건으로 데이터 유효성 검사 및 색상 적용하기
이 방법에서는 정수(Whole Number) 조건으로 데이터 유효성 검사를 설정하고, 이후 조건부 서식(Conditional Formatting)을 이용해 색상을 입히는 방법을 보여드립니다.
단계:
채용 시험 결과 데이터셋에 일련번호, 응시자 이름, 취득 점수 열이 있다고 가정해 보겠습니다. 아직 비어 있는 취득 점수 열에 데이터 유효성 검사를 적용하여, 40~100 사이의 숫자만 입력할 수 있도록 제한하겠습니다.
- 먼저 데이터 유효성 검사를 적용할 열을 선택합니다. 여기서는 취득 점수 열을 선택했습니다.
- 그런 다음 데이터 탭 >> 데이터 도구 >> 데이터 유효성 검사를 클릭합니다.
- 기본적으로 설정 탭이 열립니다. 허용 상자를 클릭하고 정수를 선택합니다.
- 이어서 데이터 상자를 클릭하면 다양한 옵션이 나타납니다. 우리의 조건에 맞게 사이(Between) 옵션을 선택하여 40~100 사이의 값만 허용하도록 합니다.
- 사이를 선택한 후 최소값 상자에 40, 최대값 상자에 100을 입력합니다.
- 그리고 확인을 클릭합니다.
- 이제 취득 점수 열을 채워 봅니다.
- 여기서 주목할 점은, 40 미만 또는 100 초과의 숫자를 입력하려고 하면 오류 메시지가 표시된다는 것입니다.
- 아래 이미지에서 D3 셀에 34를 입력하려고 하자 오류 메시지가 나타나, 조건에 맞지 않는 숫자의 입력이 차단되는 것을 확인할 수 있습니다.
- 이제 나머지 취득 점수 열을 채웁니다.
단계:
이제 데이터 유효성 검사가 적용된 열에 색상을 표시하고 싶다고 가정해 보겠습니다. 점수가 40~59 범위면 노란색, 60~100 범위면 녹색으로 표시하겠습니다. 이 작업에는 조건부 서식 기능을 사용합니다.
- 먼저 색상을 적용할 열을 선택합니다. 여기서는 취득 점수 열을 선택했습니다.
- 그런 다음 홈 탭 >> 조건부 서식 >> 새 규칙을 차례로 클릭합니다.
- 새 규칙을 클릭하면 여러 옵션이 표시되는데, 그중 적합한 것을 선택하면 됩니다.
- 여기서는 다음을 포함하는 셀만 서식 지정을 선택했습니다.
- 그다음 규칙 설명 편집 부분으로 이동합니다.
- 기본값으로 첫 번째 상자는 셀 값으로 되어 있으며, 필요에 따라 변경할 수 있습니다.
- 사이를 선택한 후 각 상자에 한계값으로 40과 59를 입력합니다.
- 그다음 서식을 클릭합니다.
- 이제 셀에 색상을 채우기 위해 채우기 탭 >> (원하는 색상 선택) >> 확인 순으로 진행합니다.
- 두 번째 대화 상자가 닫히면, 첫 번째 대화 상자에서 다시 확인을 클릭합니다.
- 이제 취득 점수 열에서 40~59 사이의 값을 가진 모든 셀이 노란색으로 표시되는 것을 확인할 수 있습니다.
- 동일한 절차로 60~100 범위에 녹색을 적용했습니다.
- 이제 색상만 보고도 점수 범위를 쉽게 구분할 수 있습니다.
대화 상자에서 정수 대신 십진수 옵션을 선택해도 동일한 절차를 적용할 수 있습니다.
더 읽어보기: 엑셀에서 여러 조건에 대한 사용자 지정 데이터 유효성 검사 적용하기 (4가지 예제)
방법 2. 데이터 유효성 검사로 색상이 있는 드롭다운 목록 만들기
이 방법에서는 데이터 유효성 검사를 이용해 드롭다운 목록을 만들고, 여기에 색상을 입히는 방법을 소개합니다.
단계:
앞선 예제와 같은 데이터셋에 등급(Grade) 열이 추가되어, 점수 범위에 따라 응시자의 등급을 입력한다고 가정해 보겠습니다. 드롭다운의 소스가 될 등급-점수 테이블이 데이터셋 옆에 준비되어 있습니다.
- 먼저 데이터 유효성 검사를 적용할 등급 열을 선택합니다.
- 앞서 설명한 단계에 따라 데이터 유효성 검사 대화 상자를 연 후, 유효성 검사 조건에서 목록(List)을 선택하여 드롭다운을 생성합니다.
- 그다음 원본(Source)을 클릭해 소스를 지정합니다.
- 마우스로 원본 범위를 선택합니다.
- 여기서 원본은 데이터셋 옆 점수-등급 테이블의 등급 값들입니다.
- 그다음 원본 버튼을 클릭합니다.
- 데이터 유효성 검사 대화 상자에서 확인을 클릭합니다.
- 이제 등급 열의 모든 셀에 드롭다운 메뉴가 생긴 것을 볼 수 있습니다.
- 드롭다운 메뉴를 열어 각 셀에 해당하는 등급을 선택합니다.
- 완성된 등급 열입니다.
단계:
이제 등급별로 서로 다른 색상을 지정하고 싶다고 가정해 보겠습니다. 이 역시 조건부 서식 기능으로 해결할 수 있습니다.
- 앞선 단계에 따라 조건부 서식 대화 상자를 엽니다.
- 그다음 특정 텍스트 >> 포함을 선택하고 원본 버튼을 클릭합니다.
- 이제 원본을 하나씩 선택합니다.
- 먼저 A+ 등급에는 녹색을 지정하겠습니다.
- A+ 셀만 선택한 후 원본 버튼을 클릭합니다.
- 그다음 서식을 클릭합니다.
- 채우기 탭 >> (원하는 색상 선택) >> 확인 순으로 진행합니다.
- 첫 번째 대화 상자에서 다시 확인을 누릅니다.
- 이제 A+가 입력된 등급 열의 셀이 녹색으로 표시됩니다.
- 각 등급마다 같은 방식을 반복하면, 등급별로 다른 색상이 적용된 완성된 데이터셋을 얻을 수 있습니다.
더 읽어보기: 엑셀에서 다중 선택이 가능한 데이터 유효성 검사 드롭다운 목록 만들기
관련 자료:
- 엑셀 데이터 유효성 검사 영숫자만 허용하기 (사용자 지정 수식 활용)
- 다른 셀 값을 기준으로 하는 엑셀 데이터 유효성 검사
- 엑셀에서 표로 데이터 유효성 검사 목록 만드는 방법 (3가지 방법)
- Excel VBA로 데이터 유효성 검사 목록의 기본값 설정하기 (매크로 및 사용자 폼)
방법 3. 날짜 조건으로 데이터 유효성 검사 및 색상 적용하기
이 방법에서는 날짜(Date) 유형의 데이터 유효성 검사에 색상을 활용하는 방법을 보여드립니다.
단계:
일련번호, 응시자 이름, 생년월일(DoB) 열로 구성된 데이터셋이 있다고 가정해 보겠습니다. 생년월일을 기준으로 응시자를 선별해야 하며, 1997년 1월 1일부터 2003년 1월 1일 사이에 출생한 응시자만 자격이 됩니다. 여기에 날짜 유형의 데이터 유효성 검사를 적용하겠습니다.
- 먼저 데이터 유효성 검사를 적용할 생년월일 열을 선택합니다.
- 앞선 절차대로 데이터 유효성 검사 대화 상자를 엽니다.
- 그다음 날짜 >> 사이 >> 시작 날짜 >> 종료 날짜 >> 확인 순으로 설정합니다.
- 이제 유효한 날짜만 입력 가능한 생년월일 열이 완성되었습니다.
단계:
- 생년월일 열을 두 가지 범주로 나눠 색상을 입히고 싶다고 가정해 보겠습니다. 1997년 1월 1일 ~ 1999년 12월 31일 출생자는 주황색, 2000년 1월 1일 ~ 2003년 1월 1일 출생자는 녹색으로 표시합니다.
- 생년월일 열을 선택합니다.
- 앞선 방법에 따라 새 서식 규칙 대화 상자를 엽니다.
- 그다음 셀 값 >> 사이 >> 1997-01-01 >> 1999-12-31 >> 서식 순으로 설정합니다.
- 방법 1에서 설명한 것과 동일한 절차로 색상을 선택했으며, 그 결과는 아래와 같습니다.
- 모든 날짜 범위에 대해 같은 방식을 적용하면 원하는 출력 결과를 얻을 수 있습니다.
동일한 절차를 시간(Time) 유형의 데이터 유효성 검사에도 적용할 수 있습니다.
더 읽어보기: 엑셀에서 VBA를 활용한 데이터 유효성 검사 드롭다운 목록 (7가지 응용)
방법 4. 텍스트 길이 조건으로 데이터 유효성 검사 및 색상 적용하기
여기서는 텍스트 길이(Text Length) 유형의 데이터 유효성 검사에 색상을 적용하는 방법을 알아보겠습니다.
단계:
F열에 응시자 코드가 있는 데이터셋이 있다고 가정해 보겠습니다. 코드는 직접적인 의미가 없어 정확하게 입력하기 어렵고, 사람들이 자주 실수합니다. 이를 최소화하기 위해 데이터 유효성 검사로 코드 길이를 제한하면, 불필요한 문자가 입력되는 것을 막을 수 있습니다.
- 먼저 응시자 코드 열을 선택했습니다.
- 데이터 유효성 검사 대화 상자를 연 후 텍스트 길이 >> 같음 >> 4 (원하는 문자 길이) >> 확인 순으로 설정합니다.
- 이제 문자를 실수로 하나 더 입력하거나 빠뜨리는 등의 오류가 발생하면 경고 메시지가 표시됩니다.
단계:
이제 코드에 'A'가 포함된 셀은 파란색으로, 'B'가 포함된 셀은 녹색으로 칠하겠습니다.
- 먼저 응시자 코드 열을 다시 선택합니다.
- 방법 3에서 보여준 절차에 따라 특정 값을 선택한 후 각각 A 포함과 B 포함으로 설정하면 원하는 결과를 얻을 수 있습니다.
관련 내용: 엑셀 데이터 유효성 검사 드롭다운 목록에 필터 적용하기 (2가지 예제)
연습 워크시트
연습용 워크시트를 준비했습니다. 직접 따라 해 보세요.
결론
이번 글에서는 엑셀에서 색상과 함께 데이터 유효성 검사를 사용하는 방법을 배웠습니다. 이 기능을 활용하면 작업이 한결 수월해지고, 작업 중 발생할 수 있는 오류도 최소화할 수 있습니다. 글이 도움이 되었기를 바랍니다. 궁금한 점이 있다면 댓글로 남겨 주세요.
관련 글
- 다른 시트의 데이터로 유효성 검사 목록 사용하는 방법 (6가지 방법)
- 배열로 데이터 유효성 검사 목록을 만드는 Excel VBA
- VBA로 명명된 범위(Named Range)를 활용한 데이터 유효성 검사 목록 사용법
- 엑셀 데이터 유효성 검사에서 사용자 지정 VLOOKUP 수식 활용하기
- [해결] 엑셀에서 복사-붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제 (솔루션 포함)