To change a conditional format, do the following: The Edit Formatting Rule dialog box appears. Select the duplicate, then select Edit Rule. The duplicate rule appears in the dialog box.

To add a new conditional format based on one that is already listed, select the rule, then click Duplicate Rule. The New Formatting Rule dialog box appears. To add a completely new conditional format, click New Rule.

The Conditional Formatting Rules Manager dialog box appears. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied. The top color represents higher values, and the bottom color represents lower values. Hover over the color scale icons to see which icon is a two-color scale. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

Select one or more cells in a range, table, or PivotTable report. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value other than an error value. Tip: If any cells in the selection contain a formula that returns an error, the conditional formatting is not applied to those cells. However, when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of: When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by default. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to option button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box.Ī contiguous set of fields in the Values area, such as all of the product totals for one region.Ī non-contiguous set of fields in the Values area, such as product totals for different regions across levels in the data hierarchy.Īvoid making many non-contiguous selections.Ĭonditionally format a set of fields in the Values area for all levels in the hierarchy of data.Ĭonditionally format a set of fields in the Values area for one level in the hierarchy of data. The default method of scoping fields in the Values area is by selection. There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field. Note: In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children. The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows. If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed. These restrictions are mentioned in the remaining sections of this article, where applicable. For example, you can't format such fields based on whether they contain unique or duplicate values. There are some conditional formats that don't work with fields in the Values area of a PivotTable report. However, conditional formatting in a PivotTable report has some extra considerations: Conditional formatting typically works the same way in a range of cells, an Excel table, or a PivotTable report.

Freeplane conditional node formatting