How to highlight top or bottom 3, 5, 10 values in Excel

Highlight top and bottom values with built-in rule

The fastest way to highlight top 3, 5, 10 (or bottom n) values in Excel is to use an inbuilt conditional formatting rule. Here's how:

1. Select the range in which you'd like to highlight numbers.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. In the drop-down menu, pick Top/Bottom Rules, and then click either Top 10 Items… or Bottom 10 items…
   
4. In the dialog box that opens, specify the number of items to highlight and choose one of the predefined formatting options. To apply formatting that is not in the list, click Custom Format… . The changes you make here are reflected in your dataset immediately. So, if you are happy with the results, click OK.

For this example, we choose to highlight top 3 values with the default Light Red Fill and get this result:

More formatting options to show highest and lowest values

If you want more options than provided in the Top/Bottom presets, you can create a new rule from scratch:

1. Select a range with numeric values.
2. On the Home tab, click Conditional Formatting > New Rule.
3. In the New Formatting Rule dialog box, choose Format only top or bottom ranked values.
4. In the drop-down list, select Top or Bottom, and type how many values to highlight in the box next to it.
5. Click the Format button and choose any formatting you want for Font, Border and Fill.
6. Click OK.

For example, here's how you can highlight top 5 values with a green background color.

Highlight top or bottom values with a formula

The built-in rules described above are handy and easy to apply. However, they have one significant drawback - whenever you want to show a different number of values, you will have to change the number in the Conditional Formatting Rules Manager.

To make the rule more resilient, you can base it on a formula. In our case, we will be using:

LARGE function to shade top numbers:

SMALL function to shade bottom numbers:

To color highest or lowest values by using a formula-based rule, please follow these steps:

1. Type the number of values to highlight in a predefined input cell. For this example, we enter the number of top values in F2 and the number of bottom values in F3.
2. Select the range of numbers (A2:C8).
3. On the Home tab, click Conditional formatting > New Rule.
4. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
5. In the Format values where this formula is true box, enter one of these formulas:
   To highlight top n values:

   =A2>=LARGE($A$2:$C$8, $F$2)

   To highlight bottom n values:

   =A2<=SMALL($A$2:$C$8, $F$3)

   Where $A$2:$C$8 is the applied range, A2 is the leftmost cell of the range; F2 and F3 are the n values.

   Please notice that we lock the source range and input cells with absolute references and use a relative reference for the upper left cell.

6. Click the Format button and choose the desired formatting.
7. Click OK twice to close both windows.

For more detailed instructions, please see Create a conditional formatting rule with formula.

Done! The top 3 and bottom 3 values are highlighted in different colors.

Later, if you wish to highlight, say, top 5 values, you will just type 5 in F2, and Excel will automatically apply the change.

How this formula works:

The LARGE function returns the n-th largest value in the specified range. In our case, it finds the 3^rd largest value in A2:C8, which is 92. The formula compares each number in the selected range with the 3^rd highest value. For any cell that is greater than or equal to 92, it returns TRUE, and the conditional formatting rule is applied.