Conditional Formatting Across Columns

Update:  After getting clarification from the reader I developed the attached model. It was bit trickier than I thought at first and I am feeling a little under the weather tonight so have not gone through a full explanation. 

 

A reader on Instagram asks: 

“Is there a function which will allow conditional formatting with multiple variables? (Age, Gender, Weight)??”

I’m following up with the reader to check I have the question right. My first instinct was to answer: “Can you apply conditional formatting across variables?” It is possible to apply conditional formatting across multiple cells. 

To do this, you need to manipulate the conditional formatting formula to allow relative references. In our example, we selected a set of age, gender, and weight. We want to highlight all selections where age is over 25. 

Go to “Conditional Formatting,” “Highlight Cells Rules,” “More Rules,” and “Use a formula to determine which cells to format.”  

Here you get total control over how to apply the formatting. In our case, we want to highlight any cells where the age is over 25. Make sure to use a relative reference so that we are always referring to the same column, but so the formula will spill down across rows. 

Also make sure you select a format! When writing your own conditional formatting formula, Excel will not provide a present format.

Now we have selected the whole row for every point in which the age is over 25. 

 

Subscribe to my mailing list.

Leave a Reply

%d bloggers like this: