Highlight the Top 10 Values in Excel

To highlight the top 10 values in Excel, use the built-in Top 10 Items rule in Conditional Formatting, or write a formula rule with LARGE. The built-in rule is the fastest way. The formula rule takes a minute longer but lets you colour whole rows, change N from a cell and rank within groups. This guide covers both for anyone who needs the best performers to stand out in a report or a job test.

The sample data#

The examples use this list of sales reps in A1:C9. A real list might have 100 rows and a top 10. Here there are eight rows, so most examples highlight the top 3, but the steps are exactly the same.

Elena and Grace have the same total. That tie matters later.

How to highlight the top 10 values with the built-in rule#

The Top 10 Items rule colours the largest values in your selection. It works the same on Windows and Mac:

1. Select the numbers only, for example C2:C9. Leave out the header.
2. On the Home tab, click Conditional Formatting > Top/Bottom Rules > Top 10 Items.
3. In the box on the left, keep 10, or type another number such as 3.
4. Pick a format from the list, such as Light Red Fill with Dark Red Text, or choose Custom Format.
5. Click OK.

With 3 in the box, Carla, Aisha, Elena and Grace are highlighted. That is four cells, not three, because Elena and Grace tie for third place. The same menu has Top 10%, Bottom 10 Items and Bottom 10%. The percent rules colour a share of the cells instead of a fixed number.

The built-in rule is quick, but it only colours the cells you selected. To colour whole rows, or to let the reader change N, use a formula.

How to highlight the top 10 with a LARGE formula#

LARGE returns the nth largest value in a range. =LARGE(C2:C9,3) returns 44,100, the third largest sale. Any value greater than or equal to that is in the top 3. Use that test as a conditional formatting rule:

1. Select C2:C9.
2. Click Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Type the formula below, click Format, pick a fill colour and click OK twice.

=C2>=LARGE($C$2:$C$9,3)

For a top 10 on 100 rows of data, the rule is =C2>=LARGE($C$2:$C$101,10).

Write the formula for the first cell of the selection. Excel moves C2 down to C3, C4 and so on for each cell. The range inside LARGE is locked with $ so every cell compares itself with the same list. The LARGE and SMALL lesson shows how these functions rank values, and the conditional formatting lesson covers formula rules step by step.

Let the reader choose N#

Put the number in a cell, such as F1, and point the rule at it:

=C2>=LARGE($C$2:$C$9,$F$1)

Type 5 in F1 and the top 5 light up. Type 3 and the format updates at once. This is handy on a dashboard.

How to highlight the whole row of each top value#

To colour the rep's name and region as well, apply the rule to the whole table and lock the column of the number you test.

1. Select A2:C9.
2. Create a new formula rule with:

=$C2>=LARGE($C$2:$C$9,3)

The $ before C keeps every cell in a row looking at column C. The row number has no $, so each row checks its own sales value. Without the $, cells in column A would test column A, and nothing useful happens. For more on this pattern, see how to highlight a whole row based on a value.

How to highlight the top values in each group#

Use COUNTIFS when you want the top performers per region, not overall. A rep is in their region's top 2 when fewer than 2 reps in the same region sold more:

=COUNTIFS($B$2:$B$9,$B2,$C$2:$C$9,">"&$C2)<2

Apply it to A2:C9. It highlights Carla and Aisha in North, Elena and Hugo in South, and Farid and David in East. COUNTIFS counts the reps with the same region and higher sales. Carla has 0 above her and Aisha has 1, so both pass. Grace has 2 above her, so she doesn't.

How to highlight the bottom 10 values#

Swap LARGE for SMALL and flip the comparison:

=C2<=SMALL($C$2:$C$9,3)

This highlights the three lowest sales: David (27,800), Ben (31,500) and Hugo (35,000). Or use Top/Bottom Rules > Bottom 10 Items. You can have a green rule for the top and a red rule for the bottom on the same range. If you want the top values listed in a new place, not just coloured, see how to make a top N list with formulas.

Why your top 10 highlighting looks wrong#

Most problems come from references or from the data. Check these:

1. Nothing is highlighted, or the wrong cells are. The formula was written for a cell other than the first one you selected. Select the range starting at the top row and write the rule for that row.
2. Only one column changes colour. You forgot the $ before the column letter in a whole-row rule.
3. Numbers stored as text are skipped. LARGE ignores text, so imported values may never highlight. Convert them to numbers first.
4. More than N cells are coloured. Values tie at the cut-off. This is correct behaviour. Break ties with a second column if you must show exactly N.
5. Another rule wins. Open Conditional Formatting > Manage Rules to see the order. When two rules set the same format, the rule higher in the list wins.

Colouring values is one of many highlighting tasks. You can also highlight duplicates, highlight cells that contain certain text or highlight overdue dates.

Practise it#

Practise conditional formatting in a live spreadsheet: you build the rules and the sheet checks them instantly. Then practise LARGE and SMALL to get the cut-off values right, starting with the exercise on this page.