Ask Your Question

Conditional formatting and array formula

asked 2018-05-18 14:57:51 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-05-18 15:50:52 +0100

erAck gravatar image

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.

edit flag offensive delete link more


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.

liberli gravatar imageliberli ( 2018-05-18 17:03:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-18 14:57:51 +0100

Seen: 250 times

Last updated: May 18 '18