In case you want to bring focus to top or bottom N values in a dataset, the best way is to highlight them in different colors. This article will teach you how to do this with Excel's presets and set up your own conditional formatting rule based on formula.
Highlighting cells with Excel conditional formatting may sound like a trivial task. But not if you think of how many ways you can actually do it. Do you want to shade the highest or lowest numbers in a range, column, or row? Or maybe you wish to show top 3 values in each row? And it would be really nice if you could control the number of highlighted cells directly on the sheet without having to go to the Conditional Formatting Rules manager every time. This tutorial shows how to do all this and a bit more!
Highlight top or bottom N values in range
In Microsoft Excel, there are a few ways to highlight highest and lowest values with conditional formatting. Below, we provide an overview of 3 different methods, so you can choose the one that works best for you.
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:
- Select the range in which you'd like to highlight numbers.
- On the Home tab, in the Styles group, click Conditional Formatting.
- In the drop-down menu, pick Top/Bottom Rules, and then click either Top 10 Items… or Bottom 10 items…
- 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:
- Select a range with numeric values.
- On the Home tab, click Conditional Formatting > New Rule.
- In the New Formatting Rule dialog box, choose Format only top or bottom ranked values.
- In the drop-down list, select Top or Bottom, and type how many values to highlight in the box next to it.
- Click the Format button and choose any formatting you want for Font, Border and Fill.
- 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:
\=upper_left_cell>=LARGE[range, n]
SMALL function to shade bottom numbers:
\=upper_left_cell New Rule.
To highlight top n values:
=A2>=LARGE[$A$2:$C$8, $F$2] To highlight bottom n values:
=A2=LARGE[$B$2:$B$15, $E$2]To show bottom n rows:
=$B2=LARGE[$B2:$G2, 3]
The steps for creating the rule are described in , so here we only show the result. The rule is applied to all the numeric cells [B2:G2]:
To show bottom 3 rows, the formula would be:
=B2=LARGE[$B2:$G2, 3]
The LARGE function finds the 3rd largest value in B2:G2, which is 257. The formula checks if B2 is greater than or equal to 275, and if TRUE, applies the conditional formatting to that cell. Because B2 is a relative reference, Excel then virtually "copies" the formula to C2, D2, etc. As the column coordinates are locked with the $ sign [$B2:$G2], the range does not change when comparing other cells in the same row.
For example, for C2, Excel will be evaluating this formula:
=C2>=LARGE[$B2:$G2, 3]
For row 3, the formula automatically changes like shown below because all row coordinates are relative:
=B3>=LARGE[$B3:$G3, 3]
And so on.
The following tutorial can help you better understand the inner mechanics: Relative and absolute cell references in conditional formatting.
That's how to highlight top and bottom values in Excel by using conditional formatting. I thank you for reading and look forward to seeing you on our blog next week!
How do I add a status icon in Excel?
Inserting icons To insert an icon, click the Insert tab and then select Icons. The Insert Icons menu will appear. You can scroll through a wide range of subjects, including people, technology, commerce, the arts, and more. Once you find an icon you like, select it and then click Insert.
How do I set icon sets in Excel?
Format cells by using icon sets.
Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to..
On the Home tab, click Conditional Formatting..
Point to Icon Sets, and then click the icon set that you want. Tip: Icon sets can be combined with other conditional formats..
How do I color the top 3 values in Excel?
On the Home tab, click Conditional Formatting > New Rule. In the New Formatting Rule dialog box, choose Format only top or bottom ranked values. In the drop-down list, select Top or Bottom, and type how many values to highlight in the box next to it.
How do I add an icon in Excel?
Click inside the cell of the spreadsheet where you want to insert the object. On the Create New tab, select the type of object you want to insert from the list presented. If you want to insert an icon into the spreadsheet instead of the object itself, select the Display as icon check box. Click OK.