I have a sheet with many columns, and I want to conditionally highlight each one with green for the top 25% of values (values > Q3), yellow for values between the top 25% and top 75% (values between Q1 and Q3) and red for the bottom 25% (values < Q1). I can do this for one column by using "Top/Bottom Rules", but is there a way to all the columns at once?
For example, I have the following data:
I want for the formatting to look like this, where each column's formatting depends on its own quartile information:
Is there a way to do this without having to manually do each column on its own?
2 Answers
I have found how to do this here. The way I did it for Excel 2019:
First, create the rules you want to condition your data with. In my case, this was done using Conditional Formatting > Manage Rules > New Rule > Format cells that only contain. Then, in the rule, I used the computed Q1 and Q3 to create the rules. I assume it is possible to do this using the Use formula to determine which cells to format and have the quartile formula in it. I am unsure if having $ in the cell locations matters, I did it without the $ in front of the letter (eg. A$1).
After creating the rules, select the column you created the rule on, then go to Home and click on Format Painter (located by default on the left in the Clipboard section, next to the Paste, Cut and Copy). After clicking on Format Painter, select the cells you want to apply the rules to.
Hope this helps anyone that has the same issue.
@anInputName,
In order to avoid performing multiple operations for each column, I also suggest you Use a formula to determine which cells to format, maybe you could try the following formula to format the cells.
=B2>B$17
=B2<B$16
=AND(B2>=B$16,B2<=B$17)
