How to Highlight the Top or Bottom N Items by Conditional Formatting in Excel

Finding the top and bottom items in an Excel worksheet is  very ordinary in your work. And in this article, we will use the conditional formatting to quickly achieve this task.

In your Excel worksheet, you will sometimes need to find the top n items or bottom n items. If those cells are not in a column or in a row, you cannot sort values. Hence, it is very difficult for you to find out all those cells manually. But in Excel, you can use the conditional formatting to finish this task. Now follow the steps below and see how it works.

Highlight the Top or Bottom N Items

The image below shows a worksheet that contains the sales volume. In order to find the target cells, you need to follow the steps below.Example for Highlight Items

  1. Select the target range in the worksheet.
  2. And then click the button “Conditional Formatting” in the toolbar.
  3. After that, choose the option “New Rule” in the drop-down menu.New Rule
  4. Next in the “New Formatting Rule” window, choose the option “Format only top or bottom ranked values”.
  5. Next click the small arrow for the rank.
  6. In the drop-down list, you can see that there are two options. You can choose rank order according to your need. Here we will choose the “Top”.
  7. Next input numbers that you need. The default number is 10. And here we input the number 8 into the text box.Rank

On the other hand, you will find the option “% of the selected range”. In this example, we need to find only 8 numbers. Thus, we will not check this option If you need to find cell according to this criterion, you can also check this option.

  1. And then click the button “Format” in this window.
  2. In the “Format Cells” window, set the format for the target cell according to your need.
  3. When you have finished all the settings, click “OK” in the “Format Cells” window.
  4. Next continue clicking “OK” in the “New Formatting Rule” window.Format Cells

Now you will come back to the worksheet, you can check the result. You will find the top 8 numbers in the range are all highlighted with a different format.


Another Approach to Highlight Cells

On the other hand, there is another approach to set this formatting rule.

  1. Repeat the first two steps in the above process.
  2. And then move your cursor on the option “Top/Bottom Rules”.
  3. Thus, you will see a sub menu. In the sub menu, there are 6 options for you to choose. You can also choose according to your actual need. And here we will choose the “Top 10 Items”.Rules
  4. In the “Top 10 Items” window, input the number into the textbox. You can also click the button in this text box to set the number.
  5. Next click the small arrow in the second text box.
  6. In the drop-down list, there are some pre-defined format styles for you to choose. If you are not satisfied with those styles, you can also click the “Custom Format”. And then you will also open the “Format Cell” window.Format Cells
  7. After you finish the setting, click “OK” in the “Top 10 Items” window. The top 8 items will also be highlighted.

The difference between these two processes is that the second one contains fewer steps. But if you need to find cells according to the percentage, the first process may be more convenient. And the choice totally depends on your actual need.

Do Not Make Things Worse

Whenever you meet with Excel corruption, you should keep calm all the time. Due to our experience, most of the lost data can be recovered. If you do some incorrect manipulations, you will make things worse. You may lose access to those files and can never get back them. One of the right methods that you can take is using a potent recovery tool. This tool can repair Excel xls data and retrieve the data and information in all your Excel files.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word document corruption and outlook repair software products. For more information visit

Comments are closed.