Build conditional formatting rules
Decomposes your conditions into rules with exact references and a priority order.
| Category | Office work › Spreadsheets |
|---|---|
| Tags | DraftingOffice workerSpreadsheet |
Build the conditional formatting rules. 1. Break my stated conditions into individual rules. 2. Per rule, a table: applies to / formula / format. 3. **Write relative and absolute references exactly.** Base the formula on the first cell of the range and state which cell that is. 4. Where rules overlap, give the priority order and whether "stop if true" should be set. 5. **Warn if there are more than about four colors.** It stops being readable. 6. **If adding a helper column would be better than conditional formatting, say so.** Then: how to verify each rule fires on the right rows.
What this prompt does
Conditional formatting breaks on reference errors and overlapping rules. This states the anchor cell explicitly and orders the rules.
Real outputs by model
Read full answers
We gave three models the same input and copied their answers unedited. Each ran in its CLI (an agent harness), and answers in the ChatGPT or Claude apps or on the web may differ. Outputs are in Korean.
Sheet structure: A2:A200 거래처명, B2:B200 계약금액, C2:C200 납기일, D2:D200 진행상태(완료/진행중/지연) Conditions I want: 납기일이 오늘보다 지났는데 진행상태가 완료가 아니면 빨간색 강조, 계약금액이 1000만원 이상이면 노란색, 진행상태가 완료면 회색 처리하고 취소선
아래처럼 3개 규칙으로 나누면 됩니다. 적용 범위는 필요에 따라 행 전체 또는 금액 열만 지정했습니다.
색상은 빨강·노랑·회색 총 3개이므로 가독성 한도인 약 4색 이내입니다.
1. 기한 초과이면서 미완료
- 적용 범위:
A2:D200 - 기준 셀:
A2 - 수식:
=AND(ISNUMBER($C2),$C2<TODAY(),$D2<>"완료")
- 형식: 빨간색 채우기 또는 빨간색 강조
참조 설명:
$C2: C열은 고정하고 행 번호는 상대 참조$D2: D열은 고정하고 행 번호는 상대 참조- 수식은 첫 행인 2행을 기준으로 작성합니다.
ISNUMBER($C2)를 포함해 납기일이 비어 있는 행이 잘못 빨간색이 되는 것을 방지합니다.
2. 계약금액이 1,000만원 이상
- 적용 범위:
B2:B200 - 기준 셀:
B2 - 수식:
=$B2>=10000000
- 형식: 노란색 채우기
참조 설명:
$B2: B열은 고정하고 행 번호는 상대 참조- 계약금액이 숫자 형식으로 입력되어 있어야 합니다.
행 전체를 노란색으로 표시하고 싶다면 적용 범위를 A2:D200으로 바꾸고 같은 수식을 사용하면 됩니다.
3. 진행상태가 완료
- 적용 범위:
A2:D200 - 기준 셀:
A2 - 수식:
=$D2="완료"
- 형식: 회색 채우기 + 취소선
참조 설명:
$D2: D열은 고정하고 행 번호는 상대 참조- A열부터 D열까지 해당 행 전체가 회색 및 취소선 처리됩니다.
규칙 우선순위와 “참이면 중지”
Excel 기준 우선순위는 다음과 같이 두는 것이 좋습니다.
| 우선순위 | 규칙 | 겹침 여부 | 참이면 중지 |
|---|---|---|---|
| 1 | 기한 초과 + 미완료 | 계약금액 노랑과 겹칠 수 있음 | 예 |
| 2 | 완료 | 1번과는 논리적으로 겹치지 않음 | 예 |
| 3 | 계약금액 1,000만원 이상 | 1번과 겹칠 수 있음 | 아니오 |
기한 초과 미완료 행은 경고성이 가장 높으므로 빨간색을 우선합니다. 따라서 빨간색 규칙을 위에 두고 “참이면 중지”를 설정하면, 해당 행의 금액 셀이 노란색 대신 빨간색으로 표시됩니다.
Google Sheets에는 Excel의 “참이면 중지” 기능이 없으므로 규칙 순서와 적용 범위로 관리해야 합니다.
검증 방법
임시로 다음 데이터를 만들어 각 규칙을 확인하세요.
| 행 | 계약금액 | 납기일 | 진행상태 | 예상 결과 |
|---|---|---|---|---|
| 2 | 5,000,000 | 어제 | 진행중 | A2:D2 빨간색 |
| 3 | 15,000,000 | 내일 | 진행중 | B3 노란색 |
| 4 | 15,000,000 | 어제 | 완료 | A4:D4 회색 + 취소선 |
| 5 | 20,000,000 | 어제 | 진행중 | 빨간색 우선, 노랑은 표시되지 않음 |
| 6 | 10,000,000 | 오늘 | 진행중 | B6 노란색 |
| 7 | 10,000,000 | 빈 셀 | 진행중 | 기한 초과 규칙 미적용 |
오늘 날짜 경계도 확인해야 합니다. 납기일이 오늘이면 <TODAY() 조건에 걸리지 않으며, 내일부터 기한 초과로 처리됩니다.
현재 조건은 조건부 서식만으로 충분합니다. 다만 기한 초과 여부를 필터링하거나 집계할 일이 많아지면 E열에 다음과 같은 helper column을 추가하는 편이 더 관리하기 쉽습니다.
=AND(ISNUMBER(C2),C2<TODAY(),D2<>"완료")
그 경우 조건부 서식은 =$E2=TRUE를 기준으로 적용하면 됩니다.
More in this category
| Analyse Énergétique avec DJU, Consommation et Coûts | |
| Betting Prediction | |
| Black Magic Mantra: Unleashing Creativity and Innovation | |
| Reconcile two tables whose numbers disagree | |
| Build a data cleanup procedure |