Conditional Formatting

media_1450187057022.png

You can highlight certain column cells that meet a specific condition using the Conditional Formatting menu.

To invoke this menu, right-click the column to which a formatting rule should be applied and select Conditional Formatting.

Available format rules are displayed when expanding the Conditional Formatting menu.

Different options are supported for different columns (depending on the type of data a clicked column displays

Highlighting Cells that Meet a Specific Condition

media_1450187113326.png

Choose the Highlight Cell Rules menu item.

Select the condition type. You can use one of the predefined conditions (the Greater Than…, Less Than.., Between… and Equal To… menu items), provide a custom condition (the Custom Condition.. item) or format cells that contain the specified text (Text that Contains…) or refer to a certain date interval(s) (A Date Occurring…).

media_1450187151500.png

Based on the selected condition type, an appropriate dialog window is invoked. You need to either enter a constant to be compared with the column’s values, or select desired check boxes related to dates, or construct a custom condition in the dedicated editor. After that, choose a format style in the dropdown list, and click the OK button. To apply formatting to an entire row instead of a single cell, select the corresponding check box.

Highlighting Top or Bottom Cell Values

media_1450187174744.png

Choose the Top/Bottom Rules menu item.

Select the rule type. You can highlight cells that contain the highest or lowest values (the Top 10%…, Bottom 10%…, Top 10 Items… and Bottom 10 Items… menu items), and values that are above or below the column’s average (Above Average and Below Average).

media_1450187204946.png

According to the selected rule type, an appropriate dialog window is invoked. Enter a cutoff value (where required), choose a format style in the dropdown list, and click the OK button. To apply formatting to an entire row instead of a single cell, select the corresponding check box.

Highlighting Unique or Duplicate Cell Values

media_1450187250425.png

Choose the Unique/Duplicate Rules menu item.

Select the rule type.

media_1450187280305.png

In the invoked dialog window, choose a format style in the dropdown list, and click the OK button. To apply formatting to an entire row instead of a single cell, select the corresponding check box.

Highlighting Cells Using Data Bars

media_1450187296329.png

A data bar fills a cell according to the ratio of the cell’s value to the highest and smallest column values. A longer bar corresponds to a higher value, and a shorter bar corresponds to a lower value.

To apply a data bar format, do the following

  1. Choose the Data Bars menu item.
  2. Select the bar style format. Solid bars and bars with gradient fills are available in various colors.

Applying Color Scales

media_1450187351860.png

This format shows data distribution and variation using color scales. A cell is filled with the background color that is calculated according to the ratio of the cell’s value to the highest and smallest column values. A two-color scale specifies two colors, which represent the minimum and maximum column values. Cell values residing between the minimum and maximum values are painted using a shade of these colors. A three-color scale additionally defines a color for the middle value and so, uses a gradation of three colors.

Choose the Color Scales menu item.

Select one of the predefined two or three-color scales.

Highlighting Cells Using Predefined Icons

media_1450187399730.png

Icon sets allow 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. In the Positive/Negative group, the available icon sets divide column values into three ranges: positive values, negative values and values equal to zero.

Choose the Icon Sets menu item.

Select one of the predefined icon sets.

Deleting Rules

media_1450187440618.png

If you have already applied one or more rules to columns, the additional Clear Rules item is displayed at the second level of the Conditional Formatting menu.

You can do one of the following.

  1. To delete conditional formatting rules from the target column, click the Clear Rules from This Column menu item. If the column has no rules applied to it, this item is not shown.

  2. To delete formatting rules from all columns, click the Clear Rules from All Columns menu item .

Showing Summaries

media_1450186993130.png

Right-click a region within a group footer or grid footer under a specific column.

media_1450187007243.png

A context menu displaying a list of supported summary types will be displayed.

Find Panel

Show Panel

media_1450186635254.png

Ensure that no cell editor is active and press Ctrl+F.

media_1450186682637.png

In Grid Views, choose the Show Find Panel command from a column menu.

Clearing the Search

media_1450186787145.png

Press ESC or click the Clear button.

Closing the Find Panel

media_1450186828833.png

Click the ‘x’ button to the left of the search box or:

If the search box is empty, press ESC (the shortcut is in effect if the search box has focus);
If the search box is not empty, press ESC twice (the shortcut is in effect if the search box has focus).

Search Syntax

media_1450186908919.png

Filtering Data

Filter Dropdown List

media_1450186141318.png

Hover over the column header and click the filter button within the column header that appears.

Creating a Simple Filter

media_1450186238609.png

Invoke the filter dropdown list containing available filter values.

Select the required filter value in the filter dropdown list.

Excel Style Filter Dialog

media_1450186331985.png

Invoke the filter dropdown list (see above) and click Custom.

This will invoke the Custom Filter Dialog, allowing you to compare a column with one or two values:

Advanced Filter Dialog

media_1450186364002.png

Right-click any column’s header and select Filter Editor.

media_1450186386832.png

If the filter panel at the bottom of the grid control is visible, click the Edit Filter button

media_1450186401527.png

The Filter Editor is also invoked when choosing the (Custom) item in a column’s filter dropdown list, if the current filter criteria applied to the column consists of three or more simple filter conditions, or if the filter criteria contain advanced comparison operators such as “Is between” and “Is any of”.

Clearing the Filter

media_1450186444103.png

To clear the filter applied to a specific column, do one of the following:

  1. Invoke the filter dropdown list (see below) and click (All).
  2. In Grid Views, right-click the column header and select Clear Filter.

To clear all filter criteria, click the Close Filter button within the Filter Panel:

Disable/Enable the Filter

media_1450186496892.png

Click the Enable Filter button within the Filter Panel.

Hiding and Displaying Columns

Hiding Columns

media_1450185988511.png

Click a column header/band header and drag it onto the grid control’s cell area, until the cursor changes its image to a big ‘X’. Then drop the header.

media_1450186013745.png

or Drag and drop a column/band header onto the Customization Form if it’s open

Displaying Columns

media_1450186042324.png

Open the Customization Form by right-clicking a column header and selecting Column Chooser.

media_1450186059570.png

The Customization Form will be displayed listing hidden columns and bands (if any).

media_1450186074757.png

Drag the required column/band from the Customization Form onto the column/band header panel and drop it at a specific position.

Reorder Columns

media_1450185898341.png

To reorder columns, drag and drop a column header to a new position.

media_1450185919689.png

To reorder bands, drag and drop a band header to a new position.

Resizing Columns

media_1450185749092.png

To resize columns and bands, drag the right edge of the target column/band header:

media_1450185775477.png

To change a column’s width so that it displays its contents compactly in their entirety, do one of the following:

  1. Double-click the right edge of the column header.

  2. Right-click the column’s header and select Best Fit:

To change the widths of all columns so that they display their contents in the best possible way, right-click the header of any column and select Best Fit (all columns).

Grouping

Grouping Data

media_1450185525492.png

Drag a column header from the column header panel to the group panel:

media_1450185576794.png

or Right-click a column header and select Group By This Column from the context menu:

Ungroup Data

media_1450185603700.png

Drag a column header from the group panel to the column header panel:

media_1450185647571.png

or Right-click a grouping column’s header and select UnGroup from the context menu:

media_1450185680112.png

To remove grouping by all columns, right click the group panel and select Clear Grouping from the context menu:

Change Group Order

media_1450185698250.png

To change group order, move a grouping column header to another position within the group panel.

Sorting Columns

media_1450185369848.png

To sort records by a column’s values and replace existing sort conditions that are applied to the current or other columns, click the target column’s header, until an Up or Down Arrow icon is displayed within the header.

The Up and Down Arrows indicate ascending and descending sort orders respectively.

media_1450185406929.png

To sort records by a column’s values while preserving existing sort conditions, do one of the following:

  1. Click a column header while holding the SHIFT key down, until an Up or Down Arrow icon is displayed within the header.
  2. Right-click a column header and select Sort Ascending or Sort Descending from the context menu that appears:

To remove sorting by a column, click a column header while holding the CTRL key down. You can also select Clear Sorting from the column header context menu.