I. Intro

You can use conditional formatting to highlight cells or ranges of cells in sheets. In addition, you can customize the highlight styles to 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 and go to Conditional formatting > Highlight cell rules > Duplicate value.

2. Click Done in the bottom right corner of the setting panel on the right to save the settings.

Highlight cells

Scenarios: Highlight cells with abnormal values, duplicate values, and keywords.

1. Create a conditional formatting rule: Go to Conditional formatting > Highlight cell rules and select a rule from the level-2 menu to open the setting panel on the right.

2. Set an application scope: Click the Select a range icon in the Apply to range input box and select a range of cells in the sheet or directly enter a range, for example, F1:F7.

3. Set a condition: Set the parameters in the Format rule list box. For example, you can select Value and > Is greater than, and enter 4 to highlight all cells with values greater than 4.

4. Set a formatting style: In the Formatting style section, set the highlight style, such as bold, italic, text color, and fill color, that you want to apply to the values that meet the condition.

5. Save the settings: Click Done in the bottom right corner of the setting panel on the right.

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

5. Set a formatting style: In the Formatting style section, set the highlight style, such as bold, italic, text color, and fill color, that you want to apply to the values that meet the condition.

6. Save the settings: Click Done in the bottom right corner of the setting panel on the right.

Format cells with formulas

Scenarios: Use formulas to customize conditional formatting rules.

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

2. Set an application scope: Click the Select a range icon in the Apply to range input box and select a range of cells in the sheet or directly enter a range, for example, D2:D7.

3. Enter a formula: In the Rules section, enter a formula.

4. Set a formatting style: In the Formatting style section, set the highlight style, such as bold, italic, text color, and fill color, that you want to apply to the values that meet the condition.

5. Save the settings: Click Done in the bottom right corner of the setting panel on the right.

Examples of common formulas

Quickly apply color to alternate rows

1. Determine the row coloring interval. For example, the row coloring interval is 1 if you want to color every other row.

2. Set an application scope, enter a MOD function, and enter the cell information, for example, =MOD(ROW(A1),2)=0 or =MOD(ROW(A1))=0.

3. Click Done to save the settings.

Highlight a row when a column meets set conditions

1. In the Rules section of the setting panel on the right, enter =$Top left cell of the selected range=Specified value, for example, =$A1=2.

In this case, starting from cell A1, if the value of a cell in column A is 2, the whole row is highlighted, as shown in the following figure.

2. Click Done to save the settings.

Strikethrough a row when a task is completed

1. In the Rules section of the setting panel on the right, enter =$Cell with the first checkbox in the selected range=1. In this case, the rule is applied to rows in the selected range with the checkboxes ticked. (1 indicates that the checkbox is ticked, and 0 indicates that the checkbox isn't ticked.)

2. In the Formatting style section, click the Strikethrough icon and then click Done.

The formula =$F2=1 indicates that starting from cell F2, a strikethrough is applied to all rows that have ticked checkboxes in column F.

Format cells with color scales

Use a default color scale rule: Select a range of cells, go to Conditional formatting > Color scales, and select a color scale style. The system automatically applies colors based on the Maxpoint, Minpoint, and Midpoint values.

•2-color scale: Cells with the Maxpoint and Minpoint values are filled with colors at both ends of the color scale, and cells with in-between values are colored proportionally.

•3-color scale: Cells with the Maxpoint, Minpoint, and Midpoint values are filled with three different colors, and cells with other values are colored proportionally.

Customize a color scale rule:

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

2. Set an application scope: Click the Select a range icon in the Apply to range input box and select a range of cells in the sheet or directly enter a range.

3. Set a formatting style: Select Custom from the Settings drop-down list and specify the colors for the Minpoint, Midpoint, and Maxpoint values as required. If no value is available, select None from the drop-down list.

4. Save the settings: Click Done in the bottom right corner of the setting panel on the right.

Format cells with data bars

Use a default data bar rule: Select a range of cells, go to Conditional formatting > Data bar, and select a data bar style. The system automatically applies data bars based on the Maxpoint and Minpoint values.

•If the selected range contains only positive values, a data bar starts from the left of each cell and extends to a length corresponding to the cell value. If the value is 0, no bar is displayed.

•If the selected range contains negative values, a data bar starts from the center of each cell and extends to the left for negative values and the right for positive values. If the value is 0, no bar is displayed.

Customize a data bar rule:

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

2. Set an application scope: Click the Select a range icon in the Apply to range input box and select a range of cells in the sheet or directly enter a range.

3. Set a formatting style:

•Select Solid or Gradient for Fill type, and set the colors for positive and negative values.

•Set Minpoint and Maxpoint as required. If you select Automatic from the Minpoint and Maxpoint drop-down lists, the system automatically applies colors.

4. Save the settings: Click Done in the bottom right corner of the setting panel on the right.

Maxpoint and Minpoint: You can select Number, Percent, or Percentile and enter a value. If the selected range contains only positive values, no bar is displayed in cells with values less than or equal to the Minpoint value, and full bars are displayed in cells with values greater than or equal to the Maxpoint value. If the selected range contains negative values, cells with values less than or equal to the Minpoint value will have bars that go to the left.

Format cells with icon sets

Use a default icon set rule: Select a range of cells, go to Conditional formatting > Icon sets, and select an icon set style. The system automatically adds icons to cells proportionally based on the cell values.

Customize an icon set rule:

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

2. Set a formatting style: Select an icon style from the Settings drop-down list and set the value ranges for different icons.

3. Set rules: Select ≥ or > from the Rules drop-down list, enter a conditional value in the Value input box, and select Number, Percent, or Percentile from the Type drop-down list.

4. Save the settings: Click Done in the bottom right corner of the setting panel on the right.

Create more conditional formatting rules

Click Conditional formatting in the toolbar and select New rule. Alternatively, click Menu and select Format > Conditional formatting to open the setting panel on the right.

View existing conditional formatting rules

•Click Conditional formatting in the toolbar and select Manage rules to view all conditional formatting rules applied to the sheet in the Conditional formatting panel on the right.

•View rules of the entire sheet: Select entire sheet from the Manage rules for drop-down list.

•View rules of the selected cell range: Select current range from the Manage rules for drop-down list.

Edit conditional formatting rules

Click Conditional formatting in the toolbar and select Manage rules. In the Conditional formatting panel on the right, click a rule to edit.

Set the rule display priority

Click Conditional formatting in the toolbar and select Manage rules. In the Conditional formatting panel on the right, click the ⋮⋮ icon before a rule and drag the rule to the top of the list.

Delete conditional formatting rules

• Delete an existing conditional formatting rule: Click Conditional formatting in the toolbar and select Manage rules. In the Conditional formatting panel on the right, hover over the rule to delete and click the Delete icon.

• Delete conditional formatting rules set for selected cells: Click Conditional formatting in the toolbar and select Clear rules in cell selection.

• Delete conditional formatting rules set for the sheet: Click Conditional formatting in the toolbar and select Clear rules in entire sheet.

III. FAQs

Can I apply multiple conditional formatting rules to a cell range?

Yes, you can.

When multiple conditional formatting rules are applied to a cell range, the latest added rule is applied first.

For example, you set a rule to highlight cells in column A with values greater than 1 in red. Then, you set another rule to highlight cells with values greater than 1 in yellow. In this case, cells in column A with values greater than 1 are highlighted in yellow.

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

A maximum of 80 conditional formatting rules can be applied to a sheet.

Can I use conditional formatting in sheets on Feishu mobile?

No, you can't. Currently, you can only view the effect of conditional formatting on Feishu mobile.