Conditional Formatting if value found in range

Home » Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)

Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)

• -- By Sumit Bansal

FREE EXCEL TIPS EBOOK - Click here to get your copy

Watch Video – Highlight Rows based on Cell Values in Excel

In case you prefer reading written instruction instead, below is the tutorial.

Conditional Formatting allows you to format a cell (or a range of cells) based on the value in it.

But sometimes, instead of just getting the cell highlighted, you may want to highlight the entire row (or column) based on the value in one cell.

To give you an example, below I have a dataset where I have highlighted all the rows where the name of the Sales Rep is Bob.

In this tutorial, I will show you how to highlight rows based on a cell value using conditional formatting using different criteria.

Click here to download the Example file and follow along.

This Tutorial Covers:

• Highlight Rows Based on a Text Criteria
• Highlight Rows Based on a Number Criteria
• Highlight Rows Based on a Multiple Criteria (AND/OR)
• Highlight Rows in Different Color Based on Multiple Conditions
• Highlight Rows Where Any Cell is Blank
• Highlight Rows Based on Drop Down Selection

Highlight Rows Based on a Text Criteria

Suppose you have a dataset as shown below and you want to highlight all the records where the Sales Rep name is Bob.

Here are the steps to do this:

1. Select the entire dataset (A2:F17 in this example).
2. Click the Home tab.
3. In the Styles group, click on Conditional Formatting.
4. Click on ‘New Rules’.
5. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
6. In the formula field, enter the following formula: =$C2=”Bob”
7. Click the ‘Format’ button.
8. In the dialog box that opens, set the color in which you want the row to get highlighted.
9. Click OK.

This will highlight all the rows where the name of the Sales Rep is ‘Bob’.

Click here to download the Example file and follow along.

How does it Work?

Conditional Formatting checks each cell for the condition we have specified, which is =$C2=”Bob”

So when it’s analyzing each cell in row A2, it will check whether the cell C2 has the name Bob or not. If it does, that cell gets highlighted, else it doesn’t.

Note that the trick here is to use a dollar sign ($) before the column alphabet ($C1). By doing this, we have locked the column to always be C. So even when cell A2 is being checked for the formula, it will check C2, and when A3 is checked for the condition, it will check C3.

This allows us to highlight the entire row by conditional formatting.

Related: Absolute, Relative, and Mixed references in Excel.

Highlight Rows Based on a Number Criteria

In the above example, we saw how to check for a name and highlight the entire row.

We can use the same method to also check for numeric values and highlight rows based on a condition.

Suppose I have the same data (as shown below), and I want to highlight all the rows where the quantity is more than 15.

Here are the steps to do this:

1. Select the entire dataset (A2:F17 in this example).
2. Click the Home tab.
3. In the Styles group, click on Conditional Formatting.
4. Click on ‘New Rules’.
5. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
6. In the formula field, enter the following formula: =$D2>=15
7. Click the ‘Format’ button. In the dialog box that opens, set the color in which you want the row to get highlighted.
8. Click OK.

This will highlight all the rows where the quantity is more than or equal to 15.

Similarly, we can also use this to have criteria for the date as well.

For example, if you want to highlight all the rows where the date is after 10 July 2018, you can use the below date formula:

=$A2>DATE(2018,7,10)

Highlight Rows Based on a Multiple Criteria (AND/OR)

You can also use multiple criteria to highlight rows using conditional formatting.

For example, if you want to highlight all the rows where the Sales Rep name is ‘Bob’ and the quantity is more than 10, you can do that using the following steps:

1. Select the entire dataset (A2:F17 in this example).
2. Click the Home tab.
3. In the Styles group, click on Conditional Formatting.
4. Click on ‘New Rules’.
5. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
6. In the formula field, enter the following formula: =AND($C2=”Bob”,$D2>10)
7. Click the ‘Format’ button. In the dialog box that opens, set the color in which you want the row to get highlighted.
8. Click OK.

In this example, only those rows get highlighted where both the conditions are met (this is done using the AND formula).

