A "Large" excel problem

I always face different situations while working with large data in excel and try to find out a simple solution to a problem that one often ignores. Similarly, this article is about a problem that I face very often and probably you all face on regular basis but use a combination of multiple techniques to work out. There are always ways to do complex tasks using simple approaches.

Here, the problem I am going to talk about is a very simple one. Suppose I want to find top 3 values from a column and highlight them. The simplest way is to do a conditional formatting with top 3 variables. I.e. use the following dialogue box to perform the task.

The main issue with this technique is that all of them will be highlighted using same format. The issue with single format is that we have to put extra effort to understand which one is the top 1 and which one is the 3rd value. If there are thousand of rows and the values are scattered across different rows. One can do shorting to bring them in the top, but this will definitely mess up the format and if you are working with Time Series data, it is not at all recommended.

But one can solve the above issue just by manipulating the conditional formatting rules and coupled that with a very useful excel function called "Large". The syntax of the function is =Large(array,k). The "array" part refers to the column that we are referring and "k" is the rank of the value we are looking for. For example, if we are looking for the top value then the value of "k" will be 1 and so on.

Refer to the below image, if we are looking for the top value from the following five numbers, we will use the Large() function in following way.

This will give us the value 258 as it is the largest value among the five values that we are referring. We will use the same logic under conditional formatting to highlight the value.

Lets build the concept step by step. Notice the below image. Column A has all the values, in column B we checking specific condition and the condition is written in column C. We are checking that the value in the column A is the largest value in the given array or not. If it is true, then the result is true else the result is false (result in column B). The only instance when it is true is for the value 258.

We will put the formula from column C to the conditional formatting > New Formatting Rule.

Now press OK and you are done, the top value will be highlighted in Blue. Similarly, we will add two more rule for 2nd highest (Yellow) and the 3rd highest (Green) value. The final Rule Manager window will look like the below image.

And the result will look like the below image.

In the above piece I have assumed that the reader knows about conditional formatting. Feel free to comment about any new and interesting thing you would like me to explore. Feedback is always welcome.