G
Guest
How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.
error message when I choose the column.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
Yes but you do it by first selecting the column, then applying the format on
the active cell
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
the active cell
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
G
Guest
You'll need to provide more information. When you say you're getting an
error message, does this mean that you aren't even able to select the column?
Or is the error coming from your Conditional Formatting formula? What is
the error message? It would also be helpful if you provided an example of
how you want your formatting to appear, and what conditions should trigger it.
error message, does this mean that you aren't even able to select the column?
Or is the error coming from your Conditional Formatting formula? What is
the error message? It would also be helpful if you provided an example of
how you want your formatting to appear, and what conditions should trigger it.
S
spai461
I'm new to Excel 2007. Trying to apply conditional formatting based on a
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional formatting
automatically adjusted the row references such that in the example above it
would go $c1, $c2, $c3, etc...
But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell with
in that column it shows the same reference as in the first row (i.e., $c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.
Is this a glitch, or am I missing something?
Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional formatting
automatically adjusted the row references such that in the example above it
would go $c1, $c2, $c3, etc...
But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell with
in that column it shows the same reference as in the first row (i.e., $c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.
Is this a glitch, or am I missing something?
Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)
S
spai461
RESOLOVED
The problem I was having had nothing to do with cell references; it was a
logic error. Relative refs work fine in conditional formatting formulae.
The problem I was having had nothing to do with cell references; it was a
logic error. Relative refs work fine in conditional formatting formulae.
T
T. Valko
What "method" did you use to set the CF?
Use the "Use a formula to determine which cells to format" method.
=C1=42
Use the "Use a formula to determine which cells to format" method.
=C1=42
T
tarday
I'm having the same issue, but have not found the resolution as you did.
What gives? I still can't get the cf to extend down a column with the
correct references - it still wants to compare to the first row. Thanks for
any help you can provide.
What gives? I still can't get the cf to extend down a column with the
correct references - it still wants to compare to the first row. Thanks for
any help you can provide.
R
reylon
Hi, I have the same problem. When you paste you cannot decide with $ if
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks
R
reylon
Are you sure you resolved? If I copy and paste conditional formatting in a
single cell it goes very well, but if I copy on a multiple cells I had the
problem that you described and the $ doesn't works (doesn' change the number
of row but use a range). Who can help me? Thanks
single cell it goes very well, but if I copy on a multiple cells I had the
problem that you described and the $ doesn't works (doesn' change the number
of row but use a range). Who can help me? Thanks
C
Chetski
The problem as I see it is not the conditional formatting, but Excel's
insistence on applying absolute references on the "Applies to" cells.
I selected a range of cells and applied a formula for conditional formatting
that works as expected on this selected range of cells. Yet when I try to
copy this rule to other rows, as previously noted, it just increases the
range of cells that gets affected by it. I want to apply this same rule as a
separate rule to a different row.
Is this possible? If so, please tell me how?
insistence on applying absolute references on the "Applies to" cells.
I selected a range of cells and applied a formula for conditional formatting
that works as expected on this selected range of cells. Yet when I try to
copy this rule to other rows, as previously noted, it just increases the
range of cells that gets affected by it. I want to apply this same rule as a
separate rule to a different row.
Is this possible? If so, please tell me how?
J
JCas
reylon said:Hi, I have the same problem. When you paste you cannot decide with $ if
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks
J
JCas
reylon said:Hi, I have the same problem. When you paste you cannot decide with $ if
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks
N
Nickelberry
I'm trying to apply a format as a table and it is not available why not and
how do I get it where I can apply it
how do I get it where I can apply it
M
MacHerb
The answer can be found here
http://www.free-training-tutorial.com/conditional-formatting.html
The key is when setting the conditional cell remove the the absolute row
reference. Example: I want A2:B12 to turn blue if C2:C12 is greater than 5.
I highlight A2:B12
Select Conditional Formatting>Manage Rules
Select new rule
Select Use formula to determine which cells to format
Place cursor in text field or click on cell selection box
Select first cell in condition column (C2)
Enter formula =$c$2>5
Then remove the $ in front of the 2 so that the formula reads: =$C2>5
Set the format to desired color (Blue)
Click Ok
Click Apply.
It worked for me in highlighting my grade book by letter grade. At a glance
i see the A's B's and C's.
Hope this helps.
http://www.free-training-tutorial.com/conditional-formatting.html
The key is when setting the conditional cell remove the the absolute row
reference. Example: I want A2:B12 to turn blue if C2:C12 is greater than 5.
I highlight A2:B12
Select Conditional Formatting>Manage Rules
Select new rule
Select Use formula to determine which cells to format
Place cursor in text field or click on cell selection box
Select first cell in condition column (C2)
Enter formula =$c$2>5
Then remove the $ in front of the 2 so that the formula reads: =$C2>5
Set the format to desired color (Blue)
Click Ok
Click Apply.
It worked for me in highlighting my grade book by letter grade. At a glance
i see the A's B's and C's.
Hope this helps.
T
Tom
I have an excel file with over 2000 lines. The data that was imported was
not consistent. One of the fields either had one or two data items, and as a
result, if the field had 2 data values, the 2nd data item was moved into the
A column of the row underneath it. These are all email address. Is there a
way I can create a rule and have them moved into the correct column (up one
row and into column C)?
This also meant the B column next to the email address now needs to be
deleted.
Can anyone tell me what I need to do? Thanks.
not consistent. One of the fields either had one or two data items, and as a
result, if the field had 2 data values, the 2nd data item was moved into the
A column of the row underneath it. These are all email address. Is there a
way I can create a rule and have them moved into the correct column (up one
row and into column C)?
This also meant the B column next to the email address now needs to be
deleted.
Can anyone tell me what I need to do? Thanks.
G
Gord Dibben
Any way to consistently tell which entry has two rows and which has one row?
Post a sample of some rows.
Gord Dibben MS Excel MVP
Post a sample of some rows.
Gord Dibben MS Excel MVP
Excel 2007 Quirks While Conditionally Formatting An Entire Column
When conditionally formatting an entire column, you create a rule that tests the value of a cell in some row. Then you establish the format to be applied to some cell (typically in the same row), if the rule evaluates to true.
Then you copy/paste or fill (most likely in the down direction) the formatted cell to cells in adjacent rows. For example, you have a three column table: the first column is the student name, the second column is the score they received on a test (from 0 to 100) and the third column is some formula (not important for this discussion) that converts the numerical score in the second column to a letter grade. You want the letter grade in the third column to be red if the numerical score in the second column is below 70. You select cell C1 (the first row that contains a letter grade) and add the following rule: B1 < 70. Simple. Now you select C1 (again) and drag it down to C30 (the last student in your class occupies row 30) and hit Control-D to drag the conditional formatting down into column C of rows 2 through 30. Note that you get the desired results.
Here's the odd thing. Select cell C15. Look at the rule that is in effect for the selected cell. Excel displays the rule as "B1 < 10" and you are expecting it to say "B15 < 10". When deciding what formatting to apply to C15, Excel is looking at B15, even though Excel (stupidly, in my opinion) displays B1 in the rule. Now, here's why it took me a while to figure this out. If there is a mistake in the rule, one that prevents the rule from being evaluated, then the conditional formatting is not applied, leading you (well, me anyway) to thinking that the rule was evaluated, only to false, further convincing me (incorrectly, as it turns out) that Excel was looking at B1 (after all, that is what Excel displays when presenting me with the Conditional Formatting dialog boxes).
So, in conclusion, Excel does look at the cell you would expect it to look at when evaluating a rule to be applied to a column, even though when displaying the rule, the wrong cell is identified.
When conditionally formatting an entire column, you create a rule that tests the value of a cell in some row. Then you establish the format to be applied to some cell (typically in the same row), if the rule evaluates to true.
Then you copy/paste or fill (most likely in the down direction) the formatted cell to cells in adjacent rows. For example, you have a three column table: the first column is the student name, the second column is the score they received on a test (from 0 to 100) and the third column is some formula (not important for this discussion) that converts the numerical score in the second column to a letter grade. You want the letter grade in the third column to be red if the numerical score in the second column is below 70. You select cell C1 (the first row that contains a letter grade) and add the following rule: B1 < 70. Simple. Now you select C1 (again) and drag it down to C30 (the last student in your class occupies row 30) and hit Control-D to drag the conditional formatting down into column C of rows 2 through 30. Note that you get the desired results.
Here's the odd thing. Select cell C15. Look at the rule that is in effect for the selected cell. Excel displays the rule as "B1 < 10" and you are expecting it to say "B15 < 10". When deciding what formatting to apply to C15, Excel is looking at B15, even though Excel (stupidly, in my opinion) displays B1 in the rule. Now, here's why it took me a while to figure this out. If there is a mistake in the rule, one that prevents the rule from being evaluated, then the conditional formatting is not applied, leading you (well, me anyway) to thinking that the rule was evaluated, only to false, further convincing me (incorrectly, as it turns out) that Excel was looking at B1 (after all, that is what Excel displays when presenting me with the Conditional Formatting dialog boxes).
So, in conclusion, Excel does look at the cell you would expect it to look at when evaluating a rule to be applied to a column, even though when displaying the rule, the wrong cell is identified.
Thanks, mbmast! You are absolutely right! I almost went crazy trying to solve this problem.
The rule appears to be wrong. But in fact it is applying the format as expected! How stupid is that!
You just need to make sure to delete the "$" when you first create the rule.
The rule appears to be wrong. But in fact it is applying the format as expected! How stupid is that!
You just need to make sure to delete the "$" when you first create the rule.
E
Exirtis
Thank you for this. I understood the conditional/absolute references, or
thought I did, but I didn't realize that the relative reference could be
divided up; meaning, I was putting something like =C2>5, instead of =$C2>5.
For those who don't understand the difference, as I didn't, the dollar sign
in front of the 'C' means that the column 'C' will always be the reference
(absolutely defined), while the row (the '2' without the dollar sign) will
change with each row being evaluated (relatively defined).
The tutorials linked above are also pretty decent, though I didn't sit all
the way through any of them.
Again, thanks for this clarification and example.
thought I did, but I didn't realize that the relative reference could be
divided up; meaning, I was putting something like =C2>5, instead of =$C2>5.
For those who don't understand the difference, as I didn't, the dollar sign
in front of the 'C' means that the column 'C' will always be the reference
(absolutely defined), while the row (the '2' without the dollar sign) will
change with each row being evaluated (relatively defined).
The tutorials linked above are also pretty decent, though I didn't sit all
the way through any of them.
Again, thanks for this clarification and example.
G
georgelf
The problem is that the row reference does not get updated in each cell. I'm
checing for the length fo the trimmed value in C2-C300, that it not be
greater than 40 (=LEN(TRIM($C2))>40. I did what the person 2 posts earlier
did, which is what I did in an earlier try. The row reference does not get
updated despite the absence of the $. HOWEVER, when I go to one of the
cells in the range( went tot he bottom) the formula works and any entry
longer than 40 is highlighted in red and Bold, as I wished. That damned
reference just doesn't get updated so at face value it looks like the formula
is dependent on what is in C2. This is a bug, I believe. I've been using
Excel, heavily, since the first version, and other SS before that. I hope
they fix it.
checing for the length fo the trimmed value in C2-C300, that it not be
greater than 40 (=LEN(TRIM($C2))>40. I did what the person 2 posts earlier
did, which is what I did in an earlier try. The row reference does not get
updated despite the absence of the $. HOWEVER, when I go to one of the
cells in the range( went tot he bottom) the formula works and any entry
longer than 40 is highlighted in red and Bold, as I wished. That damned
reference just doesn't get updated so at face value it looks like the formula
is dependent on what is in C2. This is a bug, I believe. I've been using
Excel, heavily, since the first version, and other SS before that. I hope
they fix it.