Conditional formatting in Excel lives on the Home tab, in the Styles group, roughly one-third of the way across the ribbon. Click the Conditional Formatting button to open a menu of rule types: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets, New Rule, Clear Rules, and Manage Rules. On the Mac, the path is Home > Conditional Formatting; on Windows, press Alt+H+L to open the same menu from the keyboard.

That single location answers the search, but the button is only the front door. The rules it creates live in a separate layer attached to cells, and that layer behaves differently from anything else in a workbook. This explainer covers how the engine evaluates rules, where the rules hide when the button seems to do nothing, which alternatives exist, and the exact steps that resolve the most common failures.

How conditional formatting works under the ribbon

Excel stores conditional formatting not as cell content but as a rule object attached to a range. Each rule holds three things: a scope (the cells it covers), a condition (a formula or preset such as "greater than"), and a format (fill, font color, border, or number format). The ribbon button only edits that rule set. Nothing is written into the cells themselves.

The evaluation model is the deep mechanism most guides skip. Excel evaluates every visible cell against every applicable rule each time the screen repaints. It repaints after a recalculation, a scroll, a zoom change, a row insert, or a window resize. That is why formatting sometimes appears to "wake up" when you scroll away and back: the engine re-ran the rules.

Two consequences follow. First, formulas inside rules are volatile in practice even when they are not marked volatile, because they are re-evaluated on repaint. Second, rule order matters. When multiple rules apply to one cell, Excel resolves conflicts by precedence: rules higher in the Manage Rules list win when both set the same format attribute. A cell can therefore show the fill from one rule and the font color from another.

The rules also survive operations that content does not. Copying a cell with the Format Painter copies its conditional formatting. Inserting rows inside the applied range extends the range automatically; inserting rows above it usually does not.

Where the rules actually hide

The button is on the ribbon, but the rules are stored in three less obvious places, and knowing them explains most "where did it go" complaints.

Manage Rules is the master list, and it has a scope trap. The dialog defaults to showing rules for the current selection. If you click a cell with no formatting and open Manage Rules, the list looks empty even when the sheet is full of rules. Switch the "Show formatting rules for" dropdown to This Worksheet to see everything. This single dropdown resolves more confusion than any other control in the feature.

The second hiding place is the name manager and the worksheet's internal XML. Rules are saved with the sheet, travel with it when copied between workbooks in the same session, and persist through Save As. They are not in the Formulas ribbon, not in the Format Cells dialog, and not in the Styles gallery.

The third is the conditional formatting "painter" behavior in tables. Excel Tables carry their own banded-row formatting that is separate from conditional formatting, but tables also auto-extend rules when you add rows. A rule applied to A2:A100 in a table becomes A2:A200 when the table grows, which looks like the formatting moved on its own.

Comparison: conditional formatting versus neighboring tools

Tool Where it lives Changes when data changes Typical use
Conditional formatting Home > Styles group Yes, on repaint Threshold highlighting, duplicate flags, data bars
Cell styles Home > Styles group No, static Consistent headings and totals
Manual fill Home > Font group No, static One-off emphasis
Excel Table style Table Design tab Only with table structure Banded rows, header emphasis
Data validation Data tab No, gates entry instead Restricting inputs, dropdowns
Filter and slicer Data tab / PivotTable Hides rows, does not restyle Viewing subsets

The practical distinction: conditional formatting is the only tool in this list that restyles a cell based on its current value without you touching it again. Cell styles and manual fill are paint; conditional formatting is a standing instruction.

Quantified behavior: rule limits and precedence

Excel allows an effectively large but finite number of conditional formatting rules per worksheet. The documented ceiling is bounded by available memory rather than a published count, but in practice performance degrades well before any hard limit. A worksheet with several thousand rules evaluating across tens of thousands of cells will visibly slow scrolling and recalculation on typical hardware.

Precedence is numeric and explicit. In Manage Rules, each rule has an order; the topmost rule that sets a given format attribute wins for that attribute. Stop If True is the override: when a rule with Stop If True matches, Excel skips all lower rules for that cell. This is the professional lever for resolving conflicts, and it is the one most users never touch.

Percentile rules carry their own arithmetic. "Top 10%" on 250 visible rows formats the top 25 cells. "Above average" uses the mean of the evaluated range, so a single extreme outlier drags the threshold and can silence highlighting across most of the data. Median-based rules are more robust to outliers for exactly this reason.

