How to Use Conditional Formatting in Excel
Conditional formatting highlights cells automatically based on rules you set — values, text, dates, formulas. Here's how to set it up, use formula-based rules, and manage rules that conflict.
Conditional formatting changes how cells look — color, bold, icon — based on their value or a formula you write. It's how you make a spreadsheet visually scannable: red for overdue, green for on-target, yellow for anything that needs attention.
Basic setup: highlight cells by value
Step 1. Select the range you want to format (e.g., B2:B100).
Step 2. Go to Home → Conditional Formatting → Highlight Cells Rules.
Step 3. Choose a rule type:
- Greater Than / Less Than — highlight numbers above or below a threshold
- Text That Contains — highlight cells containing specific text
- A Date Occurring — highlight dates in the past week, last month, etc.
- Duplicate Values — highlight repeats
Step 4. Set the value and choose a format (red fill, yellow, green, or custom).
Color scales and data bars
For a quick visual gradient across a range:
Home → Conditional Formatting → Color Scales — applies a two or three-color gradient (e.g., red → yellow → green) based on relative values.
Home → Conditional Formatting → Data Bars — adds a mini bar chart inside each cell proportional to its value.
Both are fast to apply and useful for financial dashboards.
Formula-based rules (the powerful option)
Formula rules let you highlight an entire row based on any condition, or use logic that built-in rules can't express.
Example: Highlight entire rows where the Status column (column D) says "Overdue":
Step 1. Select your entire data range (e.g., A2:F100).
Step 2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
Step 3. Enter this formula:
=$D2="Overdue"The $ before D locks the column but lets the row move — so the rule checks column D for each row across the entire selection.
Step 4. Set your format (red fill, bold text, etc.) and click OK.
✦ TIP
The dollar sign placement is critical in formula-based rules. $D2 locks the column but not the row. $D$2 would check only one specific cell for the entire selection — almost always wrong.
Managing multiple rules
Home → Conditional Formatting → Manage Rules shows all rules on the current sheet. Rules run top to bottom — the first matching rule wins (unless you check "Stop If True").
When rules conflict (a cell meets two different conditions), reorder them by dragging. Put the most important rule first.
The Griddy way
Formula-based conditional formatting has a steep learning curve — especially the $ anchoring logic and multi-condition rules. Just describe what you want highlighted:
"Highlight rows in red where the deal is overdue by more than 30 days and the status isn't Closed. Highlight in yellow if overdue by 1–30 days."
Griddy applies both rules with the correct formula anchoring.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.