Highlight the Top Sales through Conditional Formatting

In this article, we will learn to Highlight the Top Sales through Conditional Formatting.

In data analysis and MIS reporting, we often get the need to highlight the top and bottom values. This helps us to easily identify focus areas. In this article, we will learn how we can highlight the top or bottom values in Excel data sets, using conditional formatting.

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here In range A2:C7 I have a formula =RANDBETWEEN(10,100). This formula generates a random number between 10 and 100, in each cell, whenever a change made in the file. I want to highlight the smallest 3 numbers in this range.

To highlight the smallest three value in excel, follow these steps:

Select the range.
Go to Home ? Conditional Formatting ? New Rule.
Here, select "format only top or bottom ranked value"
In the drop-down, select bottom.
In Value box, write 3.
Select the formatting of the cell for bottom 3 values. I have selected red fill.

Hit Ok. It is done. Now the bottom three values in this range will be highlighted with red fill dynamically.

This was easy. But things get a little bit tricky when we add another criteria for highlighting bottom values.

It is easy to just highlight 3 smallest value in excel but it gets a little bit tricky when we add criteria. In this article, we will learn how to highlight the smallest 3 values with a condition.

Generic Formula in Conditional Formating

=AND(check cell=criteria, value cell<=SMALL(IF(criteria range=criteria,value range),3))

Check cell: It is the cell that you want to check if it meats the first criteria (the category).

Criteria: The criteria that you want to filter.

Value cell: It is the cell that contains a numeric value. We will compare it with 3rd smallest value in its category, in the value range.

Criteria range: It is the absolute range that contains the criteria.

Value range: The range that contains all values.

Let’s see an example to make things clear.

Example: Highlight the bottom three sales done by Department 1

Here I have a sample work file that contains sales done by different departments in different months.

I want to highlight the bottom three sales done by Department 1.

Let's identify the variables we have here.

Check cell: $B2 (we want to check if B2 contains "dealership 1". Since we don't want the column to change, we use $ sign. This makes the whole row highlight.)

Criteria: "Department 1"

Value cell: $C2

Criteria range: $B$2:$B$12

Value range: $C$2:$C$12

Now that we know all the arguments we need to pass to the formula, its time to implement the above generic formula on data set.

Select the data. Go-to home ? conditional formatting ? New Rule
Select "Use a formula to determine which cell to format" option.
In the text box below, write this formula

=AND($B2="Dealership 1",$C2<=SMALL(IF($B$2:$B$12="Dealership 1",$C$2:$C$12),3))

Select the formatting from using format button. I have used orange (maybe, don't judge my gender) fill.

Hit the OK button. And it's done. Bottom three or say smallest 3 sales from department 1 is highlighted.

How does it work?

Let's break down the formula from inside.

This formula is an array formula but CTRL+SHIFT+ENTER is not needed in conditional formatting.

Since we have two conditions, first is the Dealership 1 and second is values less than or equal to 3rd smallest value in Dealership 1. Both need to be TRUE. For that, we use AND function.

$B2="Dealership 1"

First criteria is easy to check. We simply put this statement $B2="Dealership 1". It will be TRUE if the current cell B2 contains Dealership 1.

$C2<=SMALL(IF($B$2:$B$12="Dealership 1",$C$2:$C$12),3)

For the second criteria, we need to filter Dealership 1 and find the 3rd smallest value in it. Then compare the criteria cell with 3rd smallest value. This is done using the statement $C2<=SMALL(IF($B$2:$B$12="Dealership 1",$C$2:$C$12),3)

When we evaluate it, this formula will solve in this way:

We check range $B$2:$B$12 and get cells that contain "Dealership 1" using statement $B$2:$B$12="Dealership 1". We will have an array of TRUE and FALSE.

$C2<=SMALL(IF({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE},$C$2:$C$12),3)

For each TRUE, value will be returned from value range $C$2:$C$12 and we will have another array.

$C2<=SMALL({12;FALSE;25;FALSE;FALSE;FALSE;FALSE;18;FALSE;FALSE;14},3)

Now SMALL Function will return the 3rd smallest value from this array. (FLASE will be ignored).

Now we have:

$C2<=18

Finally, the formula is

=AND($B2="Dealership 1",$C2<=18)

Now it checks If B2 contains Dealership 1. It does. Hence it is TRUE

=AND(TRUE,$C2<=18)

Next, it checks if C2 is less then or equal to 18 (third smallest sales in dealership 1). It is TRUE (12).

=AND(TRUE, TRUE)

Since both statements are TRUE the formula returns TRUE and we get our first ROW highlighted with colour. Same happens in each row.

Fewww!!! It was tough to explain. I hope I was clear enough to make you understand the method. In practice, it is easy.

Here we used hardcoded criteria but we can also give cell reference too. This will make the formula more flexible.

Here, for Criteria I have used cell reference of E1. Whichever dealership you have in E1, the three smallest value from that dealership will be highlighted.

The formula in conditional formatting is:

=AND($B2=$E$1,$C2<=SMALL(IF($B$2:$B$12=$E$1,$C$2:$C$12),3))

Hope this article about Highlight the Top Sales through Conditional Formatting is explanatory. Find more articles on highlighting values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

Find the partial match number from data in Excel : find the substring matching cell values using the formula in Excel.

How to Highlight cells that contain specific text in Excel : Highlight cells based on the formula to find the specific text value within the cell in Excel.

Conditional formatting based on another cell value in Excel : format cells in Excel based on the condition of another cell using some criteria in Excel.

IF function and Conditional formatting in Excel : How to use IF condition in conditional formatting with formula in Excel.

Perform Conditional Formatting with formula 2016 : Learn all default features of Conditional formatting in Excel.

Conditional Formatting using VBA in Microsoft Excel : Highlight cells in the VBA based on the code in Excel.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.