Excel Basics

How to Highlight Rows Based on Cell Value in Excel

Q: How do I highlight rows based on cell values in Excel?

Select the entire data range starting from the first data row (not the header), go to Conditional Formatting → New Rule → 'Use a formula to determine which cells to format'. Enter a formula that references the first cell of the criteria column with the COLUMN locked but not the row, for example =$D2="Overdue". The dollar sign before D locks the column while the row number adjusts for each evaluated row, so Excel checks column D for every row in your range.

Q: Why is my conditional formatting not working in Excel?

Five common causes, in order of frequency: (1) missing dollar signs on column references (=$D2 vs =D2); (2) rule applies to the wrong range — open Manage Rules and check Applies To; (3) rules are in the wrong priority order and a higher rule wins; (4) the comparison uses quotes in a non-text cell, or compares a text-typed number to a real number; (5) cell-level formatting overrides the conditional format. Open Home → Conditional Formatting → Manage Rules to audit all three.

Q: Can I use conditional formatting on an entire Excel Table?

Yes, and you should. When the range is defined as an Excel Table (Ctrl+T), conditional formatting rules automatically extend to new rows as the table grows — no need to edit the Applies To range every time someone pastes in new data. Use structured references in the formula, for example =[@Status]="Overdue" instead of =$D2="Overdue".

Q: How many conditional formatting rules can slow down a workbook?

Performance starts to degrade noticeably above about 50 active rules or when a single rule's Applies To range exceeds roughly 100,000 cells with a formula-based condition. Symptoms: slow scroll, lag on typing, slow file save. Fixes: consolidate rules (one rule with OR beats three separate rules), tighten the Applies To range to real data only, and replace volatile functions (TODAY, NOW, INDIRECT) with static anchors where possible.

Q: How do I copy conditional formatting rules to another sheet or workbook?

Use Format Painter: select a cell with the rule applied, press Ctrl+Shift+C (or click Format Painter on the Home ribbon), then paint over the destination range. For cross-workbook copies, copy an entire formatted column or row, paste-special with 'Formats' option into the target workbook. After pasting, open Manage Rules in the destination and fix any references that still point to the source workbook.

By Sagnik Bhattacharya6 Mar 20268 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for Excel Conditional Formatting Tips and Tricks, with highlighted cells, colour scales, and formatting triggers.

Conditional formatting is one of Excel's most underused features. It automatically changes the appearance of cells based on their values — making patterns, outliers, and trends instantly visible without a single chart.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

This tutorial is beginner-first. It covers every built-in rule (Highlight Cell Rules, Top/Bottom, Data Bars, Colour Scales, Icon Sets), then graduates to the one concept 90% of beginners get wrong the first time: formula-based rules with mixed cell references, which is how you highlight an entire row based on the value in a single column. You'll also get a full Troubleshooting section because conditional formatting silently misbehaves more often than any other Excel feature.

Everything shown works in Excel 2019, Excel 2021, Microsoft 365, and Excel for the web. For the broader Excel skill set, see the Excel formulas and analysis hub, which links every Excel tutorial on this site in a recommended reading order.

The Basics: Built-in Rules

Select your data range, then go to Home → Conditional Formatting. You'll find these categories:

Highlight Cell Rules

Greater Than / Less Than — Highlight cells above or below a threshold
Between — Highlight values within a range
Equal To — Highlight specific values
Text That Contains — Highlight cells containing specific text
Duplicate Values — Instantly find duplicates in your data. For a complete workflow on removing them, see how to clean messy data

Top/Bottom Rules

Top 10 items or top 10%
Bottom 10 items or bottom 10%
Above or below average

Real-world scenario: A sales manager reviews a 2,000-row quarterly pipeline and wants to see at a glance which deals are stuck. One rule (Highlight Cells → Greater Than → 30 days since last activity → fill red) turns a spreadsheet into a triage tool in ten seconds — no pivot, no chart, no formula to maintain.

