In the example shown, we are using 4 different conditional formatting rules. Each rule highlights a quartile in the range B4:F11 using the QUARTILE function. The formulas look like this: Note that we are highlighting quartiles in a specific order, starting with quartile 4 and ending with quartile 1. It’s important that the formula be entered relative to the upper left most cell in the data, in this case cell B4. The trick in this case is to arrange the conditional formatting rules so that they run in the same direction. The first rule highlights values greater than the 3rd quartile. The second rule highlights values greater than the 2nd quartile, the 3rd rule highlights data greater than the 1st quartile, and the last rule highlights data greater than the minimum value.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