Similarly, you can also use the OR condition. For example, if you want to highlight rows where either the sales rep is Bob or the quantity is more than 15, you can use the below formula:

=OR($C2="Bob",$D2>15)

Click here to download the Example file and follow along.

Highlight Rows in Different Color Based on Multiple Conditions

Sometimes, you may want to highlight rows in a color based on the condition.

For example, you may want to highlight all the rows where the quantity is more than 20 in green and where the quantity is more than 15 (but less than 20) in orange.

To do this, you need to create two conditional formatting rules and set the priority.

Here are the steps to do this:

1. Select the entire dataset (A2:F17 in this example).
2. Click the Home tab.
3. In the Styles group, click on Conditional Formatting.
4. Click on ‘New Rules’.
5. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
6. In the formula field, enter the following formula: =$D2>15
7. Click the ‘Format’ button. In the dialog box that opens, set the color to Orange.
8. Click OK.
9. In the ‘Conditional Formatting Rules Manager’ dialog box, click on ‘New Rule’.
10. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
11. In the formula field, enter the following formula: =$D2>20
12. Click the ‘Format’ button. In the dialog box that opens, set the color to Green.
13. Click OK.
14. Click Apply (or OK).

The above steps would make all the rows with quantity more than 20 in green and those with more than 15 (but less than equal to 20 in orange).

Understanding the Order of Rules:

When using multiple conditions, it important to make sure the order of the conditions is correct.

In the above example, the Green color condition is above the Orange color condition.

If it’s the other way round, all the rows would be colored in orange only.

Why?

Because a row where quantity is more than 20 (say 23) satisfies both our conditions (=$D2>15 and =$D2>20). And since Orange condition is at the top, it gets preference.

You can change the order of the conditions by using the Move Up/Down buttons.

Click here to download the Example file and follow along.

Highlight Rows Where Any Cell is Blank

If you want to highlight all rows where any of the cells in it is blank, you need to check for each cell using conditional formatting.

Here are the steps to do this:

1. Select the entire dataset (A2:F17 in this example).
2. Click the Home tab.
3. In the Styles group, click on Conditional Formatting.
4. Click on ‘New Rules’.
5. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
6. In the formula field, enter the following formula: =COUNTIF($A2:$F2,””)>0
7. Click the ‘Format’ button. In the dialog box that opens, set the color to Orange.
8. Click OK.

The above formula counts the number of blank cells. If the result is more than 0, it means there are blank cells in that row.

If any of the cells are empty, it highlights the entire row.

Related: Read this tutorial if you only want to highlight the blank cells.

Highlight Rows Based on Drop Down Selection

In the examples covered so far, all the conditions were specified with the conditional formatting dialog box.

In this part of the tutorial, I will show you how to make it dynamic (so that you can enter the condition within a cell in Excel and it will automatically highlight the rows based on it).

Below is an example, where I select a name from the drop-down, and all the rows with that name get highlighted:

Here are the steps to create this:

1. Create a drop-down list in cell A2. Here I have used the names of the sales rep to create the drop down list. Here is a detailed guide on how to create a drop-down list in Excel.
2. Select the entire dataset (C2:H17 in this example).
3. Click the Home tab.
4. In the Styles group, click on Conditional Formatting.
5. Click on ‘New Rules’.
6. In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
7. In the formula field, enter the following formula: =$E2=$A$2
8. Click the ‘Format’ button. In the dialog box that opens, set the color to Orange.
9. Click OK.

Now when you select any name from the drop-down, it will automatically highlight the rows where the name is the same that you have selected from the drop-down.

Interested in learning more on how to search and highlight in Excel? Check the below videos.

You May Also Like the Following Excel Tutorials:

• Dynamic Excel Filter – Extracts Data as you Type.
• Create a drop-down list with a search suggestion.
• How to Insert and Use a Checkbox in Excel.
• Select Visible Cells in Excel.
• Highlight Active Row/Column in a Data Range.
• Delete rows based on cell value in Excel
• How to Delete Every Other Row in Excel
• Apply Conditional Formatting Based on Another Column in Excel

63 thoughts on “Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)”

