Applying Conditional Formatting to Column Content

In tables and pivot tables, 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.

You do this by selecting one or more columns in the report to use, specifying the condition
to meet, and then making selections 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 table cell that contains the
data. The steps to specify a condition are very similar to those used to create filters.

You can add multiple conditions so that the data and the table cell are 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.

Several subtle conditional formatting differences exist between traditional tables and
pivot tables. Conditional formats that format one column based on the value of
another column are not reflected in a pivot table, but are reflected in a standard
table. For example, setting the color of a region name based on the sales in that
region has no effect in a pivot table. However, setting the color of the sales data
based on the value of the sales data is reflected in a pivot table, as is setting the color
of the region name based on the actual name; for example, displaying a value of
Eastern Region in bold colored text.

note: In pivot tables, conditions are evaluated against the values as calculated or
aggregated by the pivot table. Conditional formatting is applied based on the underlying
value, even if you choose the Show As options to show the data as percents or
indexes.

Your selections apply only to the contents of the column for the report with which you
are working.

 

How to…

Add Conditional Formatting to a Column in a Report

1. Click the button for the column, and choose Column Properties from the dropdown
menu. The Column Properties dialog appears.

2. Click the Conditional Format tab.

Conditional Format Tab

3. Click the Add Condition button and choose the column in the report to use to
construct the condition. The New Condition dialog appears.

4. In the Value field, type the condition value.

5. Click the OK button. The Edit Format dialog appears.

6. Make your choices for the font, cell, border, and advanced style formatting
options.

note: If you specify an image as part of the conditional formatting, it appears conditionally
in the results.

7. Click the OK button The Column Properties dialog appears.

8. Specify additional conditions, as required.

9. Click the OK button when you are done.
Columns are evaluated in the order that they are listed.

10. To reorder a column, click the Move Up or Move Down buttons.

11. To delete a column from the condition, click the Delete button.

12. To edit a condition or a conditional format, click the corresponding button.

 

Example of Applying Conditional Formatting to Results

The following example describes how conditional formatting can be applied to
results.

Suppose a report includes ten ranking categories, with a value of 1 in the column indicating
the worst ranking, and a value of 10 indicating the best ranking. You could
apply conditional formatting to show the following:

• One image to indicate a low rank for columns that contain 1, 2, or 3.
• Another image to indicate an average rank for columns that contain 4, 5, 6, or 7.
• A third image to indicate a high rank for columns that contain 8, 9, or 10.

At the Graphics dialog box, selecting the Images Only image placement option
would cause the columns to appear with only images, and not the ranking numbers,
in the results.