Format Row Based on One Cell

Apply 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 over a set value, make the row blue

Format Entire Row with Conditional Formatting

You can apply 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 over a set value, make the row blue.

In this example, we'll colour cells blue, if the number of units, in column B, is greater than 75. At a glance, we'll be able to see which dates have a high number of units.

Video: Format Row with Conditional Formatting

You can watch the steps in this video, and the written instructions are below the video. The full transcript is also available below.

Format the Cells

1. Select the cells that you want to format (A2:D4 in this example)
2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
3. In the New Formatting Rule window, click Use a Formula to Determine Which Cells to Format
4. In the rule box, enter a formula that refers to the active cell in the selection. In this example, we selected A2:D4, and cell A2 is the active cell. So, in the formula, we'll refer to cell B2, because it's in the active row.

        =$B2>75

We use an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B.

If we used a relative reference (B), the formula will be adjusted in each column, and won't work properly. Each cell would refer to the cell to its right, instead of refering to the cell in column B.

5. Click the Format button.
6. In the Format Cells dialog box, select the formatting options that you want. In this example a medium blue fill colour was selected. You could also select a Font format, and a Border format.

   

7. Click OK to close the Format Cells dialog box.

Rows with a number greater than 75 in the Units column, are highlighted with blue fill colour.

Video Transcript

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

Download the Sample File

Download a zipped sample file for this Excel tutorial. The xipped file is in xlsx format, and does not contain any macros. This example is on the sheet named MultiCell.

More Tutorials

Conditional Formatting Intro

Conditional Formatting Examples

Conditional Formatting Documentation

Conditional Formatting Data Bars  

Format Row Based on One Cell (2003)

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.