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
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
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 like this
Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts
You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.
Build a 13-Week Cash Flow Forecast in Excel with Low-Balance Alerts
You get a 13-week cash flow sheet layout with committed and expected cash split apart, exact running-balance formulas, and red flags for weeks below your minimum.
Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel
You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.
Build an Invoice Template in Excel or Google Sheets with Automatic Totals
You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.
Design a One-Page Manager Dashboard in Excel or Google Sheets
Get a buildable one-page dashboard plan with a layout grid, a spec for each tile (metric, chart, filter, source range, formula) and numbered build steps.
Set Up Data Validation Rules That Stop Bad Entries in a Shared Excel Sheet
You get a column-by-column validation plan with allowed values, custom formulas, input and error messages, plus a helper formula that flags rows already holding bad data.