SOLVED

Conditional Formatting Question

Copper Contributor

I have numerical data that exists in many rows and columns. I'm looking for a way to highlight values in each column lower than the value in the first row of each column. It's easy enough to do with conditional formatting for a single column via formula, but I haven't found a good way to do this in a multi column range.

 

Any tips to make my life easier on this?

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution
so conditional formatting uses $ and no $ as absolute and relative references accordingly so in this case you formula should be something like:
=A2<A$1
Where A2 is the upper left corner of the Applied To range and notice the $ is only on the 1 and not the A so as excel moves to different column the column reference change also but the row (row 1) will stay the same. Adjust accordingly

@mtarler Thanks! The positioning of the $ is what was getting me.

1 best response

Accepted Solutions
best response confirmed by Sergei Baklan (MVP)
Solution
so conditional formatting uses $ and no $ as absolute and relative references accordingly so in this case you formula should be something like:
=A2<A$1
Where A2 is the upper left corner of the Applied To range and notice the $ is only on the 1 and not the A so as excel moves to different column the column reference change also but the row (row 1) will stay the same. Adjust accordingly

View solution in original post