I. Overview
1) By controlling the text color through the conditional formatting, you can achieve the effect of text highlighting.
2) This section shows how to highlight low inventory data and data with inventory lower than ordered.
3) Prerequisites: Insert Data Columns, An Introduction of Conditional Formatting.
| 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 the data columns into cells
1) B3~E3: Drag data columns [Product_name], [SupplierID], [Inventory_quantity] and [Order_quantity] into cells.
2) B2~E2: Input the header and fill them with grey.
3) B2~E3: Set as center alignment and add borders.
4) Preview.
3. Select D3 and add condition
4. Property setting
1) Click + to select the property: Color.
2) Setting of color property:
Color: Red.
Select the applicable range: Current Cell.
5. Add common condition
1) Operator: Less Than.
2) Value Type: Integer.
3) Comparison Value: 10.
4) Click + to add the common condition.
6. Preview
The number of inventory less than 10 is shown in red.
7. Modify the applicable range to [Current Row], and add the formula condition
1) Type: Formula. Click Edit.
2) Define the formula as $$$ < E3. Click OK. （$$$ represents the value of current cell, and data column [Order_quantity] is inserted into E3）
3) Select the operator: OR.
4) Click+to add the formula condition.
8. Preview
When the number of inventory is less than 10 or lower than the order quantity, the whole row is shown in red.
9. Delete the conditional formatting of D3 and add a new conditional formatting for it
1) Rename the conditional formatting of D3 as Low Inventory
2) Property setting:
Click +to add the property: Color.
Select the color: Red.
Select the applicable range: Current Cell.
2) Add common condition:
Operator: Less Than.
Value Type: Integer.
Comparison Value: 10.
Click+to add the common condition.
10. Add another conditional formatting of D3:
1) Rename the conditional formatting of D3 as Inventory < Order.
2) Property setting:
Click [+] to add the property: Color.
Select the color: Azure.
Select the applicable range: Current Cell.
3) Add formula condition:
Select the type: Formula. Click Edit.
Define the formula as $$$ < E3. Click OK.
Click + to add the formula condition.
11. Preview
When the number of inventory is 3, the whole row is shown in azure, indicating that the conditional formatting Inventory < Order is executed following the execution of the conditional formatting [Low Inventory].
12. Move the conditional formatting [Inventory < Order] up above the conditional formatting [Low Inventory]
13. Preview
When the number of inventory is 3, the current cell is shown in red and other contents in the current row are shown in azure, indicating that the conditional formatting Inventory < Order is executed before the execution of conditional formatting [Low Inventory].
