Use conditional formatting rules in Google Sheets

Cells, rows, or columns tin be formatted to change text or background color if they meet certain conditions. For example, if they incorporate a certain word or a number.

Conditional formatting gif

  1. On your estimator, open a spreadsheet in Google Sheets.
  2. Select the cells yous want to utilize format rules to.
  3. Click Format and then Conditional formatting. A toolbar will open to the right.
  4. Create a rule.
    • Single color: Under "Format cells if," choose the condition that you lot want to trigger the rule. Under "Formatting manner, choose what the cell will look like when weather condition are met.
    • Color scale: Under "Preview," select the color calibration. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow Down Arrow.
  5. Click Done.

Use advanced conditional formatting

Use custom formulas with conditional formatting

You tin use custom formulas to apply formatting to one or more than cells based on the contents of other cells.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells you want to format.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down card, click Custom formula is. If at that place's already a rule, click it orAdd new ruleand thenCustom formula is.
  5. Click Value or formula and add the formula and rules.
  6. Click Washed.

Notation: Formulas can just reference the same sheet, using standard notation "(='sheetname'!prison cell)." To reference some other sheet in the formula, use the INDIRECT function.

Instance 1

To highlight when there'south more one occurrence of the aforementioned value in your information:

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the range you want to format. For example, cells A1 to A100.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drop-down bill of fare, click Custom formula is. If there's already a dominion, click it orAdd new dominionand thenCustom formula is.
  5. Write the rule for the first row. In this case the rule would be, "=COUNTIF($A$i:$A$100,A1)>1."
  6. Cull other formatting properties.
  7. Click Washed.

Case 2

To format an unabridged row based on the value of 1 of the cells in that row:

  1. On your reckoner, open a spreadsheet in Google Sheets.
  2. Select the range you desire to format, for example, columns A:E.
  3. Click Format and then Conditional formatting.
  4. Under the "Format cells if" drib-downward menu, click Custom formula is. If there's already a rule, click information technology orAdd together new ruleand thenCustom formula is.
  5. Write the rule for the first row. For case, if you desire to make the whole row green if the value in cavalcade B is "Yeah", write a formula like "=$B1="Yes"."
  6. Choose other formatting properties.
  7. Click Done.

Absolute vs. relative references

Often, you volition need to add dollar signs ($) in front of letters and numbers in formulas and then that the formatting is practical using absolute references every bit opposed to relative references (A1 to B1, A2 to B2).

Use wildcard characters with provisional formatting

You can utilize wildcard characters to match multiple expressions. Wildcard characters can exist used with the "Text contains" or "Text does not incorporate" fields while formatting.

  • To friction 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 friction match zero (0) or more characters, utilize an asterisk (*) . For example, a text rule containing "a*c" would format cells with "abc," "air conditioning," 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 case, a text dominion containing "a~?c" would format cells with "a?c" simply non "abc" or "a~?c."

Notes:

  • To remove a rule, point to the rule and click RemoveRemove.
  • Rules are evaluated in the order listed. The get-go dominion establish to be true volition define the format of the jail cell or range. To reorder rules, click and elevate them.
  • If you copy and paste from a cell or range that has formatting rules, these rules volition exist practical when you paste the copied data.

Was this helpful?

How tin we ameliorate it?