Exceljet

Quick, clean, and to the point

Highlight data by quartile

Excel formula: Highlight data by quartile
Generic formula 
=A1>=QUARTILE(data,quart)
Explanation 

To highlight cells by quartile, you can apply conditional formatting with a formula that uses the QUARTILE function.

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:

=B4>QUARTILE(B4:F11,3)
=B4>=QUARTILE(B4:F11,2)
=B4>=QUARTILE(B4:F11,1)
=B4>=QUARTILE(B4:F11,0)

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.

How this formula works

The QUARTILE function is automatic, and will calculate the 1st quartile with an input of 1, the 2nd quartile with an input of 2, and the 3rd quartile with an input of 3. With an input of 0, quartile returns the minimum value in the data.

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.