You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
Thanks to Amit Velingkar for writing this blog post. (We are re-publishing this post first published in April 2010. Why? Because so many of you keep searching for it. This is the second in a series of oldies but goodies Excel posts.)
Conditional formatting is a popular feature and is a great way to easily identify cells with a range that meet some criteria. However, users often want to create conditional formatting rules that go beyond comparing a cell’s value to a single value or a single cell reference - row or column comparisons are commonly requested operations. In this blog post, we will learn how to use relative references in conditional formatting rules to make such tasks easier.
For example, consider the spreadsheet below where we have sales data for different branches of an organization over the years 2005 to 2009:
Let’s say we want to highlight the cells where sales have decreased compared to the previous year. By default, conditional formatting inserts an absolute reference when we select another cell as a reference. (ExcellsFun has a great video that explains relative and absolute cell references). When absolute references are used in conditional formatting rules, every cell to which the conditional formatting rule is applied is compared to that single referenced cell.
For complex conditional formatting rules, users often get around that problem by creating lots of individual rules applied to single cells. In our example, we select cell C2 and click on “Conditional Formatting -> Highlight Cell Rules -> Less Than” on the home tab. When the Less Than dialog pops up, we select the cell B2 against which we want to compare. Notice that the value is the dialog gets replaced by an absolute reference to cell B2 ($B$2).
To perform column comparisons in this fashion, the user ends up having to create a new rule for each cell and select the neighboring cell for comparison.
This small table consists of as many as 20 individual rules - each comparing the cell’s value to its neighbor on the left. Besides being a tedious task (users often automate such tasks by using subroutines), there are other problems with this approach:
- Performance hit: Having lots of individual conditional formatting rules can degrade performance.
- Difficult to Maintain: Such rules are hard to maintain. Besides having to edit multiple rules for any changes, the rules won’t adjust if you insert a new row or column.
There is an easier way to do this by using just one Conditional Formatting Rule applied to the entire range. To do this, select the entire range of cells to which you want to apply the rule (in our example: C2:F6) and click on “Conditional Formatting -> Highlight Cell Rules -> Less Than” on the home tab. When the Less Than dialog pops up, we again select the cell B2 to get an absolute reference ($B$2). However, this time we use change the reference to be a relative reference (B2).
TIP: you can use the F4 key to cycle through different types of relative references until you get B2 without the $ signs.
And there you have it; we have just one Conditional Formatting rules where every cell’s value is compared. We get exactly, the same result as before. And there’s more, the rules automatically adjust to if new rows are added in the middle of this range. For example, let us add information about a new branch in Boston.
In conditional formatting rules, cell references are relative to the top-left most cell in the Applies to range. To simplify things, I like to pretend as if I have only selected the top-left most cell and I am writing a formula in that particular cell. In our case, the top-left cell C2 was compared against the cell to its left (B2). Hence, all the other cells in the range were compared with the cell to their left.
The diagrams below are examples of how to setup two commonly used CF rules – row and column comparisons. The cells with blue borders indicate the range to which this CF rule is applied, the bold blue border is used to illustrate the top-left most cell in the CF range and the red border shows the cell referenced by the CF formula. The black arrow indicates the direct relationship between the top-left cell and the cell reference, while the gray arrows indicate the implied relationships that are formed as a result.
You can always verify this by using the Conditional Formatting Rules Manager (select any cell within this range and clicking on the “Conditional Formatting -> Manage Rules” on the home tab) as shown below:
Also, you can toggle between the different types of relative references to compare against a particular column or a particular row. Relative references can also be used inside formulas to create complex Conditional Formatting rules.
Anytime you need to conditional formatting using complex cell comparisons, consider using relative references to make things easier. Remember, rules are relative to the top-left most cell in the Applies To range. I would love to hear more feedback on Conditional Formatting and ideas that you might have for future versions of Excel.
Comments: (19) Collapse
If you want to use F4 to change reference type then in most cases it is advisable to first press F2 to get into edit mode. Otherwise key strokes and mouse clicks could get you an additional reference for instance.
Another thought about how to explain relative and absolute reference would be to switch to RC mode. There it is a lot more obvious what either means.
You could use the OFFSET function in the rule in order to have your conditional formatting robust to row or column insertion.
e.g. in cell c2, the rule would instead be:
=C2
Hey please post the final version of XLL SDK for Excel 2010 to the Download Center.
Hi I have one query.
Say i have two columns. Cloumn "A"is my sales qty of last wk and Columns "B" is my current on hand qty.
I want to run CF to compare all the row entries that each Column B entry refers Column A value. If Column B value is less than Column A entry than it changes the colour'
Thanks
Vikas Dhand
@ Vikas: Select the range in column B that contains your current week data (say "B1:B10"). In Excel 2007, click on the Conditional Formatting menu on the Home Tab and choose "Highlight cell Rules"->"Less Than". In the dialog, type the cell address against whom you want to compare the top-left cell (in your case =$A1 - there is no $ sign before the row number, so this varies. However, all comparisons are made against column A).
If you data was in the range "B20:B30", you would type "=$A20" as the comparison cell.
Hello,
I have one question. I am trying to figure out how to shade the entire row if a cell falls into one of the rules' categories. As is, it will highlight that one cell. Do you know how to highlight the entire row if that cell becomes shaded?
Thanks.
Heather
1. I knew about 1 rule applied to multiple cells, but what is the syntax in VB to modify the applies to field for a conditional format. Excel 2007 won't record the syntax and I can't find it anywhere. Also, so you are aware, I am seeing a definite correlation between the # of conditional format rules and the speed of the overall excel file which is why I need to have a minimum number of rules and apply them where they are needed, but the default in Excel doesn't do this.
2. Excel 2007 never deletes the existing conditional format rules on a cell but continually adds on to that list if you copy/paste a cell that has more rules with it. Is there a way to have it always over write existing conditional format rules or have it at least ask which way. I know I can clear all rules first then copy, but other users don't understand why. This also leads to propogation of my conditional format rules that slows down my model.
Hi Amit
Thanks for the post on CF. A suggestion on your summary which says "Remember, rules are relative to the top-left most cell in the Applies To range"...
It need not be the top-left most cell; but it is the active cell in the selection. Active cell will have a white background even after selection..
@Heather: There isn't a direct way to highlight the entire row using conditional formatting in Excel today. However, you could create a customer rule based on a formula that is applied to the entire table (click on New Rule and choose the "use formula to determine..." option).
Using the same rules of relative references, it is possible to highlight entire cells by keeping the column references constant (and rows references variable). Remmember, the formula should evaluate to TRUE for the CF to be applied.
@Ken: The ModifyAppliesToRange method on the FormatConditions object should let you modify the AppliedTo range. Also, we have made changes in Excel 2010 that merges duplicate rule on copy and paste. This should help mitigate the stacking effect and the resulting performance degradation. Also, there are performance improvements in Excel 2010 for CF evaluation.
@Jacob: Thanks Jacob. You are absolutely right. Thanks for the comment. Although, on re-entry in the CF rules manager dialog, we always display the formula relative to the top-left cell.
Amit: Nice article.
One thing that I'd like to see explained: How do I edit a conditional formatting rule of ONE cell, even when the same rule has also been applied to other cells.
In the CF manager, the "Applies to" box shows reference to all cells that have that rule. If I change that to the cell I want to edit, the CF is removed from the other cells.
Nice article. This type of conditional formatting is relevant for any expression that evaluates to true. I've found much value in using dates for status sheets to automatically change the color of a row if a status item has not been completed by the due date.
@Jan: Thanks for the comment. Currently, CF is designed to work as a group and any chnages apply to the entire group. I will add your request for consideration for the next version of Excel.
@Buck: Thanks for giving us an insight into your user scenario.
Nice...can't wait to use this on MY sales data! :)
Hi,
I don't know about Office 2010, I don't have it yet. But I have a comment about Office 2007 and since I assume that it has been passed on to 2010, I will post it anyway. Despite being simple to show, it's difficult to put the problem into words so I created an Excel demo book to show it. Please, grab it at www.viol.com/.../bugexcel.xls. Basically, the problem is that Excel 2007 shows a bug when copying a range of cells with conditional formatting; it applies the formatting to a cell that shouldn't; there is a workaround but even so Excel 2007 behavior, regarding copying the conditionally formatted cells, seems not appropriate. Excel 2003, on the other hand, does it all right. I wonder if Excel 2010 shows the same behavior as Excel 2007.
Comments: (loading) Collapse