An Introduction of Conditional Formatting

  • Last update:  2020-12-01
  • I. Overview

    1) Conditional formatting allows you to control the display attributes of cells using cell values and user-specified conditions. The display attributes that can be controlled including: 

    • Cell styles: font, foreground, background, indentation, border, row height and column width;

    • Display effect: display and new value (setted by data dictionary, formula display, barcode and currency line);

    • The settings and usability of pagination, report widgets and hyperlinks.

    2) Mechanism: Users create a conditional formatting for a cell, and the creation require users to specify a condition and the display attributes when the condition is met. If the cell value make the condition true, its display effect is controlled by the user-specified display attributes.

    3) By default, conditional formatting only controls the display attributes of current cell, yet some attributes including font, foreground, background and indentation can take effect along the whole row or column. 

    4) Prerequisite:Insert Formulas and Set Filters.

    You will learn
    • View and create a conditional formatting

    • Set a conditional formatting

      • Add and remove a display property

      • Add a common condition and formula condition

      • Modify, arrange and remove a condition

      • Add and remove parentheses

    • Copy, sort and remove a conditional formatting

    II. Steps

    1. View a conditional formatting in two ways

    1) Method 1: Select B2. Right click and select [Conditional Formatting].

    2) Method 2: Select B2. Click Conditional Formatting on the right of the panel.

    2. Add a new conditional formatting

    1) Click +. The default name of the new conditional formatting is [Conditional Formatting 1].

    2) At the same time, a setup panel pops up.

    3.  A small magenta triangle appears at the upper left corner of B2

    It indicates that a conditional formatting has been added.

    4. Double click the conditional formatting to rename it

    Press Enter to complete the modification.

    5. Select and edit conditional formatting

    Select a conditional formatting and click the  button to edit it.

    6. Select a display property

    1) Click + and add the following display properties respectively: Background, Display and Row Height.

    2) The added display properties are displayed below in sequence and can be edited. For the editing method, see other sections in this chapter.

    7. Click the cross to remove a display property

    8. Add a common condition

    1) Select the Type: Common.

    2) Select the Operator: Greater Than.

    3) Select the type of the comparison value: Integer.

    4) Input the comparison value: 10.

    5) Click + to add the common condition.

    9. Set a formula condition

    1) Select the Type: Formula. Click Edit.

    2) Define the formula as $$$ < 20. Click OK. ($$$ represents the value of the current cell)

    3) Select the logical operator: AND.

    4) Click + to add the formula condition.

    10. Modify a condition

    1) Select the common condition currentValue Greater Than 10.

    2) Modify the comparison value as 15.

    3) Click Modify.

    11. Adjust the location of a selected condition

    Click the a.png or b.png arrow to adjust the location of a selected condition.

    12. Add or remove parentheses. The precondition is that there are at least three conditions.

    1) Add another formula condition:

    a) Formula: $$$%2  = 0. (True if the value of the current cell is even)

    b) Logical operator: OR. Click +.

    2) Hold down the Shift key to select adjacent conditions.

    3) Click Add Parentheses to form a condition group.

    4) Click the triangle preceding the condition group to view sub-conditions in the group.

    5) Click Remove Parentheses to remove the condition group.

    13. Remove the selected condition

    Click Remove to remove the selected condition

    14. Copy a conditional formatting in two ways

    1) Select the conditional formatting [test] and click the Copy button.

    2) Select the conditional formatting [test], right click it and select [Copy].

    3) The conditional formatting [CopyOftest] is obtained. The settings are the same as [test].

    15. Arrange a conditional formatting in two ways

    1) Method 1: Select a conditional formatting and click the up arrow, down arrow or sort button in the toolbar.

    2) Method 2: Right click a conditional formatting and select Move Up, Move Down or Sort.

    16. Remove a conditional formatting in two ways

    1) Method 1: Select a conditional formatting and click the cross in the toolbar.

    2) Method 2: Select a conditional formatting, right click it and select [Remove].

    Attachment List

    Theme: Designer and Upgrade
    • Helpful
    • Not helpful
    • Only read

    Doc Feedback