Beginner pitfall: Built-in rules apply to the selected range only, but they don't extend automatically as new rows are added to a plain range. Convert the range to an Excel Table first (Ctrl+T) — then the rule auto-extends. Without this, you'll have to re-open Manage Rules every time someone pastes new data, and sooner or later you'll forget. The Excel Tables best-practices tutorial explains why this behaviour is baked in.

Visual Formats

Data Bars

Turn any column of numbers into miniature bar charts directly inside cells. Select your range → Conditional Formatting → Data Bars. Choose gradient or solid fill. Great for comparing values at a glance without creating a separate chart. When you need more detail, graduate to full-sized charts for detailed visualisation.

Colour Scales

Apply a gradient of colours across your values — green for high, red for low (or reversed). Perfect for heat maps, performance dashboards, and highlighting distribution patterns.

Icon Sets

Add arrows, traffic lights, stars, or flags to cells based on value ranges. Useful for KPI dashboards — green arrow up for targets met, red arrow down for missed targets.

Real-world scenario: A marketing analyst turns a monthly KPI tracker into a glanceable status card by applying a 3-colour scale on "% of target" — dark green near 120%, yellow at 100%, dark red at 60%. The executive reading the report doesn't scan the numbers; they scan the colours, and the eye lands on the two red cells that need attention in under two seconds. That is the real win of conditional formatting: it externalises cognitive load.

Beginner pitfall: Colour scales and data bars re-compute their reference range every time you add a row, so outliers skew the scale after a bad paste. If every number suddenly looks pale green, open Manage Rules → Edit Rule → and set explicit Type = Number with hard min and max values instead of the default Lowest/Highest. This also protects accessibility — automatic gradients often fail the WCAG 3:1 contrast minimum for colour-blind readers.

Custom Formula-Based Rules

This is where conditional formatting becomes truly powerful. The formulas used here — IF, MOD, WEEKDAY, TODAY — are covered in depth in the advanced formulas guide. Go to Conditional Formatting → New Rule → Use a formula.

Example 1: Highlight Entire Row Based on One Cell

To highlight the entire row if column D says "Overdue":

=$D1="Overdue"

The $D locks the column reference while letting the row number change — so the rule checks column D for every row.

The rule every beginner gets wrong on the first try: Select the data range starting from the first data row (not the header row). If your headers are in row 1 and your data starts in row 2, select A2:F100 and write =$D2="Overdue". If the first selected row is row 2 but you write =$D1="Overdue", Excel will be off by one, and every row will be highlighted based on the row above it. This is the single most common "conditional formatting not working" problem reported on Excel forums — fix it by matching the row number in the formula to the first row of your selection.

Excel Table version: If your data is in a Table called tblSales, use structured references: =[@Status]="Overdue". Cleaner, self-documenting, and auto-extends as new rows are added.

Example 2: Alternating Row Colours

=MOD(ROW(),2)=0

This applies formatting to every even row, creating a zebra-stripe effect that improves readability.

Example 3: Highlight Weekends

If column A has dates:

=OR(WEEKDAY($A1)=1, WEEKDAY($A1)=7)

Example 4: Cells Older Than 30 Days

=TODAY()-$A1>30

Example 5: Highlight Duplicates Within a Single Column

To highlight rows where a value in column B appears more than once across the whole column:

=COUNTIF($B:$B,$B1)>1

Useful for invoice numbers, customer emails, or any field that should be unique. Note both dollar signs on $B:$B — locking the entire column — but only one on $B1 so the row moves per evaluated row.

Example 6: Highlight a Row Only When Two Conditions Are True

To highlight rows where status is "Overdue" AND amount is above £10,000:

=AND($D1="Overdue", $E1>10000)

Use OR() for either-or logic. Nesting AND/OR inside the condition is how you build multi-criteria flags without extra helper columns.

Real-world scenario: A project manager tracking 400 tasks applies three formula rules in priority order: "Overdue AND unassigned" (red), "Due this week AND unassigned" (amber), "Due this week" (yellow). Running the same three checks by hand would take 20 minutes every Monday; with rules, the spreadsheet colours itself on every refresh. The time saved compounds week after week — this is the quiet ROI beginners miss.

