대용량 데이터 시트를 다루다 보면 데이터를 한눈에 파악할 수 있도록 행 색상을 번갈아 적용해야 하는 경우가 자주 있습니다. 색상은 개별 행 단위로 적용할 수도 있고, 그룹 단위로 적용할 수도 있습니다. 이 글에서는 엑셀에서 그룹을 기준으로 행 색상을 교차 적용할 수 있는 6가지 쉬운 방법을 소개합니다.
엑셀에서 그룹 기준으로 행 색상을 번갈아 적용하는 6가지 방법
설명을 위해 과일을 구매한 10명의 소비자 데이터를 예시로 사용하겠습니다. B열에는 과일 품목 이름이, C열과 D열에는 각각 구매자 이름과 구매 수량이 입력되어 있습니다. 즉, 데이터 범위는 B5:D14입니다. 모든 방법에서 조건부 서식을 활용해 셀 색상을 변경하게 됩니다.
1. AND 함수와 LEN, MOD 함수 조합하기
첫 번째 방법은 먼저 IF 함수를 사용해 데이터에 숫자형 그룹 번호를 부여하고, 이후 조건부 서식 규칙에 AND, LEN, MOD 함수를 조합하여 행 색상을 교차 적용하는 방식입니다. 단계별 절차는 아래와 같습니다.
📌 단계:
- 먼저 E4 셀을 선택하고 0을 입력합니다.
0
- Enter 키를 누릅니다.
- 이어서 E5 셀을 선택하고 아래 수식을 입력합니다.
=IF(B5=B4,E4,E4+1)
- Enter 키를 누릅니다.
- 채우기 핸들(Fill Handle)을 더블클릭해 수식을 E14 셀까지 복사합니다.
- 그다음 셀 범위 B5:E14를 선택합니다.
- 홈 탭의 스타일 그룹에서 조건부 서식 > 새 규칙 옵션의 드롭다운 화살표를 클릭합니다.
- 새 서식 규칙(New Formatting Rule) 대화 상자가 나타납니다.
- '수식을 사용하여 서식을 지정할 셀 결정' 아래 빈 칸에 다음 수식을 입력합니다.
=AND(LEN($B5)>0,MOD($E5,2)=0)
- 서식 버튼을 클릭합니다.
- 셀 서식(Format Cells) 대화 상자가 열리면 채우기 탭에서 원하는 색상을 선택합니다. 여기서는 녹색, 악센트 6, 밝게 60%를 사용했습니다.
- 확인을 클릭합니다.
- 다시 확인을 눌러 새 서식 규칙 대화 상자를 닫습니다.
- 잠시 후 데이터의 교차 그룹에 선택한 색상이 적용된 것을 확인할 수 있습니다.
수식이 정상적으로 작동하여 그룹 기준으로 행 색상을 성공적으로 교차 적용했습니다.
🔎 수식 분석
E5 셀의 수식을 하나씩 살펴보겠습니다.
👉 LEN($B5): 셀 값의 글자 수를 계산합니다. 이 경우 결과는 5입니다.
👉 MOD($E5,2): E5 셀의 값을 2로 나눈 나머지를 반환합니다. 여기서는 1입니다.
👉 AND(LEN($B5)>0,MOD($E5,2)=0): AND 함수가 두 조건을 동시에 검사합니다. 두 논리값이 모두 참(True)이면 해당 행에 선택한 색상이 적용되고, 그렇지 않으면 일반 형태로 표시됩니다.
2. MOD, IF, ROW 함수 활용하기
이 방법에서는 MOD, IF, ROW 함수를 조합해 숫자형 그룹 번호를 만듭니다. 절차는 다음과 같습니다.
📌 단계:
- 먼저 E4 셀에 0을 입력하고 Enter 키를 누릅니다.
- E5 셀에 아래 수식을 입력합니다.
=MOD(IF(ROW()=2,0,IF(B5=B4,E4,E4+1)),2)
- Enter 키를 누른 뒤, 채우기 핸들을 더블클릭해 수식을 E14까지 복사합니다.
- 셀 범위 B5:E14를 선택합니다.
- 홈 탭에서 조건부 서식 > 새 규칙을 클릭합니다.
- 새 서식 규칙 대화 상자가 나타나면 수식 입력란에 다음을 입력합니다.
=$E5=1
- 서식 버튼을 클릭하고, 셀 서식 대화 상자의 채우기 탭에서 원하는 색상을 선택한 후 확인을 누릅니다.
- 새 서식 규칙 대화 상자도 확인으로 닫으면 완료됩니다.
🔎 수식 분석
👉 ROW(): 현재 행 번호를 반환합니다. 이 경우 결과는 5입니다.
👉 IF(B5=B4,E4,E4+1): IF 함수가 B5와 B4 값을 비교합니다. 두 값이 같으면 E4 값을 반환하고, 다르면 E4에 1을 더한 값을 반환합니다.
👉 IF(ROW()=2,0,IF(B5=B4,E4,E4+1)): 행 번호가 2인지 확인합니다. 참이면 0을, 거짓이면 안쪽 IF 함수의 결과를 반환합니다.
👉 MOD(IF(...),2): IF 함수의 결과를 2로 나눈 나머지를 반환합니다.
3. MOD 함수와 IF 함수 조합하기
세 번째 방법은 MOD 함수와 IF 함수만으로 숫자형 그룹 번호를 만드는 간결한 방식입니다.
📌 단계:
- E4 셀에 0을 입력하고 Enter 키를 누릅니다.
- E5 셀에 아래 수식을 입력합니다.
=MOD(IF(B5=B4,E4,E4+1),2)
- Enter 키를 누르고, 채우기 핸들을 더블클릭해 수식을 E14까지 복사합니다.
- 범위 B5:E14를 선택한 후 조건부 서식 > 새 규칙을 실행합니다.
- 수식 입력란에 다음을 입력합니다.
=$E5=1
- 서식 버튼을 클릭하고 채우기 탭에서 원하는 색상을 지정한 뒤 확인을 누릅니다.
- 새 서식 규칙 대화 상자도 확인으로 닫으면 그룹별로 색상이 교차 적용됩니다.
🔎 수식 분석
👉 IF(B5=B4,E4,E4+1): B5와 B4 값을 비교해 같으면 E4를, 다르면 E4+1을 반환합니다.
👉 MOD(IF(...),2): IF 함수 결과를 2로 나눈 나머지를 반환합니다.
4. ISODD 함수 적용하기
이 방법은 먼저 IF 함수와 SUM 함수로 그룹 번호를 만든 뒤, 조건부 서식에서 ISODD 함수를 사용해 행 색상을 바꾸는 방식입니다.
📌 단계:
- E4 셀에 0을 입력하고 Enter 키를 누릅니다.
- E5 셀에 아래 수식을 입력합니다.
=IF(B4=B5,E4,SUM(E4,1))
- Enter 키를 누른 뒤, 채우기 핸들을 더블클릭해 수식을 E14까지 복사합니다.
- 범위 B5:E14를 선택하고 조건부 서식 > 새 규칙을 실행합니다.
- 수식 입력란에 다음을 입력합니다.
=ISODD($E5)
- 서식 버튼을 클릭하고 채우기 탭에서 원하는 색상을 지정한 뒤 확인을 누릅니다.
- 새 서식 규칙 대화 상자를 확인으로 닫으면 완료됩니다.
🔎 수식 분석
👉 SUM(E4,1): E4 셀 값에 1을 더합니다. 이 셀에서는 1을 반환합니다.
👉 IF(B4=B5,E4,SUM(E4,1)): B5와 B4가 같으면 E4를 반환하고, 다르면 SUM 함수의 결과를 반환합니다. ISODD 함수는 이 값이 홀수인지 판별해 조건부 서식을 적용합니다.
5. ISEVEN 함수 활용하기
ISODD 함수와 마찬가지로 ISEVEN 함수를 사용할 수도 있습니다. 역시 먼저 IF와 SUM 함수로 그룹 번호를 만든 후 진행합니다.
📌 단계:
- E4 셀에 0을 입력하고 Enter 키를 누릅니다.
- E5 셀에 아래 수식을 입력합니다.
=IF(B4=B5,E4,SUM(E4,1))
- Enter 키를 누르고, 채우기 핸들을 더블클릭해 수식을 E14까지 복사합니다.
- 범위 B5:E14를 선택한 후 조건부 서식 > 새 규칙을 실행합니다.
- 수식 입력란에 다음을 입력합니다.
=ISEVEN($E5)
- 서식 버튼을 클릭하고 채우기 탭에서 원하는 색상을 지정한 뒤 확인을 누릅니다.
- 새 서식 규칙 대화 상자를 확인으로 닫으면 그룹별 색상이 교차 적용됩니다.
🔎 수식 분석
👉 SUM(E4,1): E4 셀 값에 1을 더합니다.
👉 IF(B4=B5,E4,SUM(E4,1)): B5와 B4가 같으면 E4를, 다르면 SUM 결과를 반환합니다. ISEVEN 함수는 이 값이 짝수인지 판별합니다.
6. ISEVEN, CEILING, ROW 함수 조합하기
마지막 방법은 ISEVEN, CEILING, ROW 함수를 조합해 보조 열 없이 바로 조건부 서식에 적용하는 방식입니다.
📌 단계:
- 먼저 범위 B5:E14를 선택합니다.
- 홈 탭에서 조건부 서식 > 새 규칙을 클릭합니다.
- 수식 입력란에 다음을 입력합니다.
=ISEVEN(CEILING(ROW()-4,2)/2)
- 서식 버튼을 클릭하고 채우기 탭에서 원하는 색상을 지정한 뒤 확인을 누릅니다.
- 새 서식 규칙 대화 상자를 확인으로 닫으면 완료됩니다.
🔎 수식 분석 (5행 기준)
👉 ROW(): 현재 행 번호를 반환합니다. 이 경우 결과는 5입니다.
👉 CEILING(ROW()-4,2): ROW 함수 결과에서 4를 뺀 값을 가장 가까운 2의 배수로 올림합니다. 여기서는 2가 됩니다.
👉 ISEVEN(CEILING(ROW()-4,2)/2): CEILING 함수 결과를 2로 나눈 값이 짝수인지 판별합니다. 짝수이면 해당 행에 선택한 색상이 적용됩니다.
💬 알아두어야 할 사항
이 방법에서는 수식을 조건부 서식 규칙 상자에 직접 입력했고, 별도의 숫자형 그룹 번호를 만들지 않았습니다. 따라서 각 그룹에 속한 항목 수가 서로 동일해야 정확하게 작동합니다. 그룹 크기가 일정하지 않으면 결과가 올바르게 표시되지 않으니 주의하세요.
마무리
지금까지 엑셀에서 그룹을 기준으로 행 색상을 교차 적용하는 6가지 방법을 살펴봤습니다. 실무에서 데이터 가독성을 높이는 데 큰 도움이 될 것입니다. 추가 질문이나 제안이 있다면 댓글로 남겨주세요. 엑셀 관련 다양한 문제와 해결책을 확인하려면 ExcelDemy 웹사이트도 방문해 보세요. 꾸준히 배우고 성장하시길 바랍니다!
함께 보면 좋은 글
- 엑셀에서 한 행 걸러 음영 적용하는 방법 (3가지)
- 표 없이 엑셀에서 행 색상 교차 적용하는 방법 (5가지)