Highlight Rows If (Conditional Formatting) – Excel & Google Sheets

This tutorial will demonstrate how to highlight rows if a condition in a cell is met using Conditional Formatting in Excel and Google Sheets.

 

highlight rows if master

 

Highlight Rows With Conditional Formatting

IF Function

To highlight a row depending on the value contained in a cell in the row with conditional formatting, you can use the IF Function within a Conditional Formatting rule.

  1. Select the range you want to apply formatting to.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

highlight rows if menu

 

  1. Select Use a formula to determine which cells to format, and enter the following formula (with the AND Function):

 

highlight rows if formula

 

  1. You need to use a mixed reference in this formula ($C3, $D3, $E3, $F3) in order to lock the column but make the row relative – this will enable the formatting to format the entire row instead of just a single cell that meets the criteria.
  2. When the rule is evaluated, each column is evaluated by a nested IF statement – and if all the IF statements are true, then a TRUE is returned, and the entire row is highlighted. As you are applying the formula to a range of columns and rows, the row changes relatively, but the column will always remain the same.
  3. Click on the Format button and select your desired formatting.

 

conditional formatting greater less than format

 

  1. Click OK, and then OK once again to return to the Conditional Formatting Rules Manager.

 

highlight rows if rule manager

 

  1. Click Apply to apply the formatting to your selected range and then click Close.

Every row in the range selected that has a cell with a value greater than 5 will have its background color changed to yellow.

 

highlight rows if rule final

 

ISERROR Function

To highlight a row if there is a cell with an error in it in the row with conditional formatting, you can use the ISERROR Function within a Conditional Formatting rule.

 

highlight rows if rule iserror

 

  1. Select the range you want to apply formatting to.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

hightlight rows if iserror menu

 

  1. Select Use a formula to determine which cells to format, and enter the following formula:

 

highlight rows if iserror formula

 

  1. You need to use a mixed reference to make sure that the column is locked and that the row is relative – this will enable the formatting to format the entire row instead of just a single cell that meets the criteria.
  2. When the rule is evaluated for all the cells in the range, the row will change but the column will remain the same. This causes the rule to ignore the values in any of the other columns and just concentrate on the values in Column D. As long as the rows match, and Column D of that row returns an error, then the formula result is TRUE and the formatting is applied for the whole row.
  3. Click on the Format button and select your desired formatting.

 

conditional formatting greater less than format

 

  1. Click OK, and then OK once again to return to the Conditional Formatting Rules Manager.

 

highlight rows if iserror rule manager

 

  1. Click Apply to apply the formatting to your selected range and then click Close.

 

highlight rows if rule is error final

 

Every row in the range selected that has a cell with an error in it has its background color changed to yellow.

Evaluate for Negative Numbers

To highlight a row if there is a cell with a negative number in it in the row with conditional formatting, you can use the OR Function within a Conditional Formatting rule.

 

highlight rows if or master

 

  1. Select the range you want to apply formatting to.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

highlight rows if or menu

 

  1. Select Use a formula to determine which cells to format, and enter the formula:

 

highlight rows if or formula

 

  1. You need to use a mixed reference to make sure that the column is locked and that the row is relative – this will enable the formatting to format the entire row instead of just a single cell that meets the criteria.
  2. When the rule is evaluated, each column is evaluated by the OR statement – and if all the OR statements are true, then a TRUE is returned. If a TRUE is returned, then the entire formula will return true and the entire row is highlighted. As you are applying the formula to a range of columns and rows, as the row changes, the row in the will change relatively, but the column will always remain the same.
  3. Click on the Format button and select your desired formatting.

 

conditional formatting greater less than format

 

  1. Click OK, and then OK once again to return to the Conditional Formatting Rules Manager.

 

highlight rows if or rule manager

 

  1. Click Apply to apply the formatting to your selected range and then click Close.

 

highlight rows if or final

 

Every row in the range selected that has a cell with a negative number will have its background color changed to yellow.

Conditional Format If in Google Sheets

The process to highlight rows based on the value contained in that cell in Google Sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.

 

google sheets conditional formatting menu

 

  1. The Apply to Range section will already be filled in.

 

google sheets not equal to range

 

  1. From the Format Rules section, select Custom Formula.
  2. Type in the following formula.

 

google sheets conditional formatting formula

 

  1. Select the fill style for the cells that meet the criteria.

 

google sheets conditional formatting format

 

  1. Click Done to apply the rule.

See also: IF Formula – Set Cell Color w/ Conditional Formatting.

If There Is an Error

The process to highlight rows where an error is contained in a cell in the row in Google Sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format, Conditional Formatting.

 

google sheets conditional formatting menu

 

  1. The Apply to Range section will already be filled in.

 

google sheets not equal to range

 

  1. From the Format Rules section, select Custom Formula.
  2. Type in the following formula:

 

google sheets conditional formatting is error formula

 

  1. Select the fill style for the cells that meet the criteria.

 

google sheets conditional formatting format

 

  1. Click Done to apply the rule.

 

google sheets conditional formatting iserror final

 

Evaluate for Negative Numbers

The process to highlight rows if there is a cell with a negative number in it in the row with conditional formatting sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.
  2. The Apply to Range section will already be filled in.
  3. From the Format Rules section, select Custom Formula.
  4. Type the following formula.

 

google sheets conditional formatting or formula

 

  1. Select the fill style for the cells that meet the criteria.
  2. Click Done to apply the rule.

 

google sheets conditional formatting negative number final