1. Sarah

   November 2020 at 10:33 pm

   I need help! example…I need a format which shows if any cells in the whole of row 2 say YES (could be more than 1) then I need a visual highlighted cell at the start of that row to flag and tell me I need to look through row 2 (because there a yes along there somewhere).

2. Ravi

   July 2020 at 7:03 pm

   Thank you so much . Helped a lot

3. Duano

   July 2020 at 9:54 am

   Hey Sumit, great stuff

4. Stuart Bennett

   June 2020 at 4:11 pm

   I followed the first one highlight based on a text value it almost worked but it’s highlighting the row above the one which has the text value in. No idea what I did wrong.

   • Ian

     July 2020 at 6:23 pm

     I had the same issue use =$C1=”bob” worked for me

5. Avraham

   April 2020 at 1:58 pm

   Thanks, excellent – it works very well and it gives a great advantage in displaying dynamic data

6. NICK BUYER

   March 2020 at 7:14 pm

   Doesnt work. No matter what I try, it doesnt work.

7. Tanis

   February 2020 at 11:26 pm

   Hello,

   My Formatting only seems to work for the first 3 rows, I have tried everything.

   Other spreadsheets where I have used this formula work just fine.
   Would anyone know what the issue may be?

   I select the table, new rule and add: =$b6=”APPROVED”
   2 out of 18 work, the rest remain white.

8. Aimee

   February 2020 at 5:49 pm

   Thank you for explaining WHY the formula does what it does. Understanding why cells behave the way they do is so key to remembering how to make them do it!

9. Tom

   January 2020 at 7:41 pm

   If I have a row of 5 cells containing numbers, how can I format it to show where more than one of the cells are greater than zero? the cell content is dynamic!

10. Swagata

    January 2020 at 9:03 am

    It works perfectly on a local excel file but stops working when that is uploaded on SharePoint. Any idea how to fix it?

11. n

    January 2020 at 4:15 pm

    Im trying to apply this rule on a drop down list, to color the entire row based on the options in the list “in process – on hold…” however its not working when typing the text between ” ” i get an error. any suggestions please?

12. George

    December 2019 at 10:20 am

    How can i highlight a certain number of rows and columns in a range based on user input? For instance i have a 10×10 range and the user inputs “3” for rows and “5” for columns. How can i turn the cells in the first 3 rows and 5 columns in my range yellow.

13. SKT

    December 2019 at 5:14 am

    How can we adjust this VBA code to only format the data from the active row that is currently selected?

14. Vira

    November 2019 at 1:12 pm

    Simple but really usefull. Thanks !

15. Travis

    November 2019 at 4:38 am

    This helped me big time with my 28,000 cell spreadsheet I’m working with. Thank you!!!

16. Keary

    October 2019 at 11:17 pm

    Is there a way to highlight a whole row if /any/ value is entered into a cell? For instance: I have a column of “Rejection Reasons” there are any number of different reasons for rejecting a thing (too many for a drop down list), but I want to highlight the row if the thing was rejected for any reason. Can I do that? Basically the opposite of highlighting based on a blank cell.

    • Linsey

      November 2019 at 2:42 am

      I think you’d just highlight cells that are greater than zero.

17. Dirk

    October 2019 at 5:03 am

    How do I do highlight the entire row directly based on the value I select from the drop-down menu? E.G. not having a SEPARATE drop-down menu: this example, make the cells in the “Sales Rep” column drop downs.

    • Linsey

      November 2019 at 2:41 am

      Wouldn’t you just highlight based on specific text? Above, the goal of highlighting based on a drop down selection is to show the rows that contain that text. It sounds like you just want different colors for different Sales Reps, which you could do by using the first tutorial provided here (highlight specific text) where each Rep has their own color.

18. JCN9801

    September 2019 at 12:27 am

    I got the conditional formatting to work for my pivot. However when any changes are made to the data displayed (e.g., collapsing a row expansion) all the CF rules are reset to apply to the first row in the PT. I using $A$10:$M$308 for the range of the CF; should I somehow do this differently?

