Conditional formatting and array formula

[liberli]

I find array formula offer a nice way to run complicated operations in a condensed form. For example, one typical application is to filter one or more values from one or more (sparse) cell ranges with the following advantages:

• the spreadsheet is self-sufficient and not altered/manipulated/re-elaborated in any way (see pivot tables)
• there is no need for dummy ranges (columns, rows or both)

Conditional formatting can use formula but it seems that it doesn’t support array formula. From my point of view, this means that while array formula allow us to achieve the results said before, if we wanted to show them in a graceful style at the same time, they would let us down. As an example: the filtering job mentioned above may happen dynamically within a large table whose values are variable according to some input parameter(s) and it would be nice to outline the filter result(s) of an array formula through an eye-catching style.

[erAck]

Right, array formulas don’t work in conditional formatting (in the sense of propagating element results to individual formatting) because the array is calculated once and only the final result elements are displayed by the array formula’s individual cells, which in the case of conditional formatting don’t exist.

However, you can define a conditional formatting such that it uses relative cell addressing (relative to the position where the conditional format is defined) and then apply that conditional format to a range of cells, which then use the references relative to the individual positions.

[liberli]

I see the workaround which is perhaps the standard solution. The outcome should be the same, but array formula in conditional formatting would be less fussy and more concise. Thanks.