Post actualizado el día octubre 24, 2026 by DeiviSanzPlay
The paradigm shift: from static traffic lights to living visual signals
For years, classic conditional formatting accustomed us to three or four preset rules: greater than, less than, between. That model has fallen short. The recent trend in spreadsheets is moving toward rules that draw on other cells, external lists, or even the workbook’s own structure, so that color stops being a manual decision and becomes a layer of reactive information.
According to Microsoft’s official documentation, current versions of Excel (both in Microsoft 365 and in the 2021 and later editions) allow conditional formatting to be combined with absolute and relative references within the same rule. That detail, which sounds minor, is what opens the door to truly flexible automatic color systems.
Three approaches gaining ground in 2026
1. Formula-based rules with mixed references
The trick is to lock the column but leave the row free: =$D2=»Pending» applied to the entire range $A$2:$H$200. This way, each row is colored according to its own status without creating a rule per row. It is the technique that generates the most queries on technical forums and the one that is least explained in basic manuals.
A common mistake: selecting the formula first and then the range, instead of selecting the range and writing the formula referring to the top-left corner. If the reference does not match that active cell, the shading appears shifted and looks «broken.»
2. Color lists managed from a helper table
Instead of hardcoding the color inside the rule, a table is created with two columns: status and color code (for example, in hexadecimal format). Then a formula rule is used that compares the cell value with the table. The practical advantage is that changing the entire report’s palette is reduced to editing two cells, without touching the conditional formatting.
3. Color scales with a shifted midpoint
Traditional three-color scales center the gradient on the 50th percentile. The new features allow the midpoint to be set at a specific value (a sales target, a stock threshold). This avoids the misleading effect of a «normal» data point appearing in red simply because the sample distribution is skewed.
What automatic color does not solve
It is worth being honest: conditional formatting is not a validation tool. If two rules overlap and both are true, Excel applies the one with the highest priority according to the order of the list, and that order is not always obvious when sheets are copied between workbooks. In addition, calculated colors are not reliably exported to all file formats: when saving as CSV or printing in black and white, the signal disappears.
It also does not work well for auditing historical changes. If you need to know when a value changed, color is of no use; you need a log or a column with a timestamp.
A specific case: expiration control
In an inventory template with 800 items, the most useful rule was not «expires in less than 30 days,» but a combination of two conditions: =AND($F2<>»»,$F2-TODAY()<=30) applied only to rows where the departure date is not empty. Without that prior check, rows pending receipt were tinted red and the alert lost all its value. It is the kind of adjustment that only appears when working with real, dirty data.
Recommendations before deploying automatic colors
- Define a maximum of three colors with clear meaning; more variety is confusing.
- Document on a separate sheet which rule paints what and in what priority order.
- Test the workbook in the Excel web version, where some complex rules render differently.
- Do not use color as the sole indicator: accompany it with text or icons for those who do not distinguish tones well.
The official documentation insists on reviewing accessibility; industry studies on data visualization point in the same direction: color speeds up reading, but it does not replace explicit data.
