컬러밴드는 어떻게 만들었나요?

원본에서는 조건부 서식을 사용하고 MOD와 ROW 함수를 이용해 홀수·짝수 행을 구분합니다.

필터를 적용하면 왜 문제가 생기나요?

단순히 행 번호를 기준으로 색상을 지정하면 필터링 후 표시되는 행의 패턴이 흐트러질 수 있습니다.

최초 작성일: 2007-05-07
최종 수정일: 2026-09-26
조회수: 18 회
작성자: 권현욱 (엑셀러)
강의 제목: 조건부 서식과 SUBTOTAL로 필터링해도 컬러밴드 유지하기

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

제품 데이터에 행마다 색상을 번갈아 표시하는 컬러밴드를 조건부 서식으로 만들고, 필터링 후에도 패턴을 유지하는 방법을 살펴봅니다.

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

조건부 서식과 SUBTOTAL로 필터링해도 컬러밴드 유지하기

핵심 요약: 필터에도 유지되는 컬러밴드

조건부 서식과 SUBTOTAL을 조합하면 필터링으로 행이 숨겨져도 교차 행 색상 패턴을 유지할 수 있습니다.

ROW를 이용하면 기본적인 컬러밴드를 만들 수 있습니다.
단순 ROW 방식은 필터링 후 패턴이 흐트러질 수 있습니다.
SUBTOTAL과 혼합 주소를 사용하면 필터링 후에도 패턴을 유지할 수 있습니다.

강좌도 강좌지만 그 무엇보다 강좌의 머리말이나 꼬리말 부분에 큰 감명을 받고 있습니다. 그만큼 따라가기가 쉽지 않겠지만 천천히, 더디지만 실속 있게 따라가 보렵니다.

Source 시트에는 다음과 같이 제품별 기본 정보 데이터가 있는데, 매 2행마다 색상이 지정되어 있음을 알 수 있습니다. 이것은 어떻게 만든 것일까요?

방법 1: 조건부 서식으로 컬러밴드 만들기

Ctrl 키를 사용하여 불연속 영역을 지정하고 셀 서식을 설정한 것도 아니고, VBA를 사용한 것도 아닙니다. 조건부 서식을 사용한 것입니다.

아이엑셀러

Source 시트의 A2 셀을 선택하고 '서식-조건부 서식' 메뉴를 선택해 보면 수식이 입력되어 있음을 알 수 있습니다. 원본에서는 다음과 같은 수식을 사용했습니다.

=MOD(ROW($A2),2)=1

아이엑셀러

필터를 적용하면 생기는 문제

그런데 이 방법은 필터를 적용하게 되면 약간의 문제가 있습니다. 어떤 데이터를 필터링하느냐에 따라 컬러밴드가 흐트러지게 됩니다.

아이엑셀러

방법 2: SUBTOTAL 함수와 조건부 서식 조합

이번에는 Source2 시트에서 같은 작업을 진행합니다. 어떤 필터링을 적용하든 그 결과는 컬러밴드가 유지됩니다.

SUBTOTAL을 이용해 필터링 후에도 컬러밴드가 유지된 Source2 시트

아이엑셀러

조건부 서식을 적용한 것은 앞의 방법과 동일합니다. 다만 SUBTOTAL 함수를 하나 더 삽입하여 조건부 서식의 수식을 다음과 같이 지정했습니다.

=MOD(SUBTOTAL(3,$A$2:$A2),2)=0

SUBTOTAL 함수의 ref 인수를 지정할 때 $A$2:$A2와 같이 절대 주소와 혼합 주소 형태를 혼용한 점도 눈여겨볼 부분입니다.

원본 데이터

Source 시트에는 다음과 같은 제품별 데이터가 들어 있습니다.

