To highlight a row may be tricky because there is no built-in option to 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 row numbers
- 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 contains references from the first row and returns TRUE when the row needs to be highlighted (i.e. =$E3=”GA”). Make sure that column reference is absolute while row reference is relative.
- Click 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 the column reference absolute and leave the row reference relative (i.e. $E3). This way, Excel updates only the row number in the range while column references remains the same.
Another important point is the base formula that will be copied automatically by Excel, should reference the first row of the data range. For example; we used $E3 because the data range starts from the 3rd row.
Note that this trick in not only applicable to highlight a row but it can be used to apply any formatting options supported by conditional formatting.