How to Highlight Cells in Excel Based on Value (9 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, you can use various methods to highlight cells based on their value. In this article, I’ll show you 9 methods by which you can highlight cells based on their values in different conditions.

Let’s say we have a dataset where sales area and Number of units sold in different months of the first quarter by different salesmen are given. Now we will highlight cells based on different conditions of their value.

dataset


How to Highlight Cells in Excel based on Value: 9 Methods

1. Highlight Cells Above a Specific Values

Suppose, for our dataset, we want to find out the sales where the number of units sold is more than 60. To do that, we need to highlight the cells which have a value of more than 60. First, select the cells that have values. Then, go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.

conditional formatting

After that, a window named Greater Than will appear. Now, in the Format cells that are GREATER THAN box insert the cut-off value, and in the with box, select the formatting style with which you want to highlight the cells. I’ve selected Green Fill with Dark Green Text here. At last click OK.

format box

Now you will see the cells which have a value greater than 60 are highlighted.

Highlight Cells Based on Value


2. Highlight Top Ten Values

Now, we will highlight the top ten values of our dataset. To do that, first, select the cells that have values and then go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items.

formatting

After that, a window named Top 10 Items will appear. Now, in the Format cells that rank in the TOP box insert a number. It is 10 for our example. The number determines the number of cells with top values which will be highlighted. In the with box, select the formatting style with which you want to highlight the cells. At last, click OK.

format box

Now you will see the cells with the top ten values highlighted.

Highlight Cells Based on Valu


3. Format Duplicate or Unique Values

You can also use conditional formatting to highlight cells with duplicate values or cells with Unique values. To do that, first, select your entire dataset. Then, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

conditional formatting

To highlight the duplicate values, select Duplicate from the box Format cells that contain and then select the formatting style in the box values with. For this example, I’ve selected Yellow Fill with Dark Yellow Text.

duplicate

Now, to highlight the unique values, select Unique from the box Format cells that contain and then select a different formatting style in the box values with. I’ve selected Green Fill with Dark Green Text. At last, press OK.

Highlight Cells Based on Value

As a result, all the duplicate values will be highlighted with yellow fill with dark yellow text, and unique values will be highlighted with green fill with dark green text.

Highlight Cells Based on Value

Read More: How to Highlight Selected Cells in Excel


4. Highlight Value Based on Multiple Criteria

We can also highlight cells by matching multiple criteria with the cells’ values. Suppose we want to find out the names of the salesmen who operate in New Orleans and sell more than 60 units every month of the first quarter.

In the beginning, select your dataset and go to Home > Conditional Formatting > New Rule.

formatting

After that, New Formatting Rule window will appear. Firstly, select Use a formula to determine which cells to format from Select a Rule Type.

Secondly, type the following formula in the Format values where this formula is true box.

=AND($B7="New Orleans",$C7:$F7>50)

Here, the AND function will find out the cells that fulfil both criteria. $B7=”New Orleans” is the first criteria and $C7:$E7>50 is the second criteria where $C7:$E7 is the data range.

After that, click on Format to select the formatting style.

Highlight Cells Based on Value

In the Format Cells window from the Fill tab, select the color with which you want to highlight the cells. You can choose many more formatting styles, such as Background colour, Pattern colour, Fill effects, etc. Also, you can change the border, font, and numbering format from other tabs of this window. After selecting your preferred formatting color, click on OK.

highlight cells

As a result, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.

format box

Finally, you will see that the row with a matching value with the criteria is highlighted.

After selecting your preferred formatting color, click on OK.


5. Highlight Rows which Contain Cells Without Value

Suppose we have some blank cells in our dataset, and we want to find out the rows which contain these blank cells. To highlight the rows with blank cells, select your entire dataset and go to Home > Conditional Formatting > New Rule.

conditional formatting

As a result, New Formatting Rule window will appear. Firstly, select Use a formula to determine which cells to format from Select a Rule Type.

Secondly, type the following formula in the Format values where this formula is true box.

=COUNTIF($A7:$F7,"")>0

Here, the COUNTIF function will count the cells that are empty in the cell range $A7:$F7. If the number of empty cells is greater than zero in any particular row, the row will be highlighted.

After that, click on Format to select the formatting style.

new rule box

After selecting your preferred formatting color, click on OK.

format box

As a result, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.

Highlight Cells Based on Value

Finally, you will see the rows with blank cells are highlighted.

Highlight Cells Based on Value

Read More: How to Highlight Blank Cells with Conditional Formatting in Excel


6. Create a Custom Conditional Formatting Rules to Highlight Values

You can create any custom rules on the basis of which the cells will be highlighted. Suppose we want to find out the salesmen who have sold more than 200 units in the first quarter.

First, select your dataset and go to Home > Conditional Formatting > New Rule.

formatting

As a result, New Formatting Rule window will appear. Firstly, select Use a formula to determine which cells to format from Select a Rule Type. Secondly, type the following formula in the Format values where this formula is true box.

=$C7+$D7+$E7+$F7>200

Here, the formula will find out the rows where the total first-quarter sales are more than 200 units.

After that, click on Format to select the formatting style.

format box

After selecting your preferred formatting color from the Format cells box, click on OK.

format box

Now, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.

mew rule

So, you will get the rows where total first-quarter sales are more than 200 units highlighted.

Highlight Cells Based on Value


7. Apply Conditional Formatting to Text

You can also highlight cells with text by applying conditional formatting. Suppose we want to find out how many salesmen are operating in Chicago. For that, we need to find out the cells which contain Chicago. To do that, select all the cells of your dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.

formatting

A window named Text That Contains will appear. In the box Format cells that contain the text: type the text you want to find out which is Chicago for this dataset. After that, select your preferred formatting styles and press on OK.

text box

As a result, all the cells which contain the text Chicago will be highlighted.

Highlight Cells Based on Value

Read More: How to Highlight Cells Based on Text in Excel


8. Highlight Cells Based on The Value of Another Cell

In this section, we will see how you can highlight cells based on the value of another cell. Let’s say for our dataset, there is a sales target in a month, which is inserted in cell H6. Now, we will find out the salesmen who made more sales than the sales target in April month.

dataset

First, select your dataset and go to Home > Conditional Formatting > New Rule.

new rule

As a result, New Formatting Rule window will appear. Firstly, select Use a formula to determine which cells to format from Select a Rule Type. Secondly, type the following formula in the Format values where this formula is true box.

=$F7>$H$6

Here, the formula will find out the cells of column F, which have more value than the value of cell H6.

Next, press Format to select the formatting style.

new formatting box

After selecting your preferred formatting color from the Format cells box, click on OK.

format box

Now, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.

new rule

Finally, you will get the rows highlighted where the cells of the F column have more values than the value of cell H6.

Highlight Cells Based on Value


9. Highlight Cells Based on Drop Down Selection

You can highlight cells based on your selection from the drop-down list. First, let’s create a drop-down list of the salesman in cell H6. You can create a drop-down list in various ways. I’ll show you one way in this article.

To make the drop down list, go to the Data Tools ribbon from the Data tab. Then click on the icon on Data Validation to expand it and select Data Validation from the expanded menu.

data validation

As a result, the Data Validation window will be opened. Select List from the Allow: box. After that, you will see a box named Source: will appear in the same window. Insert your data range in this box. At last, click on OK.

data validation box

As a result, a drop-down menu will be created in cell H6. If you click on the downward arrow beside this cell, you will see the list of the salesman.

drop down list

Now, to highlight cells based on drop-down selection, first, select your data and go to Home > Conditional Formatting > New Rule.

new rule

After that, New Formatting Rule window will appear. Firstly, select Use a formula to determine which cells to format from Select a Rule Type. Secondly, type the following formula in the Format values where this formula is true box.

=$A7=$H$6

Here, the formula will find the cells in your dataset where the text is similar to your drop-down selection.

Lastly, click on Format to select the formatting style.

new rule

Select a color by which you want to highlight and press OK.

format box

Now, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.

format box

Now, the row with the sales data of the salesman, which you will select from the drop-down list, will be highlighted. For example, if we select Nancy Flora from the drop-down list, the sales data of Nancy Flora will be highlighted.

Highlight Cells Based on Value

If you change the name from the drop-down list, the highlighted cells will also be changed based on your selection.

Highlight Cells in Excel Based on Value

Read More: Highlight Cells That Contain Text from a List in Excel


Download Practice Workbook


Conclusion

I hope, now you will be able to highlight cells in Excel based on their value with any type of conditions. Please leave a comment if you face any type of confusion regarding any of the discussed methods.


Related Articles


<< Go Back to Highlight CellHighlight in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo