Use conditional formatting rules in Google Sheets

Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number. 
 
 On your computer, open a spreadsheet in  Google Sheets . 
 Select the cells you want to apply format rules to. 
 Click  Format     Conditional formatting . A toolbar will open to the right. 
 Create a rule.
 
 Single color : Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met. 
 Color scale : Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow  . 
 
 
 Click  Done . 
 
 
 
 Example 
 
 
 
 
 
 A teacher can highlight test scores to see which students scored less than 80%. 
 
 
 
 
 On your computer, open a spreadsheet in  Google Sheets . 
 Select the test scores. 
 Click  Format     Conditional formatting . 
 Under "Format cells if," click  Less than . If there's already a rule, click it or  Add new rule     Less than . 
 Click  Value or formula  and enter 0.8. 
 To choose a red color, click Fill  . 
 Click  Done . The low scores will be highlighted in red. 
 
 
 
 
 Use advanced conditional formatting 
 
 
 Use custom formulas with conditional formatting 
 
 
 
 
 
 You can use custom formulas to apply formatting to one or more cells based on the contents of other cells. 
 
 
 
 
 
 On your computer, open a spreadsheet in  Google Sheets . 
 Select the cells you want to format. 
 Click  Format     Conditional formatting . 
 Under the "Format cells if" drop-down menu, click  Custom formula is . If there's already a rule, click it or  Add new rule     Custom formula is. 
 Click  Value or formula  and add the formula and rules. 
 Click  Done . 
 
 
 
 
 
 Note : Formulas can only reference the same sheet, using standard notation "(='sheetname'!cell)." To reference another sheet in the formula, use the  INDIRECT  function. 
 Example 1 
 To highlight when there's more than one occurrence of the same value in your data: 
 
 
 
 
 
 On your computer, open a spreadsheet in  Google Sheets . 
 Select the range you want to format. For example, cells A1 to A100. 
 Click  Format     Conditional formatting . 
 Under the "Format cells if" drop-down menu, click  Custom formula is . If there's already a rule, click it or  Add new rule     Custom formula is. 
 Write the rule for the first row. In this case the rule would be, "=COUNTIF($A$1:$A$100,A1)>1." 
 Choose other formatting properties. 
 Click  Done . 
 
 
 
 
 
 Example 2 
 To format an entire row based on the value of one of the cells in that row: 
 
 
 
 
 
 On your computer, open a spreadsheet in  Google Sheets . 
 Select the range you want to format, for example, columns A:E. 
 Click  Format     Conditional formatting . 
 Under the "Format cells if" drop-down menu, click  Custom formula is . If there's already a rule, click it or  Add new rule     Custom formula is. 
 Write the rule for the first row. For example, if you want to make the whole row green if the value in column B is "Yes", write a formula like "=$B1="Yes"." 
 Choose other formatting properties. 
 Click  Done . 
 
 
 
 
 
 Absolute vs. relative references 
 Often, you will need to add dollar signs ($) in front of letters and numbers in formulas so that the formatting is applied using absolute references as opposed to relative references (A1 to B1, A2 to B2). 
 
 
 Use wildcard characters with conditional formatting 
 
 
 
 
 
 You can use wildcard characters to match multiple expressions. Wildcard characters can be used with the "Text contains" or "Text does not contain" fields while formatting. 
 
 
 
 
 To match any single character, use a question mark (?). For example, a text rule containing "a?c" would format cells with "abc," but not "ac" or "abbc." 
 To match zero (0) or more characters, use an asterisk (*) . For example, a text rule containing "a*c" would format cells with "abc," "ac," and "abbc" but not "ab" or "ca." 
 To match a question mark or asterisk in text, you can escape the wildcard characters by adding a tilde (~) in front of them. For example, a text rule containing "a~?c" would format cells with "a?c" but not "abc" or "a~?c." 
 
 
 
 
 Notes : 
 
 To remove a rule, point to the rule and click Remove  . 
 Rules are evaluated in the order listed. The first rule found to be true will define the format of the cell or range. To reorder rules, click and drag them. 
 If you copy and paste from a cell or range that has formatting rules, these rules will be applied when you paste the copied data.