How to Apply Conditional Formatting to Rows Based on Cell Value

Bottom Line: Learn to change the formatting of an entire row of data based on the value of one of the cells found in that row.

Skill Level: Intermediate

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

If you'd like to download the same file that I use in the video so you can see how it works firsthand, here it is:

Format an Entire Row Based on a Cell Value

Sometimes our spreadsheets can be overwhelming to our readers.  Especially when you have a large sheet of data with a lot of rows and columns.  The reader needs to see all the data, but we also want to draw attention to some rows based on a condition.

In this week’s post, I answer a question from Dawna, a member of one of our training programs.  She wanted to highlight the entire rows in a data set when the value in a cell in the row matched a value in a cell outside the table.

Conditional formatting applied to entire rows

For this scenario, we can use Conditional Formatting.  This is a great feature of Excel that brings life to our spreadsheets and makes them much easier to read.  

Conditional formatting also makes your files dynamic and interactive.  The user can quickly change the cell that contains the criteria and the matching rows will be highlighted. 

Dynamic changes to table based on cell value

In the image above I changed the value in cell E3 to 6. All rows that contain a 6 in column E are immediately formatted with the font & fill color I specified in the conditional formatting rule.

Conditional formatting is a fun feature that your boss and co-workers will love. Let's get it set up!

Setting Up the Conditional Formatting

The video above walks through these steps in more detail:

  1. Start by deciding which column contains the data you want to be the basis of the conditional formatting. In my example, that would be the Month column (Column E).
  2. Select the cell in the first row for that column in the table. In my case, that would be E6.
  3. On the Home tab of the Ribbon, select the Conditional Formatting drop-down and click on Manage Rules…. That will bring up the Conditional Formatting Rules Manager window.
  4. Click on New Rule. This will open the New Formatting Rule window.
  5. Under Select a Rule Type, choose Use a formula to determine which cells to format.
  6. Under Format values where this formula is true, you are going to write a very simple formula. The formula should set the cell that you want the conditional formatting applied from equal to the first cell in the column that you already identified in Step 1. So in our example the formula would read =$E$3=$E$6.
  7. To ensure that the conditional formatting applies to all of the rows in the table, we need to change the absolute relative referencing. In other words, we are going to remove that dollar sign in front of the 6 in our formula. You can either manually delete it, or you can hit F4 twice to accomplish this step.
  8. Click on the Format… button to choose whatever format options you like. You can change the font, the fill, the border, etc.

Your New Formatting Rule window should look something like this:

New Formatting Rule Window

Conditional Formatting Rule

Once you hit OK, you will be taken back to the Conditional Formatting Rules Manager window. Here you will see the rule that you just created.

Conditional Formatting Rules Manager Window

You can click on Apply, but at this point the rule will only be applied to cell E6 because that is the cell you started from and it is what's listed in the Applies to field. We want to extend this rule to the whole table.

To do that, click on the icon to the right of that field (it has an upward facing arrow) and select the range of the entire table. In the example, this would be the data ranging from B6 to G1002 (=$B$6:$G$1002).

Conditional Formatting Rules Manager Window Entire Data Set

Click Apply one more time and the new formatting applies to the entire data set.

New formatting applied to the data set

Using Other Comparison Operators

The rule that you create doesn't always have to set a value equal to (=) another value. You can change the format for rows that are

  • less than (<),
  • less than or equal to (<=),
  • greater than (>),
  • greater than or equal to (>=),
  • or not equal to (<>)

any specific value. And it doesn't have to be a number. It can be text, dates, or other data types as well.

It doesn't matter which of those comparison operators you use. The conditional formatting is just applied based on whether your logic statements are true or false.

If you'd like to get a better understanding of logic statements, as well as IF functions, I recommend this tutorial:

IF Function Explained: How to Write an IF Statement Formula in Excel

The sample file also uses a drop-down list (data validation list) in cell E3. Check out my article on How to Create Drop-down Lists in Cells – Data Validation Lists to learn more.

Conclusion

I hope this is helpful to you. If you have any questions about the process, please leave them in the comments. You can also leave any suggestions or recommendations you might have on the topic.

Thank you!

32 comments

