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 on 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


Download Practice Workbook


9 Methods to Highlight Cells in Excel based on Value

1. Highlight Cells Above a Specific Values

Suppose, for our dataset we want to find out the sales where the number of units sold are more than 60. To do that we need to highlight the cells which have a value of more than 60. First, select the cells which 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

Read More: Excel VBA to Highlight Cell Based on Value (5 Examples)


2. Highlight Top Ten Values

Now, we will highlight the top ten values of our dataset. To do that, first, select the cells which 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 are highlighted.

Highlight Cells Based on Valu

Read More: How to Highlight from Top to Bottom in Excel (5 Methods)


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 (5 Easy Ways)


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 sold 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 which fulfill 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, 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 matching value with the criteria is highlighted.

After selecting your preferred formatting color, click on OK.

Read More: How to Highlight Cells Based on Text in Excel [2 Methods]


Similar Readings


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 which 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 Every 5 Rows in Excel (4 Methods)


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

Read More: How to Change Cell Color Based on a Value in Excel (5 Ways)


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 Apply Formula Based on Cell Color in Excel (5 Easy Ways)


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.

Thirdly, press on 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

Read More: VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)


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. You can find the other ways from here.

To create 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 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 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 (7 Easy Ways)


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

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo