The condition in a conditional formatting rule is an If expression that must evaluate to True. For example:
If the order total is less than 1000
If the customer credit limit is between 100000 and 200000
If the sales office is Tokyo
If the order date is 7/21/2008
The Conditional Formatting dialog box helps you construct the If expression by breaking it down into its logical parts. In Figure 8‑1, the expression consists of three parts. In Figure 8‑3, the expression has four parts.
In Column Name, select a column. This column contains the value that determines when conditional formatting takes effect. The column you select here does not have to be the same as the column that you selected for formatting in the report. For example, if Product Name is the column selected for formatting, you can select Profit in this field to indicate that for a certain profit amount, conditional formatting applies to the product name.
Figure 8‑3 Selecting data fields between two values
In Condition, select the comparison test, or operator, to apply to the column you selected. You can select Equal to, Less than, Less Than or Equal to, and so on. If you select Is Null, Is Not Null, Is True, or Is False, the If expression does not require additional information.
If you select an operator that requires a comparison to one or more values, one or more additional fields appear. For example, if you select Less Than or Equal to, a third field appears. In this field, type the comparison value. If you select Between or Not Between, a third and fourth field appear. In these fields, type the lower and upper values, respectively, as shown in Figure 8‑3.
Comparing to a literal value
The conditional expression, shown in Figure 8‑3 in the previous section, evaluates the Credit Limit column and compares each value to determine if it matches a value between 100000 and 200000. The 100000 and 200000 values are literal values that you type.
Alternatively, you can select a value from the list of values in the Credit Limit column. Selecting from a list of values is useful if the comparison value is a customer name and you do not know the exact customer names, or if the comparison value is a date and you do not know the date format to type. If the comparison value is a date, Interactive Viewer also provides a calendar tool, which you can use to select a date.
How to select a comparison value from a list of values
1 On Conditional Formatting, choose Change Value, below the Condition field.
Select Values appears. Choose Specify literal value, then choose Select Values. The values in the selected column appear.
2 Select a value from the list, then choose OK. The value appears in the comparison value field on Conditional Formatting.
Comparing to a value in another column
In a conditional expression, you can compare the values of one column with the values of another column. For example, in a report that displays products, sales prices, and MSRP (manufacturer suggested retail price), you can create a conditional formatting rule that compares the sales price and MSRP of each product, and highlight the names of the products whose sales price is greater than the MSRP.
How to compare to a value in another column
1 On Conditional Formatting, choose Change Value, below the Condition field.
2 On Select values, select Use value from data field. A list of columns used in the report appears.
3 Select a column from the list, then choose OK. The column name appears in the comparison value field on Conditional Formatting.
Figure 8‑4 shows a condition that compares the sales price with the MSRP value. If the sales price value is greater or equal, the product name appears in bold.
Figure 8‑4 Comparing data to a value in another column
You can now define up to two additional rules for the report column on Conditional Formatting. Choose OK. Figure 8‑5 shows the conditional formatting applied to the report column.
