Go Back

Highlight top values

The ideal way to highlight top values in excel is by conditional formatting.

What are Highlight top values function in Excel?

In excel worksheet, conditional formatting with LARGE function returns highlight top values for the given array or set of data.

How the Highlight top values function is used in Excel?

To highlight top values in excel table, the LARGE function can be used to determine or highlight top values. For instance, if you want to find 5 highest top values in an array then first determine the 5 largest value using the LARGE function. The detailed guidance to find the highest top values is discussed in below sections.

Formula or Syntax

=A1> = LARGE (data, N)

Parameters or Arguments of a Highlight top values function

  •         Data: Required: It is a set of data or array for which k-th largest value is determined.
  •         K: Required: The largest position in the data to return.


An example of the highlight top values is given below. Follow the below steps to learn how to do it:

  1. First, create the table of some data as shown below. Now, to highlight top 5 values, first use LARGE function into the F8 cell to determine 5 largest values in a set of given data. In this example, it is 123.

Figure 1. Apply Large Function

  1. Now, to calculate value greater than 123, you need to use logical operation i.e. (>=). If values greater than 123 exist in data, then =A4> = LARGE (A4:D8, E2) returns true. To highlight top values, you can use conditional formatting.

Figure 2. The top 5 values are returned

Note on Usage of Highlight top values function

The notes on usage are listed below:

  •         LARGE function returns error value #NUM!  If the array is empty or K entered is greater than array data points.


Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Using conditional formatting (CF), highlight the entire rows for top five highest positive and top five highest negative % change values . You will need to use LARGE and SMALL functions .
Solved by X. U. in 12 mins
I am using conditional formatting to highlight the top 1 number. In many cases my top value is shared by two or more cells, IE multiple cells with 2%. Sometimes conditional formatting will highlight all of the cells that share the top value like expected. Sometimes it will only highlight one or some of the cells that share the top value which is not what I want. There seems to be no rhyme or reason to it. Help?
Solved by S. Q. in 29 mins
I need to find top values within subset eg: Within a District, at mandal level, at village level - top values
Solved by T. Y. in 28 mins

Leave a Comment