Conditional Formatting-Excel
 

I'm trying to (for FOF ratings) copy conditional formatting to multiple columns. Problem is Excel keeps wanting to to evaluate the numbers against the original column and not each column individually. Example: Instead of evaluating # of QB Formations column (column M) independently, it evaluates that column against the original column, so therefore it shades the whole column red thinking those numbers are in the bottom percentile.

I can copy the conditional formatting ... one by one ... but I would be doing that forever. How can I just apply them to all the columns???

Not an expert here but could you use the fill handle and then choose Fill Formatting Only from the Fill Options button?

I can't see the image (at work).

Is the original formatting based on a formula for the column? If so, check your cell reference to be certain it's not an absolute reference.

Yep...this is what I suspect is happening. Take the $ out of the column reference. That is what makes something an absolute reference.

If a formula was not used, just highlight all of the columns you want to apply cond formatting to, and create a new rulw for formatting which includes them. Just make sure to delete the old formatting rule so that you dont have unnecessary junk rules being evaluated (even if its the same...eats more memory).

None of the columns are formulas, just data. I'm not sure about how to create a new rule, I think that's where I'm stuck. I'm not too familiar with conditional formatting ... so I could be doing it wrong.

Fill Handle didn't work either. Still compares the Formations column against the rest of the columns.

just guessing without seeing the actual spreadsheet, but can't you use copy -> paste special -> formats ?

Nope, still won't work correctly. Still the only way I've gotten it to work is copying one column at a time.

I can attach the file if it helps anyone.

Attach it. I'm not great at it but will give it a shot.

Thanks!

1. Highlight the column that's already conditionally formatted (column K)
2. Double click the Format Painter in your menu bar
3. Click each of the remaining column headers one-by-one (so click on the "L" above column L)
4. When you're done, click the Format Painter again (this turns the format painting off)

Each time you click one of those column headers, you're "painting" the formatting from column K into that column.

Somebody please feel free to check my work on this or offer an alternative. I'm mainly guessing.

Didn't work either.
I'm totally baffled.

Column M is the only one you were having a problem with, correct?

Let me know if this works for you. I applied the formatting to everything and just created a new rule for Column M, using the low mid, high points actually contained within that data set.

Yes, but I'm guessing this problem will invalidate the rest of the conditioning throughout the file. Meaning that most of the FOF ratings are 0-100, but it's going to compare every column against each other and the highlighting is going to be slightly incorrect.

Column M stands out the most because it's a 6-18 scale.

Here's the example.
Column AF is what happens if you select all the columns and conditional format.
Column AG (same data) if you just select that column individually.

It's slightly off since it's comparing column AF to the rest of the data.

I haven't looked at Logan's file, so maybe this has been solved, but here's my attempt using the method I suggested. I zipped it because the board won't let me attach an .xlsx file.

Oh shit, you know what I was doing? I was holding down CTRL while using Format Painter ... thinking repeated pasting needed it pressed down.

I think your way works. GD, I need to RTFM.

Thanks Pumpy!

No sweat. That'll be $25 and your booty.

I mean soul.

Edit: No, I think I mean booty.

Oh thank God, this has been driving me nuts for days. I knew I had to be doing something wrong.

Wait, is that "thank God it's my booty" or "thank God it's $25"?

"thank God it's my booty", of course.