# 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.

1. On your computer, open a spreadsheet in [Google Sheets](https://docs.google.com/spreadsheets/u/0/).
2. Select the cells you want to apply format rules to.
3. Click **Format** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "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 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 ![Down Arrow](https://storage.googleapis.com/support-kms-prod/76B4941B08D1516AC7336D5A3C2E7914920D "Down Arrow").
5. Click **Done**.

<div id="bkmrk-example"><div><div><a aria-expanded="true" class="zippy index1 goog-zippy-expanded" data-stats-idx="1,3" data-stats-ignore="" data-stats-imp="" data-stats-ve="2" role="button" tabindex="0">Example</a></div><div><div></div></div></div></div>A teacher can highlight test scores to see which students scored less than 80%.

<div id="bkmrk-on-your-computer%2C-op-0"><div><div>1. On your computer, open a spreadsheet in [Google Sheets](https://docs.google.com/spreadsheets/u/0/).
2. Select the test scores.
3. Click **Format** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Conditional formatting**.
4. Under "Format cells if," click **Less than**. If there's already a rule, click it or **Add new rule** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Less than**.
5. Click **Value or formula** and enter 0.8.
6. To choose a red color, click Fill ![Color fill](https://lh3.googleusercontent.com/q_VFSqjbh-0IbvSAObg1EAOj7joV92KCmhCbaSjIZKMaF4y-Sd1f2VVkv0bwMLOAPAQ=w18 "Color fill").
7. Click **Done**. The low scores will be highlighted in red.

</div></div></div>## Use advanced conditional formatting

<div id="bkmrk-use-custom-formulas-"><div><div><a aria-expanded="true" class="zippy index2 goog-zippy-expanded" data-stats-idx="2,3" data-stats-ignore="" data-stats-imp="" data-stats-ve="2" role="button" tabindex="0">Use custom formulas with conditional formatting</a></div><div><div></div></div></div></div>You can use custom formulas to apply formatting to one or more cells based on the contents of other cells.

<div id="bkmrk-on-your-computer%2C-op-1"><div><div><div>1. On your computer, open a spreadsheet in [Google Sheets](https://docs.google.com/spreadsheets/u/0/).
2. Select the cells you want to format.
3. Click **Format** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Conditional formatting**.
4. Under the "Format cells if" drop-down menu, click **Custom formula is**. If there's already a rule, click it or **Add new rule** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Custom formula is.**
5. Click **Value or formula** and add the formula and rules.
6. Click **Done**.

</div></div></div></div>**Note**: Formulas can only reference the same sheet, using standard notation "(='sheetname'!cell)." To reference another sheet in the formula, use the [INDIRECT](https://support.google.com/docs/answer/3093377) function.

### Example 1

To highlight when there's more than one occurrence of the same value in your data:

<div id="bkmrk-on-your-computer%2C-op-2"><div><div><div>1. On your computer, open a spreadsheet in [Google Sheets](https://docs.google.com/spreadsheets/u/0/).
2. Select the range you want to format. For example, cells A1 to A100.
3. Click **Format** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Conditional formatting**.
4. Under the "Format cells if" drop-down menu, click **Custom formula is**. If there's already a rule, click it or **Add new rule** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Custom formula is.**
5. Write the rule for the first row. In this case the rule would be, "=COUNTIF($A$1:$A$100,A1)&gt;1."
6. Choose other formatting properties.
7. Click **Done**.

</div></div></div></div>### Example 2

To format an entire row based on the value of one of the cells in that row:

<div id="bkmrk-on-your-computer%2C-op-3"><div><div><div>1. On your computer, open a spreadsheet in [Google Sheets](https://docs.google.com/spreadsheets/u/0/).
2. Select the range you want to format, for example, columns A:E.
3. Click **Format** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Conditional formatting**.
4. Under the "Format cells if" drop-down menu, click **Custom formula is**. If there's already a rule, click it or **Add new rule** ![and then](https://lh3.googleusercontent.com/3_l97rr0GvhSP2XV5OoCkV2ZDTIisAOczrSdzNCBxhIKWrjXjHucxNwocghoUa39gw=w18-h18 "and then") **Custom formula is.**
5. 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"."
6. Choose other formatting properties.
7. Click **Done**.

</div></div></div></div>### 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).

<div id="bkmrk-use-wildcard-charact"><div><div><a aria-expanded="true" class="zippy index3 goog-zippy-expanded" data-stats-idx="3,3" data-stats-ignore="" data-stats-imp="" data-stats-ve="2" role="button" tabindex="0">Use wildcard characters with conditional formatting</a></div><div><div></div></div></div></div>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.

<div id="bkmrk-to-match-any-single-"><div><div>- 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."

</div></div></div>**Notes**:

- To remove a rule, point to the rule and click Remove ![Remove](https://lh3.googleusercontent.com/BZ4bD0q7UU_F0Ljtv_oc3ZHPaPbdFA7Z-6jpNHxajcYmrOj1Jy1izEajlia5i6rJKnCv=w18-h18 "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.