I. Intro

You can use conditional formatting in a sheet to highlight cells or ranges of cells. You can design custom highlighting styles to help users intuitively view and analyze data. For example, you can use conditional formatting to highlight abnormal values, display data in color scales, display percentages as data bars, and add icon sets.

II. Steps

Highlight duplicate values

1.Select a range of cells, click Conditional formatting on the toolbar, and select Highlight cell rules > Duplicate values.

2.Click Done in the lower-right corner of the panel on the right to save your settings.

Highlight cell rules

Common scenarios: Highlight abnormal values, duplicate values, and cells with keywords.

1.Create a conditional formatting rule: On the toolbar, click Conditional formatting > Highlight cell rules. In the secondary menu that appears, select a rule to open the settings panel.

2.Set application range: Click the 田 icon in the Apply to range input box and select a range of cells, or directly input a range (such as K2:K7).

3.Set conditions: Click the condition boxes under the Format rule list to set specific conditions. For example, you can set Value, > Is greater than, and 4 to highlight all cells with values greater than 4.

4.Set the style: In the Formatting style area at the bottom of the panel, set the highlight style, such as Bold, Italic, Underline, Strikethrough, Text color and Fill color.

5.Save settings: Click Done in the lower-right corner to save your settings.

Set formats for top, bottom, and average values

Common scenarios: Highlight the first, last, and average values in a data set.

1.Create a conditional formatting rule: Select a range of cells, click Conditional formatting on the toolbar, and select Top/bottom/average.

2.Select a rule in the secondary menu, such as Top (the maximum value), Bottom (the minimum value), Above average, or Below average to open the setting panel on the right.

3.Set application range: Click the 田 icon in the Apply to range input box and select a range of cells, or directly input a range (such as A1:F14).

4.Set conditions: In the Rules area, enter the data range to highlight. For example:

◦Top, 7: Displays the 7 largest values (and counts the size and number of values).

◦Top, 7 and check Percent: Displays the values in the top 7%.

5.Set the style: In the Formatting style area at the bottom of the panel, set the highlight style, such as Bold, Italic, Underline, Strikethrough, Text color and Fill color.

6.Save settings: Click Done in the lower-right corner to save your settings.

Set custom formulas

Common scenarios: Use formulas to set custom conditional formatting rules.

1.Create a conditional formatting rule: Select a range of cells, click Conditional formatting on the toolbar, and select Formula to open the settings panel.

2.Set application range: Click the 田 icon in the Apply to range input box and select a range of cells, or directly input a range (such as A1:F14).

3.Input a formula: Input a formula in the Rules area.

4.Set the style: In the Formatting style area at the bottom of the panel, set the highlight style, such as bold, italics, font, or background color.

5.Save settings: Click Done in the lower-right corner to save your settings.

Examples of common formulas

Quick interval row coloring

1.Enter the row coloring interval. For example, enter 1 to color every other row.

2.Set the conditional formatting range, enter a MOD function, and enter the corresponding cell information: =MOD(ROW(A1),2)=0 or =MOD(ROW(A1))=0.

3.Click Done to save your settings.

Highlight a row when a certain column meets set conditions

1.In the conditional formatting rules area, enter =$Top-left cell of selected area=Specified value (for example: =$A1=2). This will highlight all rows whose value in the selected column equals the specified value.

The figure below uses the formula =$A1=2. This means that, starting from cell A1, all rows with a column A value of 2 are highlighted.

2.Click Done to save your settings.

Strikethrough the entire row when a task is completed

1.In the conditional formatting rules area, enter =$Cell with the first checkbox in the selected range=1. This sets the rule for all rows in the selected range whose checkboxes are ticked (=1 indicates the checkbox is ticked, otherwise the value is =0).

2.In the format style area, select Strikethrough. Click Done to save your settings.

The figure below uses the formula =$E2=1. This means that, starting from cell E2, a strikethrough is placed on all rows that have ticks in their checkboxes in the E column.

Color scales

System default color scale: Select the cell range, and click Conditional formatting > Color scales in the toolbar. The system will automatically apply a color scale based on maxpoint, minpoint, and midpoint values.

•2-color scale: Maxpoint and minpoint values are assigned two different colors, and values between the max and min are colored along a scale that runs between the two colors.

