How to apply conditional formatting to a whole column

1) Don't apply CF to an entire column unless you're sure you're going to have a million rows of data. Apply the CF to a reasonable range for your needs, typically a few 100 rows, perhaps a few 1000.

2) Highlight the full range of cells to apply to, perhaps C1:C300

3) Enter the CF formula as if it were for the first row, the rest should update themselves if you leave the $ symbols off of the row reference.

=$A1>$B1

That's how it works. Try it on a range of 10 cells or so them upload it so we can see what actually ended up in your sheet.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

It is generally more efficient to apply CF to an entire column than to specific ranges, especially if those ranges are large.

Originally Posted by romperstomper

It is generally more efficient to apply CF to an entire column than to specific ranges, especially if those ranges are large.

This is a debate worth having. Suffice to say I've seen application of Conditional Formatting and cell formatting applied to whole empty columns result in workbooks with massive file size and little real data.

Thus, I rarely counsel whole column application of formatting. This doesn't seem to happen if all you apply is number formatting, but beyond that...

It's usually due to how and when the formatting was applied. Generally it is best to apply formatting to the largest contiguous area in one go, rather than adding it piecemeal, and to use as few different formats as possible (that's also a clarity issue as much as a workbook efficiency one).