19. blduguid

    August 2019 at 8:17 pm

    I am having a problem using Conditional formatting to find cells with unique values. I have 3 columns across my spreadsheet (D, E & F). I want to compare the 3 values in every row separately. Conditional formatting works great, 1 row at a time. How can I copy the formula down my entire spreadsheet?

20. Nataly

    July 2019 at 2:29 pm

    I have a problem with this. When I follow the instructions, the whole table is highlighted, but not only one row. What am I doing wrong?

    • Linsey

      November 2019 at 2:45 am

      You’re probably missing the dollar sign in front of the column letter. E.e. C3 vs $C3

21. Mrs Niedo

    July 2019 at 11:57 pm

    I followed your instructions and it worked for =$F2=”N/A” Then, I tried adding another formula for a new word =$F2=”PENDING” and it didn’t work anymore. What is the best way to resolve my issue? Thanks in advance

    • Den

      July 2019 at 2:17 am

      The only thing I can think of is to double check what the formula applies to. If all your data is from A2 down and across to M67, go into the Conditional Formatting drop down, select Manage Rules …, and ensure the Applies to column next to your formulas lists =$A$2:$M$67 so that the formula covers your whole table.

22. Jarren Vanman

    July 2019 at 7:23 pm

    Thanks!!

23. Sridhar

    July 2019 at 6:55 pm

    Wow!! great tutorial. I knew conditional formatting but did not know that one could do so much. Thanks!!

24. Den

    June 2019 at 4:25 am

    I was actually looking to find out how to highlight a cell based on if a number was greater then or equal to a number in the same row, not a static number, and if it was lower than a number in the same row. I was able to use your formulas here to discover it. If anyone else needs this, I selected my area I wanted to become highlighted, drop down conditional formatting, manage rules, new rule, went to use a formula to determine which cells to format, and then used the following formulas for each rule:

    =$E2=$C2

    This will cause the formula to look at the two corresponding numbers, so for me, if the generated number is greater than the estimated number, it’s green. Lower than the estimated number, it’s red. Hope this helps someone else out there.

    • Den

      June 2019 at 4:28 am

      Apparently it didn’t format it properly, the formulas are

      =$E2[LESS THAN]$C2
      =$E2[GREATER THAN]=$C2

      Hopefully this will show up.

25. Ruth Crisp

    June 2019 at 12:20 am

    Need to know how to highlight a row when the first a cell in column A contains a specific word (not =). What if Bob Dylan? Need whole row highlighted when the cell contains “Bob” regardless of last name or other words contained w/in the same cell.

    • Den

      June 2019 at 4:20 am

      Ruth, try putting “*Bob*” for the input instead. If anything comes before or after Bob it will be highlighted, so it could be Bob Dylan or Dylan, Bob or Dylan, Bob J. and it will highlight.

26. Nicola

    June 2019 at 3:20 pm

    I have a column which formats the cell to R/A/G based on priority, how can I also apply another format to make the whole line grey when it is complete, this would be a separate column.

    • Linsey

      November 2019 at 2:49 am

      Create a conditional formatting rule for complete and make sure it is listed ABOVE your priority filter in the list of conditional formats applied to that area.

27. Phil

    June 2019 at 1:38 pm

    Great tutorial I have always had trouble getting Conditional Formatting to work and it work first time using your tutorial. Don’t know why others say it doesn’t work.

28. Naeem

    April 2019 at 6:46 pm

    Fantastic

29. Rasha

    April 2019 at 2:05 pm

    It should be $D1, $D2 highlights the next row instead. Also you need to say that is applies to all the sheet

30. Josekutty Joseph

    April 2019 at 10:51 am

    Really useful. Appreciate it.

31. Kristina

    April 2019 at 12:42 am

    How can you do this if you want a row to turn a certain color as soon as on cell is populated? In my case it is populated by a date.

    • Linsey

      November 2019 at 2:53 am

      $D2>0 will highlight anything that is not blank.

32. Pen

    April 2019 at 2:00 am

    This did not work for me either. I had to do a workaround to have it come out the way I wanted.

