I. Overview
1) By controlling the cell background with conditional formatting, you can make a report with spaced background color, and also can realize the purpose of highlighting data.
2) Prerequisites: Insert Data Columns, An Introduction of Conditional Formatting, Data Settings, Cell Expansion, Cell Styles.
| You will learn |
|---|
|
II. Steps
1. Create a new dataset [ds1]
1) Create a new DB query dataset.
2) Drag the table [Product] into the DB query.
2. Drag data columns into cells
1) B3~E3: Drag data columns [Product_name], [SupplierID], [Inventory_quantity] and [Order_quantity].
2) Input the header of B2~E2.
3) Set B2~E3 as center alignment.
3. Data setting of B3: List
4. Setting of header background
1) Select B2~E2.
2) Click Cell Attributes->Style->Cell on the right of the setup panel.
3) In BG column, select [Fill: Color] and click More Color.
4) In [Select Color] setup panel, select [Custom], input 56aecc and click OK.
5. Set B2~E2 as Font Bold and the font color as White
6. Setting of border
1) Select B2~E3.
2) Click Cell Attributes->Style->Cell on the right of the setup panel.
3) In [Border] column, click the drop-down box of [Color] and select [More Color…].
4) Custom color: Input 56aecc and click OK.
5) Select all options in [OutBorder] and [InBorder].
7. Preview
A detailed list is displayed.
8. Select B2 and add condition
9. Setting of Property
1) Click+to select Property: Background.
2) Setting of background:
a) Click Edit and select More Color…
b) Custom color: Input e4eff2 and click OK.
c) Select the applicable range: Current Row.
10. Add formula condition
1) Select the type: Formula. Click Edit.
2) Input the formula: row()%2 = 0, and then click OK. ((row() is the current row number. If the current row number is even, the formula condition is TRUE.)
3) Click + to add conditional formatting.
11. Preview
Even-numbered rows have colored background while odd-numbered rows have no background, showing the effect of spaced background color.
12. Select D3 and add condition
13. Setting of property
1) Click + to select the property: Background.
2) Setting of the background:
Click [Edit] and select Light Yellow.
Select the applicable range: Current Cell.
14. Add common condition
1) Operator: Less Than.
2) Value Type: Integer.
3) Comparison Value: 10.
4) Click + to add this common condition.
15. Preview
1) Cells with the inventory value less than 10 are highlighted.
2) When the row number is even and the inventory value is less than 10, the cell background is light yellow, indicating that the condition property of the child cell is executed after the execution of the condition property of the parent cell.