제품 번호	제품 이름	공급업체	제품 분류	포장 단위	단가	재고량	발주량	추가 발주량
1	태양 100% 오렌지 주스	서울 무역 ㈜	유제품	10 boxes x 20 bags	80,000	39	0	10
2	태양 100% 레몬 주스	태양 식품 ㈜	음료	24 - 12 oz bottles	19,000	17	40	25
3	태양 체리 시럽	태양 식품 ㈜	조미료	12 - 550 ml bottles	10,000	13	70	25
4	신한 100% 복숭아 시럽	신한 식품 ㈜	조미료	48 - 6 oz jars	22,000	53	0	0
5	신한 100% 파인애플 시럽	신한 식품 ㈜	조미료	36 boxes	21,000	0	0	0
6	대양 특선 블루베리 잼	대양 농산 ㈜	조미료	12 - 8 oz jars	25,000	120	0	25
7	대양 특선 건과(배)	대양 농산 ㈜	가공 식품	12 - 1 lb pkgs.	30,000	15	0	10
8	대양 특선 딸기 소스	대양 농산 ㈜	조미료	12 - 12 oz jars	40,000	6	0	0
9	북미산 상등육 쇠고기	서울 무역 ㈜	육류	18 - 500 g pkgs.	97,000	29	0	0
10	노르웨이산 연어알 조림	서울 무역 ㈜	해산물	12 - 200 ml jars	31,000	31	0	0
11	현진 커피 밀크	현진 식품 ㈜	유제품	1 kg pkg.	21,000	22	30	30
12	현진 바닐라 엣센스	현진 식품 ㈜	유제품	10 - 500 g pkgs.	13,800	86	0	0
13	유미 돌김	유미 식품 ㈜	해산물	2 kg box	16,000	24	0	5
14	유미 건조 다시마	유미 식품 ㈜	가공 식품	40 - 100 g pkgs.	23,000	35	0	0
15	유미 간장	유미 식품 ㈜	조미료	24 - 250 ml bottles	15,000	39	0	5
16	파블로바 피넛 스프레드	대륙 교역 ㈜	과자류	32 - 500 g boxes	17,000	29	0	10
17	앨리스 포장육	대륙 교역 ㈜	육류	20 - 1 kg tins	39,000	0	0	0
18	대륙 냉동 참치	대륙 교역 ㈜	해산물	16 kg pkg.	62,000	42	0	0
19	신한 초콜릿 소스	미미 제과 ㈜	과자류	10 boxes x 12 pieces	19,000	25	0	5
20	대양 마말레이드	미미 제과 ㈜	과자류	30 gift boxes	81,000	40	0	0
21	대양 핫 케이크 소스	미미 제과 ㈜	과자류	24 pkgs. x 4 pieces	10,000	3	40	5
22	신성 시리얼	신성 곡물 ㈜	곡류	24 - 500 g pkgs.	21,000	104	0	25
23	신성 쌀 튀김 과자	신성 곡물 ㈜	곡류	12 - 250 g pkgs.	9,000	61	0	25
24	삼화 콜라	삼화 음료 ㈜	음료	12 - 355 ml cans	6,000	20	0	0
25	우미 특선 레몬 파이	우미 제과 ㈜	과자류	20 - 450 g glasses	14,000	76	0	30
26	우미 코코넛 쿠키	우미 제과 ㈜	과자류	100 - 250 g bags	31,000	15	0	0
27	우미 피넛 샌드	우미 제과 ㈜	과자류	100 - 100 g pieces	43,000	49	0	30
28	알파 특선 튀김 다시마	알파 식품 ㈜	가공 식품	25 - 825 g cans	15,000	26	0	0
29	알파 왕갈비 훈제육	알파 식품 ㈜	육류	50 bags x 30 sausgs.	123,000	0	0	0
30	태평양 포장 파래	태평양 수산 ㈜	해산물	10 - 200 g glasses	25,000	10	0	15
31	대관령 바닐라 아이스크림	대관령 유업 ㈜	유제품	12 - 100 g pkgs	12,000	0	70	20
32	대관령 초콜릿 아이스크림	대관령 유업 ㈜	유제품	24 - 200 g pkgs.	32,000	9	40	25
33	한라 멜론 아이스크림	한라 유업 ㈜	유제품	500 g	2,000	112	0	20
34	태일 라이트 맥주	태일 양조 ㈜	음료	24 - 12 oz bottles	34,000	111	0	15
35	태일 적포도주	태일 양조 ㈜	음료	24 - 12 oz bottles	38,000	20	0	15
36	특제 굴 통조림	원양 수산 ㈜	해산물	24 - 250 g jars	19,000	112	0	20
37	포장 건 오징어	원양 수산 ㈜	해산물	12 - 500 g pkgs.	26,000	11	50	25
38	OK 체리 셰이크	OK 유업 ㈜	음료	12 - 75 cl bottles	26,000	17	0	15
39	OK 바닐라 셰이크	OK 유업 ㈜	음료	750 cc per bottle	28,000	69	0	5
40	보스톤산 게살 통조림	금호 수산 ㈜	해산물	24 - 4 oz tins	18,000	123	0	30
41	훈제 대합조개 통조림	금호 수산 ㈜	해산물	12 - 12 oz cans	9,000	85	0	10
42	싱가폴 원산 옥수수	델타 무역 ㈜	곡류	32 - 1 kg pkgs.	14,000	26	0	0
43	콜롬비아산 원두커피	델타 무역 ㈜	음료	16 - 500 g tins	46,000	17	10	25
44	델타 청정 생강즙	델타 무역 ㈜	조미료	20 - 2 kg bags	19,000	27	0	15
45	버뮤다 포장 문어	버뮤다 수산 ㈜	해산물	1k pkg.	9,000	5	70	15
46	버뮤다 포장 참치	버뮤다 수산 ㈜	해산물	4 - 450 g glasses	12,000	95	0	0
47	진미 바닐라 쿠키	진미 식품 ㈜	과자류	10 - 4 oz boxes	9,000	36	0	0
48	진미 트로피컬 캔디	진미 식품 ㈜	과자류	10 pkgs.	12,000	15	70	25
49	미왕 계피 캔디	미왕 식품 ㈜	과자류	24 - 50 g pkgs.	20,000	10	60	15
50	미왕 코코아 샌드	미왕 식품 ㈜	과자류	12 - 100 g bars	16,000	65	0	30
51	유림 사과 통조림	유림 농산 ㈜	가공 식품	50 - 300 g pkgs.	53,000	20	0	10
52	필로 믹스	유림 농산 ㈜	곡류	16 - 2 kg boxes	7,000	38	0	25
53	파스 페이스 티	유림 농산 ㈜	육류	48 pieces	32,000	0	0	0
54	한림 훈제 통닭	한림 낙농 ㈜	육류	16 pies	17,000	21	0	10
55	한림 특선 양념 칠면조	한림 낙농 ㈜	육류	24 boxes x 2 pies	24,000	115	0	20
56	한성 옥수수 가루	한성 제분 ㈜	곡류	24 - 250 g pkgs.	38,000	21	10	30
57	한성 통밀가루	한성 제분 ㈜	곡류	24 - 250 g pkgs.	19,000	36	0	20
58	대성 어묵	대성 육가공 ㈜	해산물	24 pieces	13,000	62	0	20
59	대일 포장 치즈	대일 유업 ㈜	유제품	5 kg pkg.	55,000	79	0	0
60	대일 파메쌍 치즈	대일 유업 ㈜	유제품	15 - 300 g rounds	34,000	19	0	0
61	사계절 핫 소스	사계절 식품 ㈜	조미료	24 - 500 ml bottles	28,000	113	0	25
62	사계절 커스터드 파이	사계절 식품 ㈜	과자류	48 pies	49,000	17	0	0
63	대륙 사과 식초	대륙 교역 ㈜	조미료	15 - 625 g jars	43,000	24	0	5
64	알파 콘 플레이크	알파 식품 ㈜	곡류	20 bags x 4 pieces	33,000	22	80	30
65	루이지애나 특산 후추	신한 식품 ㈜	조미료	32 - 8 oz bottles	21,000	76	0	0
66	유미 멸치 가루	신한 식품 ㈜	조미료	24 - 8 oz jars	17,000	4	100	20
67	태일 브랜디	태일 양조 ㈜	음료	24 - 12 oz bottles	45,000	52	0	10
68	미미 스카치 캔디	미미 제과 ㈜	과자류	10 boxes x 8 pieces	12,000	6	10	15
69	한라 분유	한라 유업 ㈜	유제품	10 kg pkg.	36,000	26	0	15
70	트로피컬 칵테일	대륙 교역 ㈜	음료	24 - 355 ml bottles	45,000	15	10	30
71	한라 연유	한라 유업 ㈜	유제품	10 - 500 g pkgs.	21,000	26	0	0
72	대관령 특제 버터	대관령 유업 ㈜	유제품	24 - 200 g pkgs.	34,000	14	0	0
73	원양 순 상어알	원양 수산 ㈜	해산물	24 - 150 g jars	35,000	101	0	5
74	서울 구이 김	서울 무역 ㈜	가공 식품	5 kg pkg.	10,000	4	20	5
75	알파인 맥주	알파 식품 ㈜	음료	24 - 0.5 l bottles	24,000	125	0	25
76	미왕 초콜릿 드링크	미왕 식품 ㈜	음료	500 ml	3,000	57	0	20
77	알파 샐러드 드레싱	알파 식품 ㈜	조미료	12 boxes	13,000	32	0	15