Prantick Bala
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.
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.
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.
Now you will see, the cells which have a value greater than 60 are highlighted.
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.
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.
Now you will see the cells with the top ten values are highlighted.
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.
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.
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.
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.
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.
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.
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.
As a result, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.
Finally, you will see that the row with matching value with the criteria is highlighted.
Read More: How to Highlight Cells Based on Text in Excel [2 Methods]
Similar Readings
- How to Compare Two Excel Sheets and Highlight Differences (7 Ways)
- How to Highlight Text in Text Box in Excel (3 Handy Ways)
- Fill Color in Cell Using Formula in Excel (5 Easy Ways)
- How to Highlight a Column in Excel (3 Methods)
- How to Highlight Partial Text in Excel Cell (9 Methods)
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.
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,"")>0Here, 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.
After selecting your preferred formatting color, click on OK.
As a result, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.
Finally, you will see the rows with blank cells are highlighted.
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.
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>200Here, 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.
After selecting your preferred formatting color from the Format cells box, click on OK.
Now, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.
So you will get the rows where total first quarter sales are more than 200 units highlighted.
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.
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.
As a result, all the cells which contain the text Chicago will be highlighted.
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.
First, select your dataset and go to Home > Conditional Formatting > 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$6Here 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.
After selecting your preferred formatting color from the Format cells box, click on OK.
Now, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.
Finally, you will get the rows highlighted where the cells of the F column have more values than the value of cell H6.
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.
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.
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.
Now to highlight cells based on drop-down selection, first, select your data and go to Home > Conditional Formatting > 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$6Here 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.
Select a color by which you want to highlight and press OK.
Now, you will see your formatting styles in the preview box of the New Formatting Rule window. Click on OK.
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.
If you change the name from the drop down list, the highlighted cells will also be changed based on your selection
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.
