Conditional Formatting

Conditional Formatting

Conditional formatting is a rule-based method to apply formatting to individual cells based on their values. It helps you to spot trends and patterns without having to closely examine each row or cell. You can use conditional formatting to change fonts and backgrounds, or apply visual cues such as icon sets and data bars.

To show the Conditional Formatting menu:

  • In a Pivot Report, right-click a cell in the Data Area and choose Conditional Formatting. The formatting is applied to the values of the corresponding data-area field.
  • In a Table Report, right-click a column header and choose Conditional Formatting.

The exact set of conditional formatting choices you have depends on:

  • the type of report (Pivot or Table); and
  • the data you’re formatting. Numbers and dates have more choices available than strings (text).

Adding Conditional Formatting to Reports

Right-click the appropriate report element as described above, and choose Conditional Formatting.

Choose the type of formatting you wish to apply. (As noted above, the set of choices you see depend on both the report-type and the data-type being formatted.)

Note: To delete formatting or change existing formatting, use Clear Rules or Manage Rules (more on these below).

In the following example, the settings for the Highlight Cells Rules / Greater Than conditional formatting option are shown.

Click OK to apply the defined formatting to the report.

Conditional Formatting Menu Items

The conditional formatting menu consists of the following items:

Highlight Cells Rules

The Highlight Cells Rules submenu allows applying the following conditional formatting rules:

  1. Greater Than, Less Than, Between, Equal To, Text That Contains formats cells comparing their values with static values.
  2. Duplicate Values formats cells whose values appear more than once. You can also use this item to format cells whose values are unique instead of duplicated.

    This item is available for Table reports only.

  3. A Date Occurring highlights date-time values that fall into a specified interval.
  4. Formatting Values Using Custom Conditions lets you create conditions as simple or as complicated as needed. The Custom Condition Editor works like the Filter Expression Editor. See the Filter Editor documentation for details.

Top/Bottom Rules

The Top/Bottom Rules submenu identifies the topmost/bottommost values. You can vary the cutoff, as shown below. You can also identify cells that are above or below the field’s average.

Data Bars

Data Bars formatting adds a color bar to each cell, where the length of the bar is proportional to the cell’s value (relative to the other cells being formatted).

Color Scales

Color Scales highlights cells using predefined sets of colors. Mercury automatically applies one of the four colors in your chosen set based on the range of values in the data being formatted.

Icon Sets

Icon Sets inserts an icon into each cell.

Editing Existing Conditional Formatting

  1. In a Table report: Right-click the heading of the column containing cells that uses the formatting you want to adjust.

    In a Pivot report: Right-click a cell in the data-area that contains the formatting you want to adjust.

  2. Choose Conditional Formatting, and then click Manage Rules.

  3. The Conditional Formatting Rules Manager form appears.

    There are small differences in the form, depending on whether you are in a Table Report or a Pivot Report.

  4. Select the rule you want to adjust, and edit it as desired (see below for details).

  5. Click OK.

For more information on other options available from the Conditional Formatting Rules Manager dialog box, including adding additional rules and ordering rules, see right below.

Conditional Formatting Rules Manager

The Conditional Formatting Rules Manager allows directly editing existing conditional formatting rules, creating new rules, deleting existing rules, and specifying the order (hierarchy) of rules.

The Rules Manager is slightly different for Table vs Pivot Reports.

Table Report

The conditional formatting rules manager provides the following actions:

Element

Action

New Rule...Opens the New Formatting Rule dialog for creating a new rule.
Edit Rule ...Opens the Edit Formatting Rule dialog for editing the selected rule. (You can also double-click a rule to open this dialog.)
Delete RuleDeletes the selected rule.
Up / DownReorders format rules in the rule list. This allows specifying their priority from high to low.
Apply to the rowSpecifies whether to apply a rule to the entire row.
ColumnSpecifies the field to which the rule is applied.
Is EnabledEnables / disables a rule.
Show formatting rules forAllows filtering rules by a specified grid column.

Pivot Report

Notice that the grid columns are slightly different than for a Table Report.

Element

Action

New Rule...

Opens the New Formatting Rule dialog for creating a new rule.

Edit Rule ...

Opens the Edit Formatting Rule dialog for editing the selected rule. (You can also double-click a rule to open this dialog.)

Delete Rule

Deletes the selected rule.

Up / Down

Reorders format rules in the rule list. This allows specifying their priority from high to low.

Row, Column

Specifies the Row-area and Column-area fields to which the rule will apply.

Measure

The data-area field (the field being summarized in the pivot report) to which the rule will apply.

Is Enabled

Enables / disables a rule.

When creating or editing a rule, choose from among the available rule types and specify the parameters for the rule.

Some of the rules are similar to those described above (icon bars, color scales, etc) but in some cases the Rules Manager may offer additional options and give you more control.

Clearing Conditional Formatting

If you have conditional formatting rules in effect, the Clear Rules submenu becomes available.

For a Table-report, the menu is similar to the following:

Select one of the following options:

  • Clear Rules from All Columns - Clears all existing conditional formatting.
  • Clear Rules from This Column - Only clears the right-clicked column of conditional formatting.

For a Pivot report, the menu is similar to the following:


A "Measure" is a field being summarized.

Select one of the following options:

  • Clear Rules from All Measures - Clears all existing conditional formatting
  • Clear Rules from This Intersection - Clears formatting for the cell’s row-area and column-area categories.
  • Clear Rules from This Measure - Clears all conditional formatting for the right-clicked data-area field.

    • Related Articles

    • Conditional Formatting

      Conditional formatting is a rule-based method to apply formatting to individual cells based on their values. It helps you to spot trends and patterns without having to closely examine each row or cell. You can use conditional formatting to change ...
    • Right-Click Menus

      Right-click on a column header Sort Ascending - Sorts the column data in ascending order. See Searching and Sorting. Sort Descending - Sorts the column data in descending order. See Searching and Sorting. Clear Sorting - Removes sorting from ...
    • Mercury Overview

      What is Mercury? Mercury is a general-purpose reporting and analysis tool that works with any SQL Server database(s). Reporting and analysis are no longer separate; you work directly with your report results to customize the layout and perform ...
    • Right-Click Menus

      Right-click a filter-area icon Hide this Field - Removes the field. (To get it back, see Show Field List below.) Order - Moves the field to a different location in the same area: Move to Beginning - Moves the field to the beginning of the list. Move ...
    • Favorites

      Using a Favorite A favorite lets you save (and share) your work. When you run a report, you typically provide report parameters. When the report appears on screen, you might change the layout, add calculated fields, filter the data, and perform other ...