Applying Conditional Formatting to Tables, Pivot Tables, and Trellises

In tables, pivot tables, trellises, and graphs, conditional formatting helps direct attention to a data element if it meets a certain condition. For example, you can show below-quota sales figures in a certain color, or display an image such as a trophy next to the name of each salesperson who exceeds quota by a certain percent.

This section describes how to apply conditional formatting in tables, pivot tables, and trellises.

How is Conditional Formatting Applied?

You apply conditional formatting by selecting one or more columns or hierarchy levels in the analysis to use, specifying the condition to meet, and then making specifications for font, cell, border, and style sheet options to apply when the condition is met. The conditional formats can include colors, fonts, images, and so on, for the data and for the cell that contains the data. Your specifications apply only to the contents of the columns or hierarchy levels in the tables and pivot tables for the analysis with which you are working.

You can add multiple conditions so that the data is displayed in one of several formats, based upon the value of the data. For example, below-quota sales can be displayed in one color, and above-quota sales can be displayed in another color. When you specify multiple conditions, all the conditions are verified and the formats are merged for the conditions that are true. In the event of a conflict when trying to merge multiple formats, the condition that is last verified as true affects the format that is displayed.

Can Conditional Formats Be Based on Another Column?

You can create conditional formats to apply to one column based on the values of a second column, for display in tables, pivot tables, and trellises. For example, you can create a conditional format to color the Region column green when values of the Sales column are greater than $30 million.

You can create a condition using a column that is not displayed in views for the analysis, if you use the Hide option on the Column Format tab of the Column Properties dialog. If you place the column in the Excluded drop target of the Layout pane, then you cannot create a condition using that column.

Conditional formatting is applied based on the underlying value, even if you select the Show Data As options in the Layout pane to show the data as percentages or indexes.

What Factors Affect Conditional Formats?

Layout, Order and Conditional Formats

The layout of the columns in the view affects the conditional formatting of the values of one column when the conditional format is based on another column. Changing the layout of the columns in the view can change the display of the conditional formats. For example, suppose that you specify a conditional format on the Region column where Year is 1999. If Year and Region are on opposite edges of the pivot table, then no conditional formatting is visible.

The order of the columns as they are displayed in the view also affects conditional formatting. The order in the view affects the "grain" at which the values are displayed. You can think of "grain" as a level of aggregation. The conditional format applies when the column to format is displayed at a finer grain or the same grain as the column on which the format is based. If the column being formatted is of a higher grain than the column on which the format is based, then the format applies only if the condition is based on a measure column. If the format is based on an attribute column and the column to format is displayed at a higher grain, then the conditional format does not apply.

For example, consider the table in Figure 7–2. A conditional format has been specified to color the Region column magenta when Year is 1999. Notice that no magenta coloring is visible, because Region is the first column in the table and so is displayed at a higher grain. (The Region column has its value suppression set to Default.) See Value Suppression and Conditional Formats below for more information.

Suppose that you change the order of the columns so that Year is the first column in the table. Then the Year column is displayed at a higher grain, and the appropriate conditional formatting is applied to the Region column. Figure 7–3 shows a table with the appropriate conditional formatting.

Conditional formats can be displayed on any edge of the table, pivot table, or trellis. On the Prompts drop target (also known as the "page edge"), the conditional format applies only to the column member that is currently selected for that target. For example, suppose that you have a Region column that has the members North, South, East, and West. Suppose the Region column is on the Prompts drop target for a pivot table and the conditional format is set to color the region name green if Sales is greater than $10 million. If East and West are the only regions that meet that condition, then each one is colored green only when it is selected for the Prompts drop target.

Value Suppression and Conditional Formats

For tables, the value suppression setting in the Column Properties dialog: Column Format tab affects conditional formatting. If you set value suppression to Repeat, then the column that you are formatting is displayed at the detail grain of the view. No aggregation is needed on the column on which the format is based for applying the conditional format.

For example, consider the table in Figure 7–2. A conditional format has been applied to color the Region column magenta when Year is 1999. Notice that no magenta coloring is visible, because the value suppression is set to Default, which does not allow for repeating column values for the members of Region.

If the value suppression is set to Repeat, then column members are repeated and the appropriate conditional formatting is applied. Figure 7–4 shows a table with repeat value suppression.