I. Overview
1) With conditional formatting, you can control font properties and realize effects such as highlighted text.
2) This article shows how to highlight data with low inventory and those with inventory quantity less than order quantity.
3) Prerequisites: Insert Data Columns, An Introduction of Conditional Formatting.
| You will |
|---|
|
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] into cells.
2) B2~E2: Enter headers 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: Font.
2) Select the applicable range: Current Cell.
3) Click Edit to set font properties:
Font type: Trajan Pro
Font style: Bold
Font size: 10
Underline: Click the drop-down box to select a line type
Foreground: Red
Special format: Shadow
5. Add common condition
1) Comparison Operator: Less Than.
2) Value Type: Integer.
3) Value: Current Value Less Than 10.
4) Click +Add to add the common condition.
6. Preview
When the number of inventory is less than 10, the corresponding figure is highlighted in a color.
7. Modify the applicable range to [Current Row] and add formula condition:
1) Select the type: Formula. Click Edit.
2) Define the formula as $$$ < E3. Click Ok. ($$$ represents the value of the current cell, and data column [Order_quantity] is inserted into E3.)
3) Select the logical operator: OR.
4) Click +Add to add the formula condition.
8. Preview
When the number of inventory is less than 10 or the order quantity, the whole row is highlighted in a color.
9. Delete the conditional formatting setting of D3 and add a new one for it:
1) Define D3 as Low Inventory.
2) Property setting:
Click +to add the property: Font.
Font type: Trajan Pro
Font style: Bold
Font size: 10
Underline: Underlined with a thin line
Foreground: Red
Special format: Shadow
Select the applicable range: Current Cell
3) Add common condition:
Operator: Less Than.
Value Type: Integer.
Value: Current Value Less Than 10.
Click +Add to add the common condition.
10. Add another conditional formatting of D3:
1) Define D3 as Inventory < Order.
2) Property setting:
Click + to add the property: Font.
Font type: Times New Roman
Font style: Bold
Font size: 12
Underline: None
Foreground: Azure
Special format: None
Select the applicable range: Current Row
3) Add formula condition:
Type: Formula. Click Edit.
Define the formula as $$$ < E3. Click OK.
Click +Add 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. Change the order
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].
