Conditional Formatting

[vidyanand]

Hello,

I want to conditionally format columns based on quartiles. I am able to conditionally format single column at a time but I have more than 100 columns to format. It's time consuming to edit cell values for each column. So how I can put condition in conditional formatting which is applicable to all the columns.

Thanks in advance

 

[dave3009]

Under what condition should your columns be formatted?

 

[vidyanand]

All the columns need to be formatted according to quatriles based on values in columns. As values in each columns would be different so do quartile. For eg., if Column A has 1,2,3,4 & column B has 5,6,7,8 as values, 3rd Quartile (i.e. 75th Percentile) would be different for both columns.

Right now I can conditionally format Column A where I am looking to highlight value greater than 3rd Quartile of Column A but when I am trying to apply same conditional formatting to Column B, it highlights all the values as it considers 3rd Quartile of Column A as condition.

So I have to manually replace the condition in each column. I am looking for universal conditional formatting which will applicable to all the columns.

 

[dave3009]

So

Highlight column A if below 25% of a given value
Highlight column B if between 25% and 50% of a given value
Highlight column C if between 50% and 75% of a given value
Highlight column D if above 75% of a given value

 

[vidyanand]

That's perfect. But in your example, we need to give 4 different conditions for 4 columns. This is not I am looking for. I just want to apply one condition; highlight the values in columns if it is above 75% of respective column values.

 

[dave3009]

So like using a formual to determine outcome

=(B2/A2)>75%

So if column B is more than 75% of A then set the highlight. This could be applied across the four columns as in next column it would equate B as C ....

 

[vidyanand]

Thanks Dave. Simple relative reference. I thought conditional formatting doesn't allow to do so. But mistake now corrected.