Timeline: what happens when you change a sheet

Action Effect on conditional formatting rules
Type a new value Rules re-evaluate on the next repaint
Insert row inside the range Range usually extends automatically
Insert row above the range Range often does not extend
Delete a row Range contracts; references may shift
Copy cell with Format Painter Rule set copies to the destination
Paste values only No rules transfer; formatting layer untouched
Sort the range Rules stay attached to cells, not to row identity
Convert range to Excel Table Rules may be reapplied to the table structure

The sorting row deserves emphasis. Rules attach to cell positions, not to the data records that move through them. After a sort, the rule that once highlighted "the row for client X" now evaluates whatever value landed in those cells. The highlighting follows the cell, not the client.

Edge cases that break the button's promises

Relative references are the most common failure point. A rule written as =$A2>100 applied to B2:B100 works because the column is locked and the row is relative, so each cell compares to its own row's A value. Change it to =$A$2>100 and every cell compares to one fixed cell, which almost never matches the intent. The rule is technically valid and produces wrong highlighting.

Merged cells are the second trap. Conditional formatting evaluates the top-left cell of a merged region and paints the whole merged area. A rule that depends on values in other merged cells will read only the anchor cell, which can look inconsistent across the sheet.

The third is the difference between a blank cell and an empty string. A cell returning ="" from a formula is not blank for conditional formatting purposes; it holds a zero-length string. Rules using ISBLANK miss it. Rules comparing to "" catch it. This distinction causes more false "rule not working" reports than any formula error.

The counterintuitive caveat for professionals: a rule can display correctly and still be broken. Because formatting is a visual layer, a rule that errors internally (for example, a formula referencing a deleted name) may simply stop matching rather than show an error. The cell quietly loses its highlight without any warning. Manage Rules is the only place the fault is visible.

Warnings worth reading before you build

Red flag: rules that reference whole columns (A:A) on large sheets can slow scrolling dramatically; scope them to the used range instead. Red flag: copying a formatted sheet into a new workbook can duplicate rules rather than overwrite them, producing two competing rule sets. Red flag: conditional formatting does not survive export to CSV, because CSV stores values only. Red flag: rules applied to a filtered range still evaluate hidden rows unless a SUBTOTAL or AGGREGATE formula excludes them. Red flag: a rule that highlights "duplicates" on a column containing blank cells will often flag every blank as a duplicate.

Practical actions that resolve most problems

To find any rule quickly, select a cell and open Home > Conditional Formatting > Manage Rules, then set the scope dropdown to This Worksheet. The matching rules appear first, and the Applies To column shows the exact range.

To clear everything on a sheet, use Clear Rules > Clear Rules from Entire Sheet rather than deleting rules one by one. To remove a single stubborn rule, select the range and use Clear Rules > Clear Rules from Selected Cells.

To diagnose a non-firing rule, open Manage Rules and check three things in order: the Applies To range, the rule formula with the cursor placed on a representative cell, and whether a higher rule with Stop If True is intercepting it.

To build a reliable custom rule, use New Rule > Use a formula, write the formula against the active cell as if it were the top-left of the range, and confirm the absolute and relative references match your intent. Test on three cells: the first, a middle one, and one you expect to fail.

To keep performance acceptable, consolidate overlapping rules, avoid whole-column scopes, and prefer built-in presets (data bars, color scales) over custom formulas where both achieve the same visual effect.

Limits of the feature and what it cannot do

Conditional formatting is a display layer, not a data transformation. It cannot change values, cannot trigger macros, and cannot send alerts. It refreshes on repaint rather than in real time, so on very large sheets the visible formatting can lag behind an edit by a noticeable moment.

It also cannot reliably cross workbooks. Rules referencing another open workbook usually work while both files are open; once the source closes, the reference may fail silently and the highlighting disappears. The same applies to rules referencing cells on deleted sheets.

Finally, the feature has no built-in audit trail. Nothing logs when a rule was added, by whom, or why. On shared workbooks this makes unexplained highlighting a genuine governance problem, and the only audit method is comparing Manage Rules lists across versions.

For spreadsheets used in regulated reporting, treat conditional formatting as a presentation choice rather than a control, and document the rules alongside the workbook so a reviewer can reconstruct the logic without guessing.