Every subset has a top and a bottom năm 2024

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:

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:

\=upper_left_cell>=LARGE[range, n]

SMALL function to shade bottom numbers:

\=upper_left_cell New Rule.