Conditional Formatting

Use Conditional Formatting to associate specific item values with format styles. Conditional formatting is an effective way to call out important elements in a report. If an item value matches a conditional expression, the format is applied. For example, you could use this feature to have item values appear blue when a value is greater than 10,000, or red when it is less than 5,000. When a value does not match a condition, the default format of the value is applied.

In the example below, the format bold and red have been applied to amounts greater than 10,000, and the format bold and green have been applied to amounts less than 1,000:

Image shows the Conditional Formatting dialog box.
Image shows the effects of applying conditional formatting in the Results section.

Based on the section, Conditional Formatting behaves as follows:

SectionConditional Formatting Behavior
Results/TablesConditional Formatting has the option to format both data cells and total cells.
ReportWhen a Report table is selected, the Item drop-down includes table columns. Due to the number of possible Report section fields, if a Report Field is chosen, the Item drop-down has a single “current item” label.
PivotThe Item drop-down includes all row and column labels, and Fact items.
CubeQueryThe Item drop-down has a Data Cell option and it can be applied to data facts only.

  To apply a conditional format:

  1. Select an item to which to apply a conditional format.

    Conditional formatting is applied to the entire column, label or row which meets the condition, not just the selected cell.

  2. Select Format, then Conditional Formatting.

    The Conditional Formatting dialog box is displayed.

  3. If desired, you can select another item to which apply the conditional formatting from the Item drop-down.

    Otherwise, the item selected in step 1 is shown.

    Note:

    In the Pivot section, ”item” refers to labels and rows. In the Table sections, item refers to a column.

  4. In the Operator drop-down box, select the comparative operator to use for the conditional expression.

    See below for a description of valid operators.

    If you select the Between operator, the Conditional Formatting dialog includes two Value fields. Select the beginning and ending values in these fields.

  5. In the Value field, enter the comparative value to use in the conditional expression.

  6. Select the format to apply to item values. Valid options are:

    • bold

    • italic

    • underline

    • fill color

    • text color

  7. Click Apply to apply the formats to the conditional expression.

    You can view conditional formats by selecting Sample to the right of the conditional expression. To view other conditional expressions formats, select on the Sample bar associated with the expression.

  8. If desired, add more conditional expressions.

    When the number of expressions totals three, the Add Expression hyperlink appears. Selecting the hyperlink adds another line conditional format controls. When the number of lines becomes more than three, a scroll bar is displayed.

  9. Click OK.

  To copy a conditional format:

  1. Select the column, label or row that has the conditional formatting you want to copy.

  2. Select Edit, then Copy Conditional Format.

  To paste a conditional format.

  1. Select the column, label or row to which to paste a conditional formatting.

  2. Select Edit, then Paste Conditional Format.

  To delete a conditional format, select the (blank) operator from the Operator field next to the format to be deleted, and click Apply.

OperatorDescription
(blank)Empty operator (used to mark an expression for deletion)
= EqualItem value is exactly as in the expression.
<> Not EqualItem value is not equal to any value in the expression.
< Less ThanItem value is less than that one in the expression.
> Greater Than Item value is greater than that one in the expression.
>= Greater or Equal expressionItem value is not less than that one in the expression.
<= Less or Equal Item value is not greater than that one in the expression.
Begins With Value in the expression is a symbolic prefix of item value.
ContainsValue in the expression is a symbolic infix of item value.
Ends WithValue in the expression is a symbolic suffix of item value.
LikeItem value matches wildcard string in expression ( “%” is used instead of asterisk in wildcards).
BetweenThe first expression value is less or equal to the first item value, and the second expression value is equal or greater than the second item value. To accommodate the Between parameter, the Conditional Formatting dialog includes two Value fields. Use these fields to specify the beginning and ending values.
IsNullItem value is null.