IF Formula – Set Cell Color w/ Conditional Formatting – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells depending on the answer returned by an IF statement formula using Conditional Formatting in Excel and Google Sheets.

 

conditional formatting if statement master

 

Highlight Cells With Conditional Formatting

A cell can be formatted by conditional formatting based on the value returned by an IF statement on your Excel worksheet.

  1. First, create the IF statement in Column E.
    =IF(D4>C4,”Overdue”,”Ontime”)

 

conditional formatting if statement formula

 

  1. This formula can be copied down to Row 12.
  2. Now, create a custom formula within the Conditional Formatting rule to set the background color of all the “Overdue” cells to red.
  3. Select the range you want to apply formatting to.
  4. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

conditional formatting if statement menu

 

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

 

conditional formatting if statement formatting rule

 

  1. Click on the Format button and select your desired formatting.

 

conditional formating if statement set format

 

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

 

conditional formatting if statement rules manager

 

  1. Click Apply to apply the formatting to your selected range and then click Close.
    The formula entered will return TRUE when the cell contains the word “Overdue” and will therefore format the text in those cells with a background color of red.
  2. To format the “OnTime” cells to green, you can create another rule based on the same range of cells.

 

conditional formatting if statement rules manager second rule

 

  1. Click Apply to apply to the range.

 

conditional formatting if statement master final

 

Highlight Cells If… in Google Sheets

The process to highlight cells that contain an IF Statement 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 if statement menu

 

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

 

google sheets conditional formatting if statement range

 

  1. From the Format Rules section, select Custom Formula and type in the formula.

 

conditional formatting if statement custom 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 if statement mastser final