I. Intro 

You can use conditional formatting to highlight cells or cell ranges and customize the highlight styles to visualize and analyze data. For example, you can use conditional formatting to highlight outliers, display data in color scales, display percentages as data bars, and add icon sets.

Through conditional formatting in Feishu Sheets, you can not only directly use rules such as highlighting outliers, displaying Color scales, showing percent as Data bars, and adding icon sets, but also customize rules for Highlight cell rules or cell ranges to visually view and analyze data. 

Note: The mobile app can view the effects of conditional formatting but cannot edit conditional formatting.

II. Steps 

Add new rules

Open the spreadsheet. In the toolbar, click Conditional Formatting > New rule. Alternatively, click Menu > Format > Conditional formatting. 

You can also click Menu > Format and select a conditional formatting option instead of creating your own rules. Available conditional formatting options include highlighting cells that meet specified conditions, top, bottom, or average values, or custom formula results; displaying color scales and data bars; or adding icon sets.

1.1 Highlight cells 

Open the spreadsheet. In the toolbar, click Conditional Formatting > Highlight Cell Rules. You can highlight outliers, duplicate values, cells containing a specific keyword, and more. 

Note: If the Conditional Formatting button is not on your toolbar, click More from the toolbar to find it.

Make the following settings, then click Done in the lower-right corner to save the settings. 

Apply to Range: Click the Select a range icon, then select a range of cells in the spreadsheet or directly enter a range (for example: F1:F7).

Format Rule: Set the conditions to specify the data you want to highlight. For example, you can select Value and > Is greater than, and enter 4 to highlight all cells with values greater than 4.

Formatting style: Set the highlight style, such as bold, italic, text color, and fill color.

1.2 Set formats for top, bottom, and average values 

Open the spreadsheet. In the toolbar, click Conditional Formatting > Top/Bottom/Average to highlight the top, bottom, or average values in a data set. 

Make the following settings, then click Done in the lower-right corner to save the settings. 

Apply to Range: Click the Select a range icon, then select a range of cells in the sheet or directly enter a range (for example: A2:F15).

Rules: Configure the rules to specify the data you want to highlight. For example, select Top and 7 to highlight the 7 largest values.

Formatting style: Set the highlight style, such as bold, italic, text color, and fill color.

1.3 Format cells with custom formulas 

Open the spreadsheet. In the toolbar, click Conditional Formatting > Formula to customize conditional formatting rules. 

Make the following settings, then click Done in the lower-right corner to save the settings. 

Apply to Range: Click the Select a range icon, then select a range of cells in the sheet or directly enter a range (for example: D2:D7).

Rules: Enter a formula. Refer to the table below for a list of commonly used formulas.

Formatting style: Set the highlight style, such as bold, italic, text color, and fill color.

Scenario
Example formula
Screenshot
Apply color to alternate rows.
Enter the formula MOD(ROW(A1),2)=0 or MOD(ROW(A1))=0 to highlight even rows.
Highlight a row when a column meets condition(s). 
Enter the formula $A1=2 to highlight rows where the value in column A is 2 (starting from cell A1).
Strikethrough a row when a task is completed.
Enter the formula $F2=1 to apply a strikethrough to all rows that have checkboxes selected in column F (starting from cell F2). 
Note: 1 indicates that the checkbox is selected, and 0 indicates that the checkbox isn't selected.

For more information about customizing conditional formatting, see Use cases for custom formulas for conditional formatting.

1.4 Format cells with color scales 

Open the spreadsheet. In the toolbar, click Conditional Formatting > Color scales to use a default color scale rule:

2-color scale: Cells with the Maxpoint and Minpoint values are filled with two different colors in the darkest shade, and values in between are displayed in gradient tones.

3-color scale: Cells with the Maxpoint, Minpoint, and Midpoint values are filled with three different colors, and cells with other values in gradient tones.

You can also click More Rules to make the following settings. Once you're done, click Done in the lower-right corner to save the settings. 

Apply to Range: Click the Select a range icon, then select a range of cells in the sheet or directly enter a range (for example: A1:F14).

Settings: Configure the Minpoint, Midpoint, and Maxpoint and select the data type (Example: percent or number) and color.

Note: When the data type is set to percent or percentile, the final range values are calculated based on the source data. If you want to directly display the percent value differences of the source data, you can choose the "Number" type. For more information about percent and percentile, see Use percent and percentile for conditional formatting.

1.5 Format cells with data bars 

Open the spreadsheet. In the toolbar, click Conditional Formatting > Data bar and select a data bar style (Solid fill or Gradient fill). Data bars will be added based on the maxpoint and minpoint values. 

You can also click More Rules to make the following settings. Once you're done, click Done in the lower-right corner to save the settings. 

Apply to Range: Click the Select a range icon, then select a range of cells in the sheet or directly enter a range (for example: A1:F14).

Settings: Select Solid or Gradient for Fill type and set the colors for positive and negative values. Set the Minpoint and Maxpoint as required. You can select different data types (example: percentage or number). For example, if you want to set the data bar according to the percent value, you can select the "Number" type. At the same time, set the Minpoint to 0 and the Maxpoint to 1.

Note: When the data type is set to percent or percentile, the final range values are calculated based on the source data. If you want to directly display the percent value differences of the source data, you can choose the "Number" type. For more information about percent and percentile, see Use percent and percentile for conditional formatting.

If the selected range contains numbers, percentages, or percentiles and there are no negative 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. 

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.

1.6 Format cells with icon sets 

Open the spreadsheet. In the toolbar, click Conditional Formatting > Icon sets and select a default icon set style. Icons in the cell are filled proportionally based on cell values. 

You can also click More Rules to make the following settings. Once you're done, click Done in the lower-right corner to save the settings. 

Apply to Range: Click the Select a range icon, then select a range of cells in the sheet or directly enter a range (for example: A1:F14).

Settings: Select the desired icon style. You can set the value ranges for different icons and select different data types (for example: percentage or number).

Note: When the data type is set to percent or percentile, the final range values are calculated based on the source data. If you want to directly display the percent value differences of the source data, you can choose the "Number" type. For more information about percent and percentile, see Use percent and percentile for conditional formatting.

View and edit conditional formatting rules

Open the spreadsheet. In the toolbar, click Conditional Formatting > Manage rules to view all conditional formatting rules applied to the spreadsheet in the pane on the right. From here, click a rule to make changes. 

Manage rules for the entire spreadsheet: Select entire sheet from the Manage rules for drop-down list.

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

Change rule display priority

Open the spreadsheet. In the toolbar, click Conditional Formatting and select Manage rules. In the Conditional formatting pane on the right, hover over the ⋮⋮ icon next to the rule and drag it to rearrange its priority. Rules placed higher up the list have a higher priority and will take precedence over lower priority rules. 

Delete conditional formatting rules

You can delete a conditional formatting rule in two ways:

Method 1: In the toolbar, click Conditional Formatting and select Manage rules. In the Conditional formatting pane on the right, hover over the rule and click the Delete icon.

Method 2: In the toolbar, click Conditional Formatting and select Clear rules in cell selection or Clear rules in entire sheet.

III. FAQs 