33. Felicia

    March 2019 at 8:58 pm

    Is there a way to do the first method, but as you go?

    Basically a way that you can automatically have the row fill based on one cell, but not have to do the format every time and have this happen as you fill in your spread sheet?

    • Linsey

      November 2019 at 2:56 am

      Once you create the rule, you have to change the area it applies to so that it covers your entire table. In the “Conditional Formatting Rules Manager” you’ll select the applicable area for that rule under “Applies to”.

34. asdfasfasdf

    March 2019 at 11:39 pm

    only highlights the cells containing criteria but not the entire row

    • Linsey

      November 2019 at 2:56 am

      Once you create the rule, you have to change the area it applies to so that it covers your entire table. In the “Conditional Formatting Rules Manager” you’ll select the applicable area for that rule under “Applies to”.

35. dave

    March 2019 at 2:00 am

    doesn’t work. tried the first example and it highlighted the row ABOVE where the selected text appeared.

    • Linsey

      November 2019 at 2:57 am

      Make sure the D2 is highlighted when you’re creating a rule based on D2.

36. Efx

    March 2019 at 3:50 pm

    you forgot to include the “Applies to” cell range to get it to highlight the full row.

37. Janine

    March 2019 at 10:41 pm

    This did not work as I wanted to shade rows where the content was in a ‘Day column to shade rows with ‘Friday’ in them, but the cell was derived by a formula ‘=WEEKDAY(A6)’ so it doesn’t recognise the cell is a Friday

38. Kailas

    February 2019 at 5:50 pm

    Thanks. Useful information.

39. Katt H

    February 2019 at 6:20 pm

    It did not work. I have a table(which I then changed back to a range) from A2 to L371. My formula was =L$13=”Inactive”. Column L has blank cells, cells with the word Active, and cells with the word “Inactive”. This method, highlighted mostly cells that said Active, ?? I then capitalized all the INACTIVE rows – still nothing. I’m so confused. Why this didn’t work – does the reference cell need to be the in the beginning?

    • Linsey

      November 2019 at 3:03 am

      You put the dollar sign on the row number instead of the column number which means it will only look at the 13th row when evaluating the formatting.

40. Annie

    January 2019 at 5:15 pm

    Thank youuu!!!!!

41. joseph

    January 2019 at 11:58 am

    looking for highlight the cells and values belongs to a formula cell

42. joseph

    January 2019 at 11:57 am

    great

43. Don bosco

    January 2019 at 12:53 pm

    hi, If i type the number 5 in excel cell, 5 rows should be need to highlighted in excel . how to do this?

44. Jesse

    January 2019 at 10:03 am

    this formula has 2 typos: the quotation marks are closing quotations. Delete these in this formula and replace them and type fresh quotations around the text “Bob”

45. Vasanth R

    December 2018 at 9:25 pm

    Highlight Rows Based on a Text Criteria not working for whole row & Also not automatically applies to whole table…

46. Moshe S

    December 2018 at 12:24 am

    I agree Kristin P, something doesn’t seem right with the explanation for formatting based on text

47. Kristin P

    November 2018 at 11:18 pm

    Is there a typo in the first explanation (Highlight based on Text) as to which cells you select the formula to apply to?

    How do I conditional format Excel based on range of values?

    Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to. On the Home tab, click Conditional Formatting. Click New Rule. Select a style, for example, 3-Color Scale, select the conditions that you want, and then click OK.

    How do you highlight a cell if its value appears in a list?

    3) Highlight Items From Criteria List.

    Create a list of items you want to highlight. ... .

    Select range A2:A7..

    On the Ribbon's Home tab, click Conditional Formatting, then click New Rule..

    Click Use a Formula to Determine Which Cells to Format. ... .

    For the formula, enter. ... .

    Click the Format button..

    Select a font colour for highlighting..

    Can I use an IF formula in conditional formatting?

    One of the methods to achieve Conditional formatting based on a custom formula is using the IF statement. The IF function works on the IF/THEN/ELSE condition syntax.

    Can I use named range in conditional formatting?

    You cannot refer to a named range in conditional formatting without it (and to a regular range outside of the current sheet, for that matter).