Your email address will not be published. Required fields are marked *

  • Great Tutorial.
    I would consider using a dynamic table range (using offset, match) so that if the dataset would grow or shrink the formatting would apply still.

  • Hi Jon.

    Is it possible at the beginning of an email to indicate what version of Excel this tip can be used?

    At work I still use Office 2010 (please don’t ask why). At home we have Office 2016.

    Dave

  • One thing I’ve noticed is when you delete some lines, the conditional formatting becomes messy and even broken when using highlight duplicate for example. Have any clue to prevent this without not forgetting to delete lines?

  • Hi Jon,
    Its amazing trick.
    one thing i noticed that in most bottom of Table ($B$1003 to $G$1003 ) a list of selected functions added that calculate above data automatically.
    I tried to find how it works here but no Luck this time.
    I am just a beginner so don’t know much Tricks.
    Can you Please help me for this.

  • Jon,
    That was a great tutorial. I just applied it to one of the multitude of excel workbooks that I have created for our team and it REALLY makes the information pop.

    I played around with it and just used conditional formatting to highlight a row where two cells in that row (used to perform a cost calculation) must not be equal to -0- (or have an entry) so that a ‘visual’ error doesn’t happen.

    Thanks again and Happy New Year to you, your wife, and ‘little’ Excel masters!

  • Hi Jon,
    Thanks for this really helpful tutorial!
    I have a question- where does this formula specify that the formatting should be applied to the entire row, rather than the column or the entire sheet?
    Is it possible to specify that conditional formatting apply to highlight a particular column?
    Thanks
    Rosie

  • Why my data validation is not working in excel ? it is working properly in formula. But if I use the same in data validation it is not working.
    please help
    =AND($C$31,$E$31) using in D coulumn

  • Hello, is there a way to do highlighting based on the same value in one row but for the highlighting to extend to all columns? For example, Column A is date with columns B to D being various alphanumeric data. Rows 1 to 5 might be for Day 1; rows 6 to 12 would be data for Day 2; rows 13 to 17 would be data for Day 3 etc. What function would you use to cause rows 1 to 5 and their respective columns to be highlighted one color, then rows 6 to 12 to be highlighted an alternate color with their respective columns and so on? It can be two colors or different colors.

  • Hello, currently I have conditional formatting icons (red, yellow, green) on column N, these are date ranges using =TODAY()+60 and =TODAY()+30, but when my project reaches 100% in column Q I don’t want to keep reporting the icons in column N (red, yellow, green) but changes to a green check mark instead when Column Q changes to 100%?

    • Add another conditional format to check for completion to run in the first place of order and use the “stop if true check”

  • I am trying to set the reference to >= but it doesnt appear to be working. I have data in O4 as a number “2” to reference the value, and I want any number 2 or greater in column “I” to be formated.

    The formula I am using is currently =$O$4=$I2 and that works when the value is “2”. I tried entering the “>” symbol in various places in the formula and its not working at all.

    Can anyone tell me what I am doing wrong?

    Thanks!

  • Hi,
    I have a column of data [month in days] logging a daily value which gets progressively higher most days [+24hrs]. can I highlight the times when it doesn’t count a +24hr incremental rise?

    Thanks in advance

  • Hi,
    I am trying to format 4 cells in a row to show bottom value….. I can achieve this for one row……..

    however, I have 150 rows and want to apply to each separate row to show outcome…..basically it’s a list of 150 products (Column A) with 4 prices from different suppliers…(Column B,C,D,E) with the best price showing in F…and the best price from bcde highlighted……

    I want to avoid having to apply formatting to each row individually, as it would take too long…….

    I tried removing the absolute property from the cells in the editor so that I could copy down…..however, it defaults to that when “apply” is clicked….seems solution works universally for columns…but not for rows….. any idea how I might solve this?
    Many thanks

  • Great tip, Jon. I wish I knew this trick a lot earlier. One issue I have is that Excel does not differentiate between small and capital letters. So, I am unable to filter or conditionally format ‘a’ and “A” separately. Would you have any advice?

  • I hope you are still watching for comments and questions. I’m going a little bonkers trying to conditionally format some rows based on one cell in the row. I follow your instructions exactly, but I get some rows that highlight correctly and some that do not.

    I am tracking TY letters that must be written and mailed. I am trying to use the “status” row to conditional format. if that cell says “Mailed” I want the whole row green. My formula rule is:
    =$H6=”Mailed”
    (Column H is the Status column)
    My Applies to set is:

    =$B$6:$L$130 (the full table)

    but I get some rows green that should be, some green that shouldn’t be, and some that should be green, aren’t! I’m going crazy please help!

    I have actually tried copy/pasting my data set into a brand new Excel workbook and using “values only” to eliminate any weird formats from previous files of the spreadsheet before me.

  • How can we insure that the range affected by this rule will scale up number of rows if new rows are added to the table? Instead of $B$6:$G$1002 could we use $B$6:$G1002? Thanks.

  • Hi, I am trying to highlight the entire row based on some text and apparently, it only highlight the cell that contains the text. My formula is =$L3=”j. Lost”. Would be great if you could advise on this! Thank you! 🙂

  • this tip has worked great. Thank you. Only issue is it seems to go a bit off the rails when you add rows to the initial range. sometimes it works, sometimes not. I am trying to color the first three columns light blue when date is within 7 days of initial edit to highlight new entries. Formula I use is =(TODAY()-$D3)<=7 where $d3 contains first edit date. looking at rules over time, excel has made lots of changes to the affected range. any way to keep it to the fixed range or have excel expand and contract the range as rows are added or deleted? I used $a$3:$a$238 for applies to range.

  • Hi, I’m having trouble getting this to work for my rows.
    I want the conditional formatting applied row by row as items are sold.

    In column C, if the cell value is greater than 1, I want all the cells in that row formatted. I entered =C2>1 then I highlighted D2:02 for the formatting.

    However, no matter what I do I can’t get eliminate the absolute reference for D2:02. When I click the arrow and use F4 it clears, when I click Apply it reappears.

  • Hello
    -I have a question on conditional formatting
    -I have a 3 columns A,B and C
    In column A and B there is min and Max value
    – I have applied conditional format for first row based on min and Max value
    – if the value in the 3 column is less than min value then cell will be red
    – if the value is grater that the Max value then the 3 column 1st row will be blue
    -if the value of the 3rd column 1st row is in between the min and Max it should be green

    I have applied this condition and I got the effect but I need to condition for every row with different min and Max values
    If I drag or copy the condition the reference will be to 1st row of min and Max , not 2nd row of min and Max

  • I want conditional formatting for example if font colour is blue do this, or if back-ground color is orange do this. Please give step by step instructions.
    Many Thanks
    Ibrahim

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter