I have excel data. Names in rows and data about names in columns:
Name Data1 Data2 Data3 ...
AA 1 7 5 ...
BB 8 5 3 ...
CC 5 9 7 ...
... ... ... ... ...
Now imagine conditional formatting in each column - highlight top 10% values in each column. That is easy conditional formatting what can I do.
But my question is: How to highlight rows which has let's say 3 and more highlighted cells by conditional formatting in columns?
Short of using VBA, you'd need to "Use a formula to determine which cells to format." In that formula, add each condition found on the row, and format the cell if the sum >= 3.
In the following example, data is conditionally formatted if it's within the top 20% value per column:
We need a formula that determines if 3 or more data points are within the top 20% per column.
If a number is in the top 20% of a dataset, it's in the 80th percentile. (100% - 20% = 80%). Excel has a PERCENTILE function, which is just what we need.
=PERCENTILE(A$1:A$15,0.8) returns the number 8. So any value 8 and above should be highlighted, which we see in the example.
We would get TRUE or FALSE if we compared each value in the A column with the percentile. I've done the same for all columns below, giving us a grid of TRUEs and FALSEs.
Now we need to determine if there are 3 or more TRUEs. This is easy to do, because TRUE is represented as a 1 in Excel, and FALSE is represented as a 0. So we can add the TRUEs and FALSEs per row:
Combine all this work into a conditional format formula, and this is what you get:
=($A1>=PERCENTILE($A$1:$A$15,0.8))+
($B1>=PERCENTILE($B$1:$B$15,0.8))+
($C1>=PERCENTILE($C$1:$C$15,0.8))+
($D1>=PERCENTILE($D$1:$D$15,0.8))+
($E1>=PERCENTILE($E$1:$E$15,0.8)) >= 3
I used the above formula for my example data, so rows with 3 or more highlighted cells get bold numbers and red borders:
Select columns B:F (here five data columns are assumed, the formula would need extra terms for more than that) and as a Conditional Formatting formula rule apply:
=IF($B1>=LARGE($B:$B,COUNT($B:$B)/10),1)+IF($C1>=LARGE($C:$C,COUNT($C:$C)/10),1)+IF($D1>=LARGE($D:$D,COUNT($D:$D)/10),1)+IF($E1>=LARGE($E:$E,COUNT($E:$E)/10),1)+IF($F1>=LARGE($F:$F,COUNT($F:$F)/10),1)>2
with formatting of your choice (presumably that does not conflict with that for the 10% formatting). Column labels will also be formatted unless range is abbreviated.
