Post actualizado el día octubre 24, 2026 by DeiviSanzPlay
Conditional formatting is Excel’s native way to make a cell change color when its content meets a rule. Compared to painting by hand, the advantage is that the color recalculates itself: if the data changes, the fill does too. Below you have the exact setup, with the shortcuts and the points where it usually fails.
Prepare the range before creating the rule
The most common mistake is selecting a single cell and applying the rule. Excel associates it with that cell and, when you copy, the colors get misaligned. Before touching anything:
- Select the entire range you want to color (for example, B2:B200). Don’t include the header row if it contains text, because it will also be evaluated.
- Check that the values are truly numeric. A number stored as text (with an apostrophe in front or aligned to the left) does not respond to greater than/less than rules.
- If you’re going to drag the rule to other columns, decide whether you need absolute ($B2) or relative references.
Quick rules for numeric values
With the range selected, go to Home → Conditional Formatting → Highlight Cells Rules. There you have Greater Than, Less Than, Between, and Equal To. Practical example: to mark amounts above 1,000, choose «Greater Than,» type 1000, and select a green fill. Excel creates the rule instantly across the entire range.
These rules are enough for simple thresholds, but they don’t let you compare two columns against each other. For that you need the formula option.
Custom formula: the case of comparing columns
Suppose a table where column B is the actual stock and column C is the minimum stock. You want B to be painted red when it’s below C. Select B2:B200, go to Conditional Formatting → New Rule → Use a formula and type:
=B2<C2
The key is the relative reference: B2 and C2 without dollar signs allow the rule to shift row by row. If you type =$B$2<$C$2, the entire column will be evaluated against the first row and the result will be a uniform block of color, almost always wrong.
Colors based on text or categories
For text values, the path is Text that Contains or Equal To within the same menu. A detail that goes unnoticed: the comparison is not case-sensitive, so «Pending» and «pending» will be colored the same. If you need to distinguish them, you’ll have to use a formula with the EXACT function.
When there are more than three categories, chaining rules one by one becomes unmanageable. In that case, a scale or a rule for each value is better, but with judgment: more than six or seven different colors in a table makes it unreadable, as data visualization guides recommend.
Color scales and data bars
If what you’re looking for isn’t a threshold but a gradient (low values in light, high values in dark), use Color Scale. It’s useful in sales or temperature reports, but it has a real limitation: the color depends on the minimum and maximum of the range, so a new data point can alter all the shades. If you need stable colors, set the minimum and maximum values manually instead of letting Excel calculate them.
Rule order and common errors
When several rules affect the same cell, the one at the top of the rules manager wins (Conditional Formatting → Manage Rules). If a cell isn’t painted as you expect, check the order there before redoing the rule.
Other failures that appear in practice:
- Copy and paste: when pasting new data, conditional formatting can be duplicated. Use «Paste Values» or review the rules afterward.
- Performance: thousands of rules with complex formulas slow down the workbook. A single rule covering the entire range is preferable to a hundred rules per row.
- Pivot tables: conditional formatting applied to a pivot table is lost on refresh if it isn’t configured from the table’s own options.
What conditional formatting doesn’t solve
It doesn’t change the cell’s value or serve to validate data: it only changes the appearance. Nor does it export reliably to other formats like CSV, and in black-and-white printouts the colors lose their function. For real control of the data, combine it with data validation and, if the volume is high, with Power Query.
