Automatically Format Rows

I was recently asked how to set up conditional formatting to format a row based on the value in a column. In this post, I’ll demonstrate how to create a conditional formatting rule that automatically formats a row based on a single column value for all rows in the worksheet. Thanks Karla for your question!

Objective

Before we jump into the mechanics, let’s confirm our goal. We have a sheet with a bunch of transactions, like this:

And we want to format each past due invoice … that is, each row where the value in column E is Yes. But rather than format the rows manually, we want to define a conditional formatting rule so that Excel formats the Yes rows like this:

So, let’s get to it.

Video

Details

We’ll use these steps:

• Select range and note active cell
• Apply conditional formatting
• Paste new invoices next month

Let’s do this thing!

Select range and note active cell

First, we want to select the range we wish to format. For example, if we just want to apply the conditional formatting rule to range B7:E24, we select that range. If we want to apply the conditional formatting rule to all rows in columns B:E, then we select those columns.

In this case, we want to apply the rule to all rows in columns B:E, so we select columns B:E like this:

Now that we have selected the range, it is time to note the active cell. This is VERY IMPORTANT. If you have already tried to apply conditional formatting to range and find it isn’t working as expected, this is probably the little detail that you need to get it working.

You’ll notice that even though a range is selected, within that range there is one (and only one) active cell. It is often the top-left cell in the range (but not always, it just depends on how you select the range). In the screenshot above, the selected range is B:E while the active cell is B1.

Now that we have noted the active cell (B1), we can successfully complete the next step.

Apply conditional formatting

With the range B:E selected, we use the Home > Conditional Formatting > New Rule command.

In the resulting New Formatting Rule dialog, we opt to Use a formula to determine which cells to format.

The next step is to write a formula that returns TRUE for each cell within the range that should be formatted. Now, as you write the formula into the dialog box, you want to imagine that you are writing it into the active cell. When you click OK, Excel will essentially fill that formatting formula down and right through the selected range. This is why we noted the active cell in the previous step!

This also means that we need to understand the difference between absolute and relative cell references. A fully absolute cell reference ($A$1) DOES NOT CHANGE as the formula is filled down or right. However, a fully relative cell reference (A1) does. In addition to fully absolute ($A$1) and fully relative (A1) references, we also have mixed references ($A1 and A$1).

Here is how that all works. You want to consider the row and column part of the reference independently. If the column reference is absolute ($A), then it does not change/update as you fill the formula horizontally across columns. If the column reference is relative (A), Excel will update it (B, C, D, …) as you fill or copy the formula horizontally across columns.

If the row reference is absolute ($1), it does not change/update as you fill/copy the formula vertically through rows. If the row reference is relative (1), it does.

So, putting this all together, here is what we want. We want our formatting formula to format the entire row when the corresponding value in column E is Yes. As Excel basically fills our formatting formula down through the rows and columns within the selected range, we need the cell reference to update for each row but be locked onto column E. That is, we want the column reference to be absolute but the row reference to be relative, like this: $E1.

So, our formatting formula, which will return TRUE when the value in column E is equal to Yes is this:

=$E1="Yes"

As Excel basically fills the formatting formula down, it will update the row reference to 2, 3, 4, and so on. But, as Excel fills the formatting formula right, it will not change the column reference.

After we write the formatting formula, we basically click the Format button and pick any format we’d like to apply. In this case, I just used a light cell fill.

When we apply the conditional formatting rule … bam:

We got it!

The last detail to discuss is how to paste new invoices.

Paste new invoices next month

If you type new transactions under the current transactions range, Excel will properly apply the conditional formatting rule and the formatting is applied as expected.

If you paste new transactions (instead of typing them), you could see unexpected results depending on how you paste. Let’s unpack this for a sec.

If you copy a range of cells and do a standard paste, you are actually pasting the values AND a bunch of other stuff … including formatting. This means that you will paste over the conditional formatting rule and thus it will appear as if your worksheet is broken, like this:

So, instead of doing a standard paste, you want to paste values. This will paste the cell values but not overwrite your conditional formatting rule. You’ll see the expected formatting applied, like this:

Yay … we did it!

Conclusion

If you have any questions or comments, or other ways to accomplish this objective, please share by posting a comment below … thanks!

Sample file