•3-color scale: Maxpoint, minpoint, and midpoint values are assigned three different colors, and other values are colored along a scale based on these three colors.

Custom color scale rules:

1.Select a range of cells, click Conditional formatting on the toolbar, and select Color scales > More rules to open the settings panel.

2.Set application range: Click the 田 icon in the Apply to range input box and select a range of cells, or directly input a range.

3.Set the style: Select Custom and set the Minpoint, Midpoint and Maxpoint as desired. If there is no midpoint, select None from the drop-down menu.

4.Save settings: Click Done in the lower-right corner to save your settings.

Data bar

System default data bar: Select the cell range, and click Conditional formatting > Data bar in the toolbar. After you select a data bar style, the system will automatically apply a data bar based on the maxpoint and minpoint.

•If the selected range has only positive values: The data bar will start at the left of each cell and extend to a length corresponding to the cell value in relation to the maxpoint and minpoint. If the value is 0, no bar is displayed.

•If the selected range includes negative values: The data bar will start in the center of each cell and extend to the right for positive values and to the left for negative values. If the value is 0, no bar is displayed.

Custom data bar rules:

1.Select a range of cells, click Conditional formatting on the toolbar, and select Data bar > More rules to open the settings panel.

2.Set application range: Click the 田 icon in the Apply to range input box and select a range of cells, or directly input a range.

3.Set the style:

•Select Solid or Gradient for the data bar fill style and set the colors for Negative value and Positive value.

•Set the Minpoint and Maxpoint as desired. If you select Automatic from the drop-down menu, the system will automatically apply colors.

4. Save settings: Click Done in the lower-right corner to save your settings.

Maxpoint and Minpoint: You can enter a number, percent, or percentile value. If there are no negative values, cells with values less than or equal to the minpoint will not display bars, and those with values greater than or equal to the maxpoint will have full bars. If there are negative values, values less than the minpoint will have bars that go to the left.

Icon sets

System default icon set: Select the cell range, and click Conditional formatting > Icon sets in the toolbar. After you select a icon set style, the system will automatically apply icons based on the proportional relations of values.

Custom icon set rules:

1.Create a rule: Select a range of cells, click Conditional formatting on the toolbar, and select Icon sets > More rules to open the settings panel.

2.Set the style: Select an icon style from the drop-down list. You can set different icons for different value ranges.

3.Set rules: In the Rules drop-down list, select Greater than or equal to or Greater than. In the Value column, enter the conditional value. In the Type drop-down list, select Number, Percent, or Percentile.

4.Save settings: Click Done in the lower-right corner to save your settings.

Create more conditional formatting rules

On the toolbar, click Conditional formatting > New rule, or go to Menu > Format > Conditional formatting to open the settings panel on the right.

View existing conditional formatting rules

•On the toolbar, click Conditional formatting > Manage rules to show all conditional formatting rules for the current sheet in a list on the right.

•View rules for entire sheet: At the top of the panel, select Manage rules for entire sheet.

•Manage rules for select cells: At the top of the panel, select Manage rules for current range.

Edit conditional formatting rules

On the toolbar, click Conditional formatting > Manage rules and select the rule to edit from the list on the right.

Set rule display priority

On the toolbar, click Conditional formatting > Manage rules. In the list on the right, click the ⋮⋮ icon to drag and drop rules in order of priority.

Delete conditional formatting rules

•Delete an existing conditional formatting rule: On the toolbar, click Conditional formatting > Manage rules. Find the rule to delete in the list on the right, mouse over the rule, and click the trash can icon that appears.

•Delete conditional formatting rules for selected cells: On the toolbar, click Conditional formatting > Clear rules in cell selection.

•Delete all conditional formatting rules for current sheet: On the toolbar, click Conditional formatting > Clear rules in entire sheet.

III. FAQs

Can I set multiple conditional formatting rules for a single area?

Yes. When multiple conditional formatting rules are set for a single cell, the most recently added rule will be given priority.

How many conditional formatting rules can be applied to a single sheet?

80.

Can I use the conditional formatting function on Sheets in the mobile app?

Currently, the mobile app only allows you to view conditional formatting effects. If conditional formatting rules have been applied to a sheet, you will see the effects in the mobile app.