Beginner pitfall: Dollar signs work on both axes — $A1, A$1, $A$1, and A1 all behave differently. For row-based rules, 99% of the time you want column-locked-only ($D1). If your rule only highlights the first row or highlights diagonally, the dollar signs are wrong. Press F4 while the cursor is inside the reference to cycle through lock modes.

Pro Tips

Rule order matters — Rules are evaluated top to bottom. Use "Manage Rules" to reorder them and check "Stop If True" when needed.
Use named ranges — Reference named ranges in formulas for clarity and maintainability.
Performance — Too many conditional formatting rules on large datasets can slow down Excel. Use sparingly on 100K+ row sheets.
Copy formatting — Use Format Painter (Ctrl+Shift+C) to copy conditional formatting rules to other ranges.
Clear rules — To remove all formatting: Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet.

Dashboard Use Case

Combine multiple conditional formatting types to create a visual dashboard:

Data bars on revenue columns
Traffic light icons on target achievement columns
Colour scale on a heatmap of monthly performance
Red highlight on overdue items

All without writing a single formula or creating a chart. That's the power of conditional formatting. Ready to take it further? See the full guide to building a complete interactive dashboard.

Troubleshooting

Five problems almost every beginner hits when using conditional formatting, with exact fixes.

Rule highlights the wrong row, or every row, or no row. The formula's row number doesn't match the first row of the Applies To range. Open Manage Rules → Edit Rule → look at both the Applies To range and the formula. If Applies To starts at row 2, the formula must reference row 2 (for example, =$D2="Overdue", not =$D1). Fix it and press OK — every row in the range snaps into place.
Rule stopped working after you sorted or filtered the data. Sorting can split the Applies To range into fragments (you'll see a comma-separated list in Applies To). The fix is to re-select the whole range and re-apply the rule once. Better long-term fix: convert the range to a Table (Ctrl+T) so sorting doesn't fragment rules.
Icon Sets show the wrong icons even though numbers look right. The underlying cells are text-typed, not numbers (common after CSV import). Select the column → Data → Text to Columns → Next → Next → Finish. Excel re-parses text-numbers as real numbers and the icons correct themselves.
Two rules are conflicting and you want only one to win. Open Manage Rules, drag the winning rule to the top of the list, then tick "Stop If True" next to it. Excel evaluates top-down and stops evaluating further rules once the first matching rule with "Stop If True" hits.
Workbook crawls or locks up on save. Rules have multiplied — usually because copy-paste duplicates them. Open Manage Rules → This Worksheet view. If the list is more than 20–30 rules, many will be near-duplicates from paste operations. Delete the orphans. For a permanent fix, use Format Painter to copy formatting instead of copying whole cells.

Frequently Asked Questions

How do I highlight rows based on cell values in Excel?
Select the data range starting from the first data row (not the header), go to Conditional Formatting → New Rule → "Use a formula to determine which cells to format". Enter a formula with the column locked, for example =$D2="Overdue". The $ before D locks the column so Excel checks column D for every row in the range.

Why is my conditional formatting not working in Excel?
Five common causes in order of frequency: missing dollar signs on column references, Applies To range is wrong, rule priority order wrong (higher rule wins), comparing text-typed number to real number, and cell-level formatting overrides. Open Home → Conditional Formatting → Manage Rules to audit.

Can I use conditional formatting on an entire Excel Table?
Yes, and you should. When the range is a Table (Ctrl+T), rules auto-extend as new rows are added. Use structured references like =[@Status]="Overdue" instead of =$D2="Overdue".

How many conditional formatting rules can slow down a workbook?
Performance degrades above ~50 active rules or when a single rule's Applies To exceeds ~100,000 cells with a formula condition. Fixes: consolidate rules with OR, tighten Applies To to real data only, replace volatile functions (TODAY, NOW, INDIRECT) with static anchors where possible.

How do I copy conditional formatting rules to another sheet?
Use Format Painter: select a formatted cell, press Ctrl+Shift+C, paint over the destination. For cross-workbook: copy a formatted column, paste-special with "Formats" option. Then open Manage Rules in the destination and fix any source-workbook references.