Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Saturday, June 30, 2012 2:51 PM
It appears that Excel 2010's conditional formatting is broken. I'm trying to format a range of cells using a formula. If I set conditional formatting to highlight a single cell, it works fine. However, if I try to format a *range* of cells using a single rule (again, the rule is using a formula) then only the top-left cell of the range works correctly. All of the other cells get the chosen formatting, but they never lose it. That is, the chosen highlighting never goes off for the range (except for the top-left cell, which works correctly). Has anyone else seen this or know of a way to get this working correctly?
All replies (13)
Saturday, June 30, 2012 3:08 PM ✅Answered | 3 votes
Conditional formatting works fine in Excel 2010.
Make sure that the formula uses relative cell addresses where needed.
For example, if you want to highlight cells in A2:K50 if the value of the cell in column D in the same row is larger than 100, use
=$D2>100
as formula. The column reference $D is absolute because we want to look at column D, but the row reference 2 is relative because we want Excel to adjust it automatically for the cells in other rows.
And make sure that the rule is applied to the correct range.
Regards, Hans Vogelaar
Monday, July 2, 2012 8:06 AM
Hi,
Just checking in to see if the information of Hans was helpful. Please let us know if you would like further assistance.
And also please refer to “Use a formula to determine which cells to format” of the following link to check whether the steps you applied the conditional formatting is correct.
Jaynet Zhang
TechNet Community Support
Thursday, September 19, 2013 4:28 PM
Hans is correct [of course] in his answer. The reason for this seemingly un-intuitive behavior is as follows.
Suppose, as Hans suggested, you want to conditionally format each row in a range of cells A2:K50 based on the TRUE/FALSE value in column D of each row. If you use the formula "= D2 = TRUE" as the basis for your conditional formatting, for example, then the relative addressing for both row and column will apply the conditional formatting as follows:
D2(TRUE/FALSE) > A2 formatting
E2(TRUE/FALSE) > A3 formatting
F2(TRUE/FALSE) > A4 formatting
...
Thus, only the left-most cell of each row, A2 above, (and the "top-left cell" of the entire range) is conditionally formatted based on the value in the D-column. The other cells in the row/range are conditionally formatted based on the TRUE/FALSE values in columns E through N. If you don't have TRUE/FALSE values in columns E through N, then no conditional formatting will be applied for the non-"left-most cell" in that row.
Wednesday, July 30, 2014 8:37 PM | 2 votes
It's squirrly. Sometimes it works, other times it doesn't for no apparent reason, even though the rules are applied the same way.
What would *really* be nice would be the ability to save a rule and turn on and off as needed. Would be a big help in finding differences in columnar data.
Right now you have define the rule, find the difference, then delete the rule, reselect the data, and re-input the rule the find the next difference.
Works, but VERY unwieldy!
Wednesday, May 27, 2015 1:07 PM
I realize I'm late to the party, Jwil52, but perhaps you could work around your issue of turning on and off formatting rules?
What if you make the rule and AND rule, where the first condition is your original rule, and the second condition simply reads a given cell on a given sheet. Then you can use that cell to input a 1/0/TRUE/FALSE to in effect toggle your original rule on and off.
Wednesday, September 23, 2015 10:50 AM
Hi FYI Conditional Formatting certainly does not work fine in Excel 2010 if the excel session is in manual calculation mode as for the majority of our users . Summary is that in excel 2010, hitting shift F9 only recalculates formula in sheet. It does NOT recalculate formula used in conditional formatting. Please see section at the bottom of link.
Sections from URL are listed here :
The conditional formatting in Excel 2010 'should work without having to set your calcs to automatic'. In previous versions, a simple Shift+F9 would recalculate your worksheet AND re-evaluate any conditional formatting. In Excel 2010, only the formulae are re-calculated, but the formatting is not re-applied, i.e. the previous formatting remains. This is less than ideal with complex workbooks, where setting calculations to automatic would re-calculate the entire workbook and potentially take up a lot of time.
I tested your suggestion: It does appear to work PROVIDED you did not use SHIFT+F9 in previous attempts. As you said, as soon as SHIFT is used, Excel 2010 seems to get confused, even if you do not use SHIFT subsequently.
However, SHIFT+F9 is NOT an incorrect combination of key strokes: Shift-F9 re-calculates theactive worksheet only, while F9 re-calculates the entire workbook. Again, re-calculating the workbook rather than the sheet may not be ideal if there are complex formulae on other tabs.
Like you said, previous versions of Excel, including 2007, can handle both.
Saturday, February 6, 2016 12:39 AM
I just ran into this and one point isn't clear to me. If I want to apply conditional formatting to a range of cells using relative addressing (for example, if a cell value is equal to the one above then use the "bad" format), then what address do I specify? In early versions of excel, I could do this by saying something like R(-1)C, but now if I'm trying to format a range in one shot, I'm not sure what cell address I would reference. What I have been doing is selecting one cell and create the conditional format expression and then copy the format to the range. Is there a better way?
Saturday, February 6, 2016 10:21 AM | 1 vote
When you select a range and set up conditional formatting, specify the formula as it will apply to the active cell within the selection, usually the top left cell. Excel will automatically adjust the relative references within the formula for the other cells, while the absolute references will remain the same.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
Monday, March 7, 2016 5:14 PM
Just spent about 3 hours this morning trying to get the Conditional Formatting to highlight a Row of Cells (columns) when the Trigger Cell matches a list of items and after researching this issue, finally stumbled onto this site. My formula for the CF would work perfectly for only the Trigger Cell. After reading your perfectly outlined example in using an Anchor on the top left cell of the columns to be highlighted, my spreadsheet lit up like a Christmas Tree. For reference: this is my sample formula:
=IF(ISERROR(VLOOKUP($A1,Z,1,FALSE)),FALSE,TRUE)=TRUE | Applies To: $A:$J
*And Where “Z” in this example refers to a named range.
Utterly confounded by why trying to get the Conditional Formatting in Excel to work as needed is so difficult, but grateful for your expert advise! I'll be using this info from now on when needed for other projects.
Friday, April 1, 2016 1:58 PM
Conditional formatting for me only works with the default (Red Font). I want to take a range of cells and if those cells are more than 39 fill the cell with yellow. It does not work. There is some bug with conditional formatting...I am convinced. This should be an easy format to do.
Friday, April 1, 2016 3:29 PM
Select the range.
On the Home tab of the ribbon, click Conditional Formatting > Highlight Cells Rules > Greater Than...
Enter 39 in the text box, then select Custom Format... from the dropdown to the right of it.
Activate the Fill tab.
Select Yellow.
Click OK, then click OK again.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
Wednesday, August 29, 2018 7:34 PM
Agree, it is squirrely as he##. Weird conditional formatting issues have happened to me multiple times.
I'm using the correct formula, the EXACT same formula as another book, no dice. Formula is accepted but not applied.
It appears the solution is to keep trying many many different things while pulling your hairs out one at a time.
Wednesday, May 15, 2019 7:16 AM
Had the same issue, adding a CF rule to a multi row range, using correct relative and absolute addressing, it refused to work correctly. I got it to work by selecting the first row in the range, adding the CF rules to that row only, then copying and pasting the format of that row to other rows in the range. This made it work for me.
marcus_m_scholz