To highlight a column may be tricky because there is no built-in option in Excel’s Conditional Formatting feature. Here, you can find out how you can do this dynamically using Excel formulas.
- Begin by selecting rows by clicking on column letters
- Open Conditional Formatting window by going to HOME > Conditional Formatting > Add New Rule
- Select Use a formula to determine which cells to format
- Enter the formula that returns TRUE when the column needs to be highlighted (i.e. =D$8>=70). Make sure that row reference is absolute while column reference is relative.
- Click the Format button to edit formatting settings.
- Click OK to continue and apply your settings
The conditional formatting feature applies selected formatting options to a cell, when a given condition is met. If this condition is provided by a formula, Excel will check whether the formula returns TRUE before applying the formatting options. Therefore, we need a formula that will return TRUE when the condition is met within the selected range.
Fortunately, we do not need to add conditional formatting to each cell one-by-one. Excel can handle this by looking at absolute and relative references. All you need to do is to make row reference absolute and leave column reference relative (i.e. D$8). This way, Excel updates only the column number in the range while other cells remain the same.
Another important point is the base formula that will be copied automatically by Excel, should reference the first column of the data range. For example; we used D$8 because the data range starts from column D.
Note that this approach is not limited to highlight a column. Any formatting option available in Conditional Formatting can be applied to the selected column.