How to Use Conditional Formatting in Excel

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:

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.

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.

Check rule order and applied ranges

Conditional formatting is only useful when the rule matches the rows people are reviewing. Set the Applies to range deliberately, anchor the first data column or row correctly in formula-based rules, and test a normal row plus an edge case. If several rules can match the same cell, review their order and whether Stop If True is enabled.

Use formatting to direct attention, not to encode information that exists nowhere else. Keep the underlying status, date, or value in the cell so the workbook remains usable without color. Avoid red-green-only meaning when the sheet may be read in grayscale or by someone with color-vision differences; add text labels, icons, or a clear legend for important states.

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.

Sources

• Microsoft Support: Use conditional formatting to highlight information in Excel
• Microsoft Support: Use data bars, color scales, and icon sets to highlight data