Build Conditional Formatting Rules with Exact Formulas and Rule Order

You get a table of conditional formatting rules with the exact custom formula, the range to apply each one to, colors, and a rule order that avoids conflicts.

Prompt

Prompt
You are a spreadsheet analyst who builds conditional formatting for Excel and Google Sheets.

Before building anything, ask me these questions in one message and wait for my answers:
1. Are you using Excel or Google Sheets?
2. What are your sheet name, column headers and data range (for example A1:F200 with headers in row 1)?
3. What should stand out (for example overdue dates, values below target, duplicates, whole rows by status)?
4. Which colors do you want, or should I choose?

Then build the rules, following these rules:
- Every rule uses a custom formula written exactly as it should be typed, with correct $ anchoring (lock the column, not the row, when coloring whole rows).
- Formulas point to the first data row of the applied range, never the header row.
- Give the exact Applies-to range for each rule, excluding headers.
- Order rules so the most important wins, and say when to tick Stop If True in Excel or note that Sheets uses the first matching rule.
- Guard against blanks so empty rows never light up.

Output format:
1. Rule table with columns: Order, Purpose, Applies to, Custom formula, Format.
2. Step-by-step clicks to add one rule in my app.
3. A short test: 3-4 sample rows and the color each should show.

End with one line on any assumption about my layout that could break the formulas.

Example output

At a glanceConditional formatting rules in priority orderRule order for the Tasks sheet so rows never match conflicting rules
  1. Gray out finished tasksRule 1=$D2="Done" on A2:F150, Stop If True
  2. Overdue and not doneRule 2Light red fill when due date is past
  3. Due in the next 7 daysRule 3Amber fill for upcoming due dates
  4. Actual over budgetRule 4Bold dark red text on F2:F150 when F > E

Example for: an Excel project tracker on sheet Tasks, A1:F150, columns Task, Owner, Due Date (C), Status (D), Budget (E), Actual (F).

1. Rule table

OrderPurposeApplies toCustom formulaFormat
1Gray out finished tasks=$A$2:$F$150=$D2="Done"Light gray fill, gray text. Tick Stop If True
2Overdue and not done=$A$2:$F$150=AND($C2<>"",$C2<TODAY(),$D2<>"Done")Light red fill
3Due in the next 7 days=$A$2:$F$150=AND($C2<>"",$C2>=TODAY(),$C2<=TODAY()+7,$D2<>"Done")Amber fill
4Actual over budget=$F$2:$F$150=AND($E2<>"",$F2<>"",$F2>$E2)Bold dark red text

Why this order: Done rows are handled first and stopped, so a finished task with an old due date never turns red. Overdue sits above Due Soon so a date can only match one of them. Rule 4 changes font, not fill, so it shows on top of red or amber rows.

2. Adding one rule in Excel

  1. Select A2:F150 on the Tasks sheet.
  2. Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Paste =$D2="Done" into the formula box.
  5. Click Format > Fill, pick light gray, then OK.
  6. Repeat for rules 2-4, then open Conditional Formatting > Manage Rules, set Show formatting rules for: This Worksheet, and use the arrows to match the Order column. Tick Stop If True on rule 1 only.

3. Test rows (assume today is 10 March)

TaskDue DateStatusBudgetActualExpected result
Vendor contract2 MarchDone4,0004,500Gray row only (Stop If True blocks rule 4)
Site survey5 MarchIn progress1,200900Red row
Draft plan14 MarchNot started800950Amber row, Actual in bold red
Final review(blank)Not started500(blank)No formatting

Assumption: Status text is exactly "Done" and Due Date holds real dates, not text; if dates were imported as text, rules 2 and 3 will not fire.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for the sheet layout first means formulas use your real columns and ranges instead of generic examples.
  • Requiring $ anchoring rules and first-data-row references prevents the most common reason highlighting shifts or misfires.
  • Specifying rule order and Stop If True stops overlapping rules from fighting, and the test rows let you check it works.

Use when you want a sheet to flag problems at a glance. Have your column headers and data range ready. Works in ChatGPT, Claude or Gemini for both Excel and Google Sheets.

Common edits

  • Ask for a duplicate-detection or top-10 rule using COUNTIF or RANK added to the table.
  • Request a color-blind-friendly palette or icons instead of fill colors.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets