How to Highlight Cell Using the If Statement in Excel (7 Ways)

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 cell if values are greater than, equal, less than, and different conditions.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Ways to Highlight Cell Using the If Statement in Excel

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.

Excel Highlight Cell If


1. Apply Conditional Formatting to Highlight Cell 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 which have values.

Excel Highlight Cell If Value is Greater Than Another Cell

  • After selecting the cells, go to,

Home → Styles → Conditional Formatting → Highlight Cells Rules → Greater Than.

Excel Highlight Cell If Value is Greater Than Another Cell

  • 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.

Excel Highlight Cell If Value is Greater Than Another Cell

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 Secondly, type the COUNTIF function in the Format values where this formula is true box. The COUNTIF function is
=COUNTIF(D6, ">170")=1
  • Thirdly, to give cells format, click on the Format box.

Excel Highlight Cell If Value is Greater Than Another Cell

  • 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.

Excel Highlight Cell If Value is Greater Than Another Cell

Excel Highlight Cell If Value is Greater Than Another Cell


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

Excel Highlight Cell If Value is Equal to Another Cell

  • 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.

Excel Highlight Cell If Value is Equal to Another Cell

  • 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 has Less Than 125 by using Conditional Formatting. To highlight cells whose value has Less Than 125, Please follow the instruction below to learn!

Steps:

  • Firstly, select cells D6 to F13.

Excel Highlight Cell If Value is Less Than Another Cell

  • Secondly, from your Home Tab, go to,

Home → Styles → Conditional Formatting → Highlight Cells Rules → Less Than

Excel Highlight Cell If Value is Less Than Another Cell

  • Thirdly, 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 which have a value of Less Than 125 are highlighted.

Excel Highlight Cell If Value is Less Than Another Cell


1.4 Highlight Cell If Cell Contains Specific Characters in Excel

In this sub-method, we will find out 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.

Excel Highlight Cell If Cell Contains Specific Characters

  • Hence, from your Home Tab, go to,

Home → Styles → Conditional Formatting → Highlight Cells Rules → Text that Contains

Excel Highlight Cell If Cell Contains Specific Characters

  • 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

Excel Highlight Cell If Cell Contains Duplicate or Unique Value

  • 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.

Excel Highlight Cell If Cell Contains Duplicate or Unique Value

  • Hence, you will get your desired output that has been given below screenshot.


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 which contain these blank cells. To highlight the blank cells 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

Excel Highlight Cell If Cell Does Not Have Value

  • 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.

Excel Highlight Cell If Cell Does Not Have Value

Step 2:

  • Now, press on the Format box.

Excel Highlight Cell If Cell Does Not Have Value

  • 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.

Excel Highlight Cell If Cell Does Not Have Value

Read More: How to Highlight a Cell in Excel (5 Methods)


Similar Readings:


2. Perform the ISERROR and VLOOKUP Functions to Highlight Cell with If Statement

In this method, we will apply the 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.

Perform the ISERROR and VLOOKUP Functions to Highlight Cell with If Statement

  • Now, from your Home Tab, go to,

Home → Conditional Formatting → New Rule

Perform the ISERROR and VLOOKUP Functions to Highlight Cell with If Statement

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.

Perform the ISERROR and VLOOKUP Functions to Highlight Cell with If Statement

  • 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.

Perform the ISERROR and VLOOKUP Functions to Highlight Cell with If Statement

  • 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 [2 Methods]


Things to Remember

👉 There is no error when The ISERROR Formula will return FALSE if the value is found.

👉 The NOT Formula reverses the ISERROR Formula’s return, thus FALSE returns TRUE.


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo