Home > Format > Conditional > Row Format Row Based on One Cell ValueHow do you highlight a row based on a cell value in Excel? You can set up conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, if the values in column B are greater than 75, make all data cells in the same row blue. The video and notes below show you the easy steps. 
Video: Format Row with Conditional FormattingTo see how to set up the conditional formatting, you can watch the steps in my short video, and the written instructions, with screen shots, are below the video. The full transcript is also available below. 
New Formatting Rule Dialog BoxThe New Formatting Rule window opens, where you can enter the conditional formatting rule details
The lower section, Edit the Rule Description, changes automatically. Now it shows a box where you can enter your custom formula:

Do Not use IF Function in RuleIn a conditional formatting formula, you DO NOT need to use the Excel IF function. The conditional formatting rule is like a worksheet IF formula.

Enter Conditional Formatting FormulaAny conditional formatting formula must be a test, that gives a TRUE or FALSE result. For this conditional formatting rule, the formula will do a simple check:
Row and Column ReferenceBefore you create a conditional formatting rule, it's important to check which cell is active on the worksheet. If the formula will have cell references, they might need to refer to the active cell's row and/or column

Relative or Absolute ReferenceIn Excel, a cell reference can be an absolute, relative reference, or mixed reference. Which type of reference should be used in this conditional formatting formula? Note: In a cell reference, a dollar sign ($) tells Excel to lock that part of the reference
Enter Formula with Mixed ReferenceFor this formula, we need to use the last option, a mixed reference, with only the column locked $B2. With that reference, the conditional formatting will work correctly, in every cell that is selected, in the entire dataset, from A2:D8

Add FormattingThe purpose of conditional formatting is to highlight cells that meet the criteria rules. In this example, the cells should turn blue, if the formula result is TRUE. That will highlight the rows, so they're easy to spot in a long list. Follow these steps, to set the formatting:
Check the Rule SettingsBack in the Conditional Formatting Rules Manager, the new rule settings are shown
On the worksheet, the new conditional formatting rule is applied.

Absolute Reference vs Relative ReferenceIn the formula, it's important to use an absolute reference to column B, by putting a dollar sign ($) in front of the column letter  $B
Incorrect HighlightingIf we use a relative reference  B  without the $ sign, the column reference would adjust automatically, in each column of the selected range.

Change Conditional Formatting RuleAfter you create a conditional formatting rule, you might want to change it later. For example, in the previous example, a formulabased rule was created, to highlight rows with a value of 75 or more units sold. Instead of setting that minimum number (75) in the conditional formatting rule's formula, we'll put the number in a worksheet cell. That will make the number easy to see, and it can be changed quickly and easily First, set up the worksheet cell:
Existing Conditional Formatting RuleIf you want to change an existing conditional formatting rule, follow the steps below:

Conditional Formatting Rules ManagerThe Conditional Formatting Rules Manager opens, showing a list of rules for the selected cells (Current Selection).
To change one of the conditional formatting rules:

Change the Rule SettingsIn the bottom section, Edit the Rule Description, you can make changes to the rule and its formatting. In the formula, we'll refer to cell F1 on the worksheet, where a minimum number will be entered. In this example, the formula will change:
First, follow these steps to change the formula:
Next, you can also change the formatting for the rule, if you'd like to. In this example, the rule will have green fill colour, instead of blue.
In the Conditional Formatting Rules Manager, the new rule settings are shown
On the worksheet, the new conditional formatting rule is applied.

Excel Table FormulasUsually, if you refer to a cell in a named Excel table, a structured table reference. is automatically created. For example, in the screen shot below, a worksheet formula in cell H2 refers to cell B2. Because cell B2 is in a named Excel table, the reference shows the table name, and the column name
Excel automatically created that type of reference, when I clicked on cell B2, while starting the formula Conditional Formatting Formula ErrorHowever, if you try to use a structured table reference when setting up a Conditional Formatting rule, Excel will show an error message.
So, don't try to use structured table references when setting up your conditional formatting rules. Just use normal cell references, and everything should go smoothly! 
Video TranscriptThis is the full transcript for the Colour a Row in Excel Based on One Cell's Value video, at the top of this page. With Excel's conditional formatting, you can easily highlight a cell if it's over or under a certain value, or if it meets a value that you've set. But in some cases, instead of just a single cell, you might like to highlight a whole row in a table, if one of the cells in that row is over a certain number or under. In this case, we would like to highlight each row in this list if the number of units sold is greater than 75. So to do that, I'm going to select all of the rows, all of the columns in each row. So I've selected from A2 down to D10. On the Ribbon, on the Home tab, I'll click Conditional Formatting, and none of these preset rules will do exactly what I want. So I'm going down to New Rule, and in here I'll select a formula. So I'm going to use a formula to determine how to color each row. When I click that, there's a spot where I can put the formula. I want to, in each row, look at the value that's in column B. So I'll type = And we want, from every column, we want to look at column B. So we have to lock that cell. We don't want it to be relative, we want it to be absolute. So type a $ to lock that in. And then B. And we want, in this case, the active cell we can see is white, where the other cells are highlighted with blue. We can see that, in the name box, A2 is showing up. So that's the active cell, so the active row is 2. So I'm going to type 2 here. We're going to check what's in B2 and see if it's greater than 75. So that's our test. And if it is greater than 75, we want to format it. So I'll click Format and I'll choose a fill color, maybe a blue color and click OK, and click OK again. And now, any row where the number of units is greater than 75, all four cells in that row are colored blue. 
Get the Practice FileDownload the zipped Conditional Format Row Based on Cell Value sample file for this Excel tutorial. The xipped file is in xlsx format, and does not contain any macros. More TutorialsFix Conditional Formatting Extra Rules Conditional Formatting Examples Conditional Formatting Documentation 
Last updated: January 8, 2024 12:35 PM