Formatting report data based on conditions
When you format data in a selected column, the format applies to all the values. Often, it is useful to change the format of data when a certain condition is true. For example, you can display numbers in red if the value is a negative number and in black if the value is a positive number. Conditional formatting is the formatting of data according to defined conditions.
You can also change the format of data in a column according to the values in another column. For example, in a report showing customer names and the number of days each customer’s invoice is past due, you can highlight in blue any customer name that has an invoice past‑due value between 60 and 90 days. Then, you can highlight in red and bold any customer name that has an invoice past‑due value greater than 90 days.
As another example, in a sales inventory report, when you apply conditional formatting to the Product column, you can define the condition based on the Quantity in Stock column, such that conditional formatting is applied to the Product column if the quantity in stock is below a specified value. After you create the condition, you define the format in which to display data that satisfies the condition. The formatting appears on the selected column and not on the column on which you based the condition.
To apply conditional formatting, you create a rule defining when and how to change the appearance of data. You can apply conditional formats only to data in columns. The rule consists of the condition that must be true, and the text attributes to apply to column entries that satisfy the condition. You can define up to three conditions or rules for a single column and remove or modify conditional formatting for a column.
How to set conditional formats
1 To define the condition, select and right-click the column on which to display conditional formatting. Choose FormatConditional Formatting. Conditional Formatting appears, as shown in Figure 8‑1. The example shown in Figure 8‑1 highlights all customers with a credit limit of less than $100,000.
Figure 8‑1 Defining conditional formatting
The report in Figure 8‑2 displays conditional formatting for the data fields in the Credit Limit column that satisfy the defined condition.
Figure 8‑2 Customers with credit limits below $100,000
2 In Conditional Formatting, create a rule specifying the following information, then choose OK:
*The format to apply, such as bold style. Choose Format to select formatting options.
*The condition that must be true to apply the format, such as Credit Limit Less Than or Equal to 100000.