It is often a good idea to highlight duplicate values in a data set to help easily identify the outliers. In this article we are going to explain how to find quartiles in Excel and highlight them dynamically with Conditional Formatting.
Syntax
= relative reference of first cell >= QUARTILE.INC( absolute reference of data, quartile number )
Steps
- Begin by selecting the data range B2:G7
- Open the Conditional Formatting window by going to HOME > Conditional Formatting > Add New Rule
- Select Use a formula to determine which cells to format
- Enter the formula that compares the first cell with QUARTILE function =B2>=QUARTILE.INC($B$2:$G$7,3)
- Click the Format button to edit formatting settings
- Click OK to continue and apply your settings
How
The conditional formatting feature applies selected formatting options to a cell, when a given condition is met. If this condition is provided by a formula, Excel will check whether the formula returns TRUE before applying the formatting options. Therefore, we need a formula that will return TRUE when the value in a cell exists more than once within the selected range.
The old QUARTILE or the new QUARTILE.INC functions returns the quartile of a data set, based on percentile values. They get 2 arguments that represents the data set itself and the quartile number. You can give numbers from 0 to 4 to get values between minimum and maximum.
| Quartile number | Return value |
| 0 | Minimum value |
| 1 | First quartile (25th percentile) |
| 2 | Median value (50th percentile) |
| 3 | Third quartile (75th percentile) |
| 4 | Maximum value |
Return values act as limit numbers that distinguish quartiles from each other. By comparing cell values with the limit, we can find which quartile the cell value belongs.
=B2>=QUARTILE.INC($B$2:$G$7,3)
Fortunately, we do not need to add conditional formatting to each cell one-by-one. Excel can handle this by looking at absolute and relative references. All you need to do is to make an absolute range reference (i.e. $B$2:$G$7) and leave the original cell (B2) as relative. This way, the range can be updated as other cells are added.
Please go to the link to find other articles on using conditional formatting for highlighting data.
