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
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
Hi, Jeff. I use conditional formatting extensively and wondered if you have encountered the issue I have with it. I also apply to columns so additional rows can be added, but I also sort my rows and other activities. Over time when a row shows up not formatted and should be, I check my conditional formatting and my one rule has now propagated to 50 very chopped up references so I have to re-set my original rule and delete the other 49 rules which Excel only allows to delete one at a time. Do you know of any way to get my one rule to “stick” no matter how many rows are added or sorted? This is very frustrating to me as I spend a lot of time fixing all the conditional formatting in spreadsheets.
This is wonderful automatic conditional formatting and helpful.
Thanks 🙂
Dear Jeff
I am an avid fan of your column and extend my heartfelt thanks for keeping the education going to make us all better at Excel.
I have an Excel problem which I have not been able to solve despite much effort
I often have to do analysis on multiple different spreadsheets that span several columns (up to 25-27 wide) and many rows (about 25000-26000, typically)
The spreadsheets are all different, with different numbers of rows and columns
I apply an AutoFilter on the headings and then filter some of the the headings on different criteria for my analysis
At any time during my analysis filters may be active on several of the 25-odd columns
From time to time I need to unfilter a specific column only, or a few columns at a time.
Instead of going column to column and making two clicks each time (Click the Filter drop down, then Clear Filter) I would like create a VBA macro and link it to a tool button on my QAT toolbar so that when I click the button, all the the active cell columns (and only those columns) get unfiltered (Filter cleared)
I did a lot of searching on the web to find a solution but without success. There are VBA codes for how you can clear all the filters of all the columns at one go, or also VBA codes on how to clear the filter of a specific column if you know the column number of the filtred range. The column number is hard coded into the code. That works if your spreadsheet is always the same – eg you can clear the filter of the 6th column of the table each time. My requirement is more generic. I want to clear the filter of the columns **and only** the specific columns of my active cells by clicking a single button on the QAT, as described below.
Lets assume my filtered range is columns A to Z, with several columns filtered and some not.
I want to unfilter only columns A, B, J, L, N, so I select one cell in each of those columns (Ctrl-Click), then hit the “Unfilter” QAT button.
Only those columns get unfiltered
I was thinking of a code that would
first cycle thru the active cells, recording the column number of each active cell (A, B, J, L, N, etc in this example) and checking each time if it was back to the first cell then exiting out of the subroutine
then jump to the header row of the filtered set and record the row number of the header
then jump to each (row, column) pair in turn and unfilter that column
Could you please help me code this ?
Is there a better way ?
I am still on Office 2010, unfortunately.
Hope you can help
Thanks
Hi Rajendra – here’s the VBA code for that and it works great!
Sub ClearFiltersOfSelectedColumns()
Dim cell As Range
Dim columnNumber As Long
Dim headerRow As Long
‘ Determine the header row (assuming headers are in the first row)
headerRow = 1
‘ Loop through each selected cell
For Each cell In Selection
columnNumber = cell.Column
‘ Check if the column has a filter applied
If ActiveSheet.AutoFilter.Filters(columnNumber).On Then
‘ Clear the filter for this column
ActiveSheet.Columns(columnNumber).AutoFilter Field:=columnNumber
End If
Next cell
End Sub