While working with a large worksheet in Microsoft Excel, we need to highlight cells using the If statement in Excel. You can use various ways to highlight cells based on their value in Excel. Conditional Formatting is one of the tools to highlight cells. You also can use the ISERROR and VLOOKUP functions. Today, in this article, we’ll look at the seven quick and easy techniques that Excel highlight cells if values are greater than, equal, less than, and different conditions.
How to Highlight Cell in Excel Using If Statement: 7 Ways
Let’s say we have a dataset where the Sales Representative’s Name and their area and the Number of Units Sold in different months of the first quarter by different salesmen are given in columns B, C, D, E, and F respectively. Now, we will highlight cells based on different conditions of their value. Here’s an overview of the dataset for today’s task.
1. Apply Conditional Formatting to Highlight Cells with the If Statement
Conditional Formatting is a crucial tool in Excel to highlight cells. In this method, we will learn how to highlight cells in detail by using the Conditional Formatting tool.
1.1 Highlight Cell Value is Greater Than Another Cell
Let, for our dataset, we want to find out the sales where the number of units sold is more than 150. To do that we need to highlight the cells which have a value of more than 150. Let’s follow the instructions below to learn!
Step 1:
- First, select the cells that have values.
- After selecting the cells, go to,
Home → Styles → Conditional Formatting → Highlight Cells Rules → Greater Than.
- Hence, a window named Greater Than will appear in front of you. Now, in the Format cells that are GREATER THAN box insert 150 as the cut-off value, and in the with box select the formatting style with which you want to highlight the cells. I’ve selected Light Red Fill with Dark Red Text. At last, click OK.
- After clicking on the OK box, you will be able to highlight cells that have a value greater than 150.
You can also highlight cells that contain more than that by applying the COUNTIF function. To do that, follow step 2 below.
Step 2:
- Select cells D6 to F13, and go to Conditional Formatting to select New Rule.
- While clicking on the New Rule option, a window named New Formatting Rule pops up. Firstly, select Use a formula to determine which cells to format from Select a Rule Type. Next, type the COUNTIF function in the Format values where this formula is true box. The COUNTIF function is
=COUNTIF(D6, ">170")=1- Then, to give cells format, click on the Format box.
- Hence, a Format Cells window will appear in front of you. From that window, select the Fill menu and then select Yellow color from the Background Color. At last, press OK.
- After that, again press OK.
- Finally, you will highlight cells by using the COUNTIF function whose values are more than 170.
Read More: How to Highlight Cells in Excel Based on Value
1.2 Highlight Cell If Value is Equal to Another Cell
From our dataset, we will highlight cells whose value is equal to 136. We can do that by using Conditional Formatting. To highlight cells whose value is equal to 136, follow the steps below.
Steps:
- First of all, select the cells array D6 to F13, and then, from your Home Tab, go to,
Home → Styles → Conditional Formatting → Highlight Cells Rules → Equal To
- When you press on the Equal To option, an Equal To window pops up.
- Now, in the Format cells that are Equal To box insert 136 as the cut-off value, and in the with box select Green Fill with Dark Green Text to highlight cells. At last, click on OK.
- By clicking on the OK box, you will be able to highlight cells whose value is equal to 136.
1.3 Highlight Cell If Value is Less Than Another Cell in Excel
Here, we’ll learn how to highlight cells whose value is less than another cell (Here 125) by using Conditional Formatting. To highlight cells whose value is less than 125, Please follow the instructions below to learn!
Steps:
- Firstly, select cells D6 to F13.
- Secondly, from your Home Tab, go to,
Home → Styles → Conditional Formatting → Highlight Cells Rules → Less Than
- Then, after that, a window named Less Than will appear. Now, in the Format cells that are Less THAN box insert 125 as the cut-off value, and in the with box select the Light Red Fill with Dark Red Text color to highlight cells. At last, click OK.
- Finally, you will see the cells that have a value of Less Than 125 are highlighted.
1.4 Highlight Cell If Cell Contains Specific Characters in Excel
In this sub-method, we will find cells that contain specific characters by using conditional formatting. We will highlight New York here as specific characters. Let’s follow the steps below to learn!
Steps:
- First, we select the cells B6 to F13 to highlight specific characters named New York.
- Hence, from your Home Tab, go to,
Home → Styles → Conditional Formatting → Highlight Cells Rules → Text that Contains
- After that, a Text that Contains window pops up. Now, in the Format cells that contain the text box, insert New York as the specific character, and in the with box select the formatting style with which you want to highlight the cells. I’ve selected Light Red Fill with Dark Red Text. At last click OK.
- After completing the above process, you will be able to highlight cells that contain New York as the specific character from our dataset.
1.5 Highlight Cell If Cell Contains Duplicate or Unique Value
You can also use conditional formatting to highlight cells with duplicate values or cells with Unique values. To do that, please follow the instructions below.
Steps:
- First, select your entire dataset. Then, from your Home Tab, go to,
Home → Styles → Conditional Formatting → Highlight Cells Rules → Duplicate Values
- After that select Duplicate from the box Format cells that contain and then select Light Red Fill with Dark Red Text for the formatting style in the values with, at last, press OK.
- Hence, you will get your desired output that has been given screenshot below.
1.6 Highlight Cell If Cell Does Not Have Value in Excel
Suppose we have some blank cells in our dataset, and we want to find out the cells that contain these blank cells. To highlight the blank cells using Conditional Formatting, follow the steps below.
Step 1:
- First of all, select cells B6 to F13 from our dataset and then go to,
Home → Conditional Formatting → New Rule
- To click on the New Rule option, as a result, the New Formatting Rule window will appear. Firstly, select Format only cells that contain from Select a Rule Type. Secondly, select Blanks from Format only cells with.
Step 2:
- Now, press on the Format box.
- After that, a Format Cells window will appear in front of you.
- From the Format Cells window, go to the Fill option and select a color from the Background Color. We chose Red from the Background Color option. At last, press OK.
- While clicking on the OK box, we would go back to the window named New Formatting Rule, and from that window again press OK.
- After completing the above process, you will be able to highlight the cells without value.
2. Perform the ISERROR and VLOOKUP Functions to Highlight Cell with If Statement
In this method, we will apply ISERROR and VLOOKUP functions to highlight cells. Let’s say we have a dataset where some arbitrary names are given. We highlight the Names in column B that are similar to those in column C. Let’s follow the instructions below.
Step 1:
- First, select cells B5 to B14.
- Now, from your Home Tab, go to,
Home → Conditional Formatting → New Rule
Step 2:
- After that, a 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 formula in the Format values where this formula is true. The formula is,
=NOT(ISERROR(VLOOKUP($B5, $C$5:$C$7, 1 FALSE)))- Hence, to give format, press on the Format box.
- Further, a Format Cells window will appear in front of you.
- From the Format Cells window, go to the Fill option and select a color from the Background Color. We chose Red from the Background Color option. At last, press OK.
- Now, click on the OK box, we would go back to the window named New Formatting Rule, and from that window again press OK.
- Finally, you will be able to highlight the cells that are matched with column C.
Read More: How to Highlight Cells Based on Text in Excel
Things to Remember
👉 There is no error when The ISERROR Formula returns FALSE if the value is found.
👉 The NOT Function reverses the ISERROR Formula’s return, thus FALSE returns TRUE.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to highlight cells using the IF statements will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
