Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Collapse]Pivot Grid
   [Expand]Getting Started
   [Expand]Fundamentals
   [Expand]Binding to Data
   [Expand]Data Shaping
   [Collapse]Data Analysis
     Conditional Formatting
     Integration with the Chart Control
     Key Performance Indicators (KPIs)
   [Expand]Layout
   [Expand]Focus and Navigation
   [Expand]Printing and Exporting
   [Expand]Appearance
   [Expand]Design-time Features
   [Expand]Miscellaneous
   [Expand]UI Elements
   [Expand]End-User Capabilities
   [Expand]Examples
   [Expand]Member Tables
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Conditional Formatting

The Pivot Grid control includes a Microsoft Excel-inspired conditional formatting feature that allows you to change the appearance of individual cells based on specific conditions. This feature helps to highlight important information, identify trends and exceptions, and compare data using the collection of format rules.

You can add, remove or edit format rules in different ways: using the PivotGrid Designer at design time, using API or at runtime. You can easily export the result of conditional formatting to a file in various formats (HTML, MHT, PDF, RTF, TXT, CSV, XLS or XLSX).

Expanded Create Format Rules

To create a new formatting rule at design time, invoke the PivotGrid Designer and go to the Format Rules page in the Appearances section.

  1. Use the button to add a new rule to the collection. To delete the rule, select it and click the button.

  2. Set the PivotGridFormatRule.Measure property that defines the data field to whose values the format rule is applied.
  3. Select one of the PivotGridFormatRule.Settings property values that specify which data cell/field intersections are involved in conditional formatting. You can select an intersection of row and column fields or specify the type of cells.
  4. Select the FormatRuleBase.Rule property value that specifies the type of a format rule and defines condition and appearance settings applied to data cells.

    You can select one of the following rules.

    Format rule

    Condition Class

    Short Description

    Fields Intersection Support

    Data Cells Specifying Support

    Format based on value FormatConditionRuleValue Applies a format if a column's value meets a specified condition (Equal, Less, Between, etc.).
    Format based user defined expression FormatConditionRuleExpression Applies a format if a cell value(s) meets a specific expression.
    Format only values that contain FormatConditionRuleContains Applies a format if a value(s) matches one of the constants.
    Format only values that are above or below average FormatConditionRuleAboveBelowAverage Applies a format if a cell value(s) is above or below the fields intersection data average.
    Format using two-color scales FormatConditionRule2ColorScale Applies a format using a two-color scale to display data distribution and variation using a gradation of two colors.
    Format using three-color scales FormatConditionRule3ColorScale Applies a format using a three-color scale to display data distribution and variation using a gradation of three colors.
    Format only top or bottom rank values FormatConditionRuleTopBottom Applies a format if a value is in the range of the highest or lowest field intersection data values.
    Format using Data bar FormatConditionRuleDataBar Applies a format using a data bar. The bar length changes proportionally to cell value. A longer bar corresponds to a higher value, and a shorter bar corresponds to a lower value.
    Format using icons FormatConditionRuleIconSet Applies a format using an icon set. An icon set format allows you to classify column values into several ranges separated by threshold values, and display a specific icon in a column cell according to the range to which this cell value belongs.
  5. Set the appearance of formatted cells. You can do this in two ways.

    • Use the rule's PredefinedName property that sets the name of the predefined style pattern for the current conditional formatting rule.
    • Use the rule's Appearance property that provides access to appearance settings.

The image below demonstrates a configured rule.

To check whether the format rule is correct, use the PivotGridFormatRule.IsValid property. If this property returns true, the format rule is applied to Pivot Grid data cells.

To disable the format rule, set the FormatRuleBase.Enabled property to false.

Expanded Edit Format Rules

There are two general ways of applying formatting rules to the Pivot Grid control at runtime: by using the Format Rules Menu or the Conditional Formatting Manager.

Format Rules context menu

If the PivotGridOptionsMenu.EnableFormatRulesMenu property value is set to true, end-users can invoke the Format Rules context menu and select a required rule and predefined style from the list.

Important

To enable the multi-column item layout at the third menu level shown in the image above, add the BarManager component to the form. Otherwise, menu elements at the third menu level will be arranged in a linear list; specific menu items will be displayed without glyphs.

Conditional Formatting Rules Manager

End-users can create, sort and modify the created rules using the Conditional Formatting Rules Manager. To allow end-users to use the Conditional Formatting Rules Manager, ensure that the control's PivotGridOptionsMenu.EnableFormatRulesMenu property is set to true.

To invoke this manager at runtime, select the Manage Rules... item from the Format Rules context menu.

  • To create a new rule, click the New Rule... button, select a rule type and specify its parameters. Depending on rule type, you can set maximum and minimum values, specify format, colors or icon style, etc.
  • To edit the existing rule, click the Edit Rule... button and change the rule parameters. Besides that, you can see the rule's format preview and specify the row, measure and column in the list of rules in the main window.
  • To delete the rule, select the rule and click the Delete Rule button.
  • To reorder rules, select the rule and click Up or Down button.

Expanded Create Format Rules in Code

The Pivot Grid control allows you to apply conditional formatting to data cells or field value cells. By default, an empty collection of style format rules is created with a new instance of the PivotGridControl. The PivotGridControl.FormatRules property provides access to a collection of PivotGridFormatRule objects that are used to define formatting settings.

To add a new format rule, create the PivotGridFormatRule object and specify the following settings.

Finally, add the created format rule to the PivotGridControl.FormatRules collection.

Expanded Conditional Formatting Limitations

See the list below for information on conditional formatting limitations.

Expanded Example: How To Apply Format Rules

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T191674.

The following sample applies format rules to Pivot Grid Control's data cells. The FormatConditionRuleDataBar class allows you to see a cell value relative to other cells. A longer bar corresponds to a higher value, and a shorter bar corresponds to a lower value. Cells placed at the intersection of 'Year' column and 'Sales Person' row are painted with yellow gradient.

The image below shows the result.

Expanded See Also

How would you rate this topic?​​​​​​​