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:
- Greater Than, Less Than, Between, Equal To, Text That Contains formats cells comparing their values with static values.
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.
- A Date Occurring highlights date-time values that fall into a specified interval.
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.
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 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 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 inserts an icon into each cell.
Editing Existing Conditional Formatting
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.
Choose Conditional Formatting, and then click Manage Rules.
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.
Select the rule you want to adjust, and edit it as desired (see below for details).
- 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.
The conditional formatting rules manager provides the following actions:
|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.|
|Apply to the row||Specifies whether to apply a rule to the entire row.|
|Column||Specifies the field to which the rule is applied.|
|Is Enabled||Enables / disables a rule.|
|Show formatting rules for||Allows filtering rules by a specified grid column.|
Notice that the grid columns are slightly different than for a Table Report.
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.)
Deletes the selected rule.
Up / Down
Reorders format rules in the rule list. This allows specifying their priority from high to low.
Specifies the Row-area and Column-area fields to which the rule will apply.
The data-area field (the field being summarized in the pivot report) to which the rule will apply.
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.