I only want one column in Excel table to have conditional formatting - not all columns

[Hard Head]

When I apply conditional formatting to one column in an Excel table all the columns end up with the conditional format - I only want the one column to be formated. The steps that I took to try to format the one column follows:

1. Highlighted the column (but not the column heading) that I wanted to apply conditional formatting in the table.
2. Went to conditional formatting, selected "New Rule ...", selected "Format only cells that contain"
3. In the Edit the Rule Description section of the dialog box I entered the following:
a. 1st drop down box select "Cell Value"
b. 2nd drop down box select "Equal To"
c. 3rd box I type in the number "2"
4. Defined the formatting for the cell

All columns end up formatted. Any ideas on only formatting the column I selected?

Thanks

[L-Drr]

Please post your file (without confidential data), so we can have a look.

[Hard Head]

I have attached a simplified spreadsheet where I highlighted column 2 in the table and applied conditional format. As you can see, the other columns end up with the conditional formating also (although the "applies to" text box shows the correct column that I want formated). My actual table has many columns with most columns being conditionally formatted differently than the others. The result is that when I go back to "Manage rules ..." for any column, rather than have 1 rule in the list I have many (~30). The "applies to" box is always correct for each of the rules therefore the actual formats appear to be applied correctly but why does a conditional format applied to column 2 show up in all the columns? This behavior does not occur if the data is not an Excel table but just a range of numbers.

[FDibbins]

Hi and wselcome to the forum

Im not sure what you did, but it may be best tio just start all over.

1. highlight the entire for that table.
2. on the home tab, styles, select CF
3. delete ALL rules you find there.

Now, start again...
1. highlight the range you want to apply the conditional formatting to - just that range of cells, and try to avoid using entire columns, CF can become very resource-heavy
2. on the home tab, styles, select CF
3. select new rule, select format only cells that contain
4. follow the process you did before

[jeffreybrown]

Hi Hard Head,

This behavior does not occur if the data is not an Excel table but just a range of numbers

I found this to be the same when I converted a range to a table and then back to a range.

Didn't find anything on the web to explain this behavior, but my guess, the properties of the table show all conditional formatting for the table not matter what cell you are in the table.

As long as the reference is pointed to the right range (G9:G13 in this case) then all seems fine and only the number 2 in that range is highlighted if entered.

[newdoverman]

The file that you posted works perfectly here. There must be a corruption of some kind in the file you are having trouble with.

[jeffreybrown]

There must be a corruption of some kind in the file you are having trouble with.

I don't think there is any corruption in the OPs workbook. As I stated above, when I tried this on a workbook of my own, the same behavior exists.

I created a range of data from E4:H13 >> highlighted F5:F13 and in CF >> =$F5=2

CF applied fine, but if I place my cursor on H5 the same condition shows up but I clearly applied it to F5:F13 not to H5:H13.

As soon as I convert the table range (E4:H13) back to a range and select H5, no condition exists s you would expect it not to be there.

[newdoverman]

That is totally bizarre. I can't make it misbehave. I even did as you did...no problem. I entered 2's all over the place and the only ones highlighted were in the proper column only. I used the formula method as well as the Cell Value is and neither one caused a problem.

I am using Office 2010 X64 SP2 if that matters.

[Hard Head]

Wow - thanks for the quick responses. To clarify, the conditional formating does appear to work correctly (I originally did not think it did when I first discovered 30 rules rather than 1 rule for each table column). The number 2 that I have in col1 in my spreadsheet does not get formatted so it is behaving correctly. As JeffreyBrown stated in his first post above, it appears that this is just the way Excel does conditional formatting in tables - puts all conditional formats in ALL cells - even the ones that don't apply to those cells. It just didn't seem correct to me and in my case gets a little unwieldy when there are so many rules listed.

At this point I'm convinced it is working correctly (as Jeff stated, the references are pointed to the right ranges).

Thanks for everyone's input!

Rick

[jeffreybrown]

Hi Rick,

You're welcome…glad you have a workable solution and thanks for the feedback...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.