Format reports 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 employee names and the number of hours that each employee worked, you can highlight in blue any employee name who worked under 40 hours and highlight in red and bold any employee name who worked more than 40 hours.

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.

To set conditional formatting:

  1. To define the condition, select and right-click the column on which to display conditional formatting.
  2. Select Format > Conditional Formatting.
  3. In the Conditional Formatting box, create a rule specifying the following information, then click tap OK:
    • The format to apply, such as bold style.
    • The condition that must be true to apply the format, such as Early Out exceptions less than or equal to 2.

The condition in a conditional formatting rule is an "If" expression that must evaluate to "true." For example:

If the total hours are more than 40

If the location is Tokyo

If the order date is 7/21/2017

The Conditional Formatting dialog box helps you construct the "If" expression by breaking it down into its logical parts:

  • 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 Employee Name is the column selected for formatting, you can select Hours Worked in this field to indicate that for a certain amount, conditional formatting applies to the employee name.
  • Format — Font, color, and so forth
  • 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, enter the comparison value. If you select Between or Not Between, a third and fourth field appear. In these fields, enter the lower and upper values, respectively.
  • Value — The value to apply to the condition

The conditional expression evaluates the value in one column, for example, Hours Worked, and compares each value to determine if it matches a value between 30 and 40. The 30 and 40 values are literal values that you enter.

Alternatively, you can select a value from the list of values in the Hours Worked column. Selecting from a list of values is useful if the comparison value is an employee name and you do not know the exact employee 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, you can use a calendar tool to select a date.

To select a comparison value from a list of values:

  1. On Conditional Formatting, select Change Value to the right of the Value field.
  2. In the Select Values listing, select Specify literal value> Select Values. The values in the selected column appear.
  3. Select a value from the list, then click tap OK. The value appears in the comparison value field in the Conditional Formatting glance (also known as a contextual callout) Provides information and actions in a dialog box for an item on the screen when the user right-clicks or taps the item..

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 employee name, scheduled hours, and worked hours, you can create a conditional formatting rule that compares the worked hours and scheduled hours for each employee, and highlight the names of the employees whose worked hours are greater than their scheduled hours.

To compare to a value in another column:

  1. In the Conditional Formatting glance, select Change Value, to the right of the Value field.
  2. In the Select Values listing, select Use value from data field. A list of columns used in the report appears.
  3. Select a column from the list, then click tap OK. The column name appears in the comparison value field in the Conditional Formatting glance.

You can create up to three conditional formatting rules for a single column. You can, for example, create three rules to set the values of an hours worked column to one of three colors, depending on its value.

For each row of data in the report, the rules are evaluated in the order in which they appear in the list of rules. As each rule is evaluated, the specified format properties are applied if the condition is met.

When creating multiple rules for a column, be careful that the conditions do not cover overlapping values. Consider the following scenario:

  • The first rule sets the hours worked value to blue if the value exceeds 40.
  • The second rule sets the hours worked value to pink if the value exceeds 30.

If the hours worked value is 50, the value appears in pink, not blue as you expect, because the condition in both rules is true (50 exceeds 30 and 40), and the second rule supersedes the first rule. For the rules to make sense, the order of the rules should be switched.