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:

Pivot Report

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

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.