Conditional formatting is a feature in Excel that allows you to apply different formats (such as colors, icons, or data bars) to cells or ranges based on certain criteria or rules. You can use conditional formatting to highlight the top 3 and lowest number in a list of values, for example, to identify the best and worst performers, the highest and lowest prices, or the most and least frequent occurrences.
To find the top 3 and lowest number using conditional formatting in Excel formula, you need to use two functions: LARGE and SMALL. The LARGE function returns the k-th largest value in a range of values, where k is a positive integer. The SMALL function returns the k-th smallest value in a range of values, where k is a positive integer. For example, LARGE(A1:A10,1) returns the largest value in the range A1:A10, and SMALL(A1:A10,1) returns the smallest value in the range A1:A10.
To apply conditional formatting based on these functions, you need to use a formula that evaluates to TRUE or FALSE for each cell in the range. For example, to highlight the top 3 values in the range A1:A10, you can use the formula =A1>=LARGE($A$1:$A$10,3). This formula compares the value in A1 with the third largest value in the range A1:A10, and returns TRUE if A1 is greater than or equal to that value, and FALSE otherwise. By copying this formula to the other cells in the range, you can create a logical test that determines which cells should be formatted.
Procedures
To find the top 3 and lowest number using conditional formatting in Excel formula, follow these steps:
- Select the range of values that you want to format. For example, select A1:A10.
- On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.
- In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format.
- In the Format values where this formula is true box, enter the formula that corresponds to the condition that you want to apply. For example, to highlight the top 3 values, enter
=A1>=LARGE($A$1:$A$10,3). Make sure to use absolute references for the range of values, and relative references for the current cell. - Click Format, and then select the format that you want to apply. For example, click the Fill tab, and then select a color.
- Click OK to close the Format Cells dialog box, and then click OK to close the New Formatting Rule dialog box.
- Repeat steps 2 to 6 for the other condition that you want to apply. For example, to highlight the lowest value, enter
=A1=SMALL($A$1:$A$10,1), and select a different format.
Explanation
To illustrate how to find the top 3 and lowest number using conditional formatting in Excel formula, let’s use a scenario with real numbers. Suppose you have a list of sales figures for 10 products in the range A1:A10, as shown in the table below:
| Product | Sales |
|---|---|
| A | 120 |
| B | 150 |
| C | 90 |
| D | 180 |
| E | 140 |
| F | 100 |
| G | 160 |
| H | 130 |
| I | 110 |
| J | 170 |
You want to highlight the top 3 and lowest sales figures in the list, using green and red colors, respectively. To do this, you can use the following formulas for conditional formatting:
- For the top 3 values, use
=A1>=LARGE($A$1:$A$10,3). This formula compares the value in A1 with the third largest value in the range A1:A10, which is 160. If A1 is greater than or equal to 160, the formula returns TRUE, and the cell is formatted with green color. If A1 is less than 160, the formula returns FALSE, and the cell is not formatted. By copying this formula to the other cells in the range, you can highlight the top 3 values, which are 180, 170, and 160. - For the lowest value, use
=A1=SMALL($A$1:$A$10,1). This formula compares the value in A1 with the smallest value in the range A1:A10, which is 90. If A1 is equal to 90, the formula returns TRUE, and the cell is formatted with red color. If A1 is not equal to 90, the formula returns FALSE, and the cell is not formatted. By copying this formula to the other cells in the range, you can highlight the lowest value, which is 90.
The result of applying these formulas for conditional formatting is shown in the table below:
| Product | Sales |
|---|---|
| A | 120 |
| B | 150 |
| C | 90 |
| D | 180 |
| E | 140 |
| F | 100 |
| G | 160 |
| H | 130 |
| I | 110 |
| J | 170 |
Other Approaches
Besides using the LARGE and SMALL functions, you can also use other approaches to find the top 3 and lowest number using conditional formatting in Excel formula. For example, you can use the RANK function, which returns the rank of a value in a range of values. The rank is the position of the value in the sorted list, where the smallest value has rank 1, the second smallest value has rank 2, and so on. For example, RANK(A1,$A$1:$A$10,0) returns the rank of the value in A1 in the range A1:A10, in descending order. You can use this function to create formulas for conditional formatting, such as:
- For the top 3 values, use
=RANK(A1,$A$1:$A$10,0)<=3. This formula returns TRUE if the rank of the value in A1 is less than or equal to 3, and FALSE otherwise. - For the lowest value, use
=RANK(A1,$A$1:$A$10,1)=1. This formula returns TRUE if the rank of the value in A1 is equal to 1, and FALSE otherwise.
Another approach is to use the PERCENTILE function, which returns the k-th percentile of a range of values, where k is a value between 0 and 1. The k-th percentile is the value that splits the range into two parts, such that k% of the values are less than or equal to that value, and (100-k)% of the values are greater than or equal to that value. For example, PERCENTILE($A$1:$A$10,0.9) returns the 90th percentile of the range A1:A10, which is 170. You can use this function to create formulas for conditional formatting, such as:
- For the top 3 values, use
=A1>=PERCENTILE($A$1:$A$10,0.7). This formula returns TRUE if the value in A1 is greater than or equal to the 70th percentile of the range A1:A10, and FALSE otherwise. - For the lowest value, use
=A1=PERCENTILE($A$1:$A$10,0). This formula returns TRUE if the value in A1 is equal to the 0th percentile of the range A1:A10, which is the minimum value, and FALSE otherwise.
These are some of the possible ways to find the top 3 and lowest number using conditional formatting in Excel formula. You can experiment with different functions and parameters to achieve the desired result. Conditional formatting is a powerful tool that can help you visualize and analyze your data in Excel.
