Conditional cell formatting

Use conditional cell formatting in tables and Gantt charts to highlight individual cells, columns, or rows.

How to set conditional formatting in charts, read Charts conditional formatting.

Overview

Select Cell formatting [1] from the column or row header, the table options. 

Specify one or several rules [2] to detect cells that should have different text or background color, or use a bold font in the column or row. You can set one or multiple rules; the first matching rule will be used. You can also choose the default formatting without any rules.

There are two ways to apply those rules [3]: you can set them to the values displayed in the cells or to the values calculated by a custom formula.  Also, there are several ways to define conditions [4]: Default, Range, Exact value, Regular expression, Top/Bottom, and Heatmap. Select one of them. 

Use the option Apply to all row cells [5] to apply color/background/font style to all cells in a particular row. If another column in the report also has formatting, conditions are merged in cells in this column (formatting from that column is applied if the text, background, or font in this column has a default selection).

Conditional Report Alerts

You can define conditional report alerts in eazyBI reports and charts to receive an automatic email when an alert on a measure is triggered (entered or exited).

Conditional Formatting Assistant

When you need assistance to describe and apply conditional cell formatting, use the conditional formatting assistant. This assistant can suggest which methods (such as range, exact value, or custom formula) and how to apply them to highlight results. Specify coloring rules and what colors you want to use. eazyBI will generate the conditional formatting setup, including a custom formula if needed, which you load and apply to the report.

Default

Specify default cell formatting for all rows without any rules. It can be used to set the text color and background color for cell values. It’s also possible to align text to the center, left, or right, and to make values bold.

Range

Specify ranges of values that should have different formatting. Can be used for numeric values, percentages, dates, or file sizes. Both min and max endpoint values are included in the range.

For dates, use exact dates or relative dates to define a range. If the date is a DateTime value (with a precise time, e.g., Issue creation date, Issue resolution date), the start of that date (00:00) is always used; take this into account when defining the Max value (add one more day).

For percentage values, include % sign (e.g. 10%).

Example

Using relative dates, color-code cells based on issue due date: overdue, due today, and due in the future.

Note for DateTime values. Use the next day for Max value: "today" instead of "yesterday", and range "today" - "tomorrow" instead of "today" - "today".

Report with applied rules (the report was run on Jan 06, 2021):

Exact value

Use exact values to apply rules for text or integer numerical values. This approach is recommended if you have a few specific or unique values (e.g., text statuses, story points) and want to color-code cells by them. Use precise and case-sensitive values. Select the Empty checkbox to highlight cells with empty values.

Example

Using an exact value condition, color the text in all cells for issues with resolutions “Duplicate” or “Won't Do" in different colors.

Report with applied rules:

Regular expression

Use a regular expression to apply rules for text containing some pattern (e.g., starts, ends, or contains specific characters). Use Regular expressions to define the pattern. When used for date values, the date text representation (using the measure date formatting) will be used.

Example

Using a regular expression, color all rows with issues having any label (i.e., having any value in the "Issue labels" column). A regular expression. is used to match any character. 

Report with applied rules:

Top/Bottom

Use Top/Bottom to highlight the top and/or bottom cell values. If several values are the same, then all cells matching the top/bottom values are selected. This option is recommended for numeric values and dates.

Example 

Using the Top/Bottom option, color rows with issues with the three largest and smallest logged hour values. Top values also display bold.

Report with applied rules:

Heatmap

Highlight all values using different intensity colors from the specified color range. By default, minimum, midpoint, and maximum values are detected automatically. If necessary, exact values could be specified. For example, specify a midpoint value of 0 to highlight negative values in one color and positive values in a different color. The heatmap option is recommended for numerical values and dates.

Example

Color as a heatmap table with issues resolved, where lighter color is for weekdays with fewer resolved issues, and darker color is for cells with a larger number of resolved issues.

Report with applied rules:

See the short video:

Using custom formula

By default, the cell measure value is used for selecting the matching rule. In some specific cases, you can override it with a custom calculation formula. Custom calculations use the same MDX expressions as those used in calculated member formulas. You may use an already existing measure as a custom calculation or write the calculation directly in this same form.

The custom formula could be used for a wide range of conditions. The data type of the result of the calculation must be the same as the data type of the values in the cells on which the condition is set.

Example

Color the background for issues assigned to me (i.e., the user who is currently logged into eazyBI). Set the condition on the Issue assignee column if present in the report or on any other text column. In the example, the conditional formatting is set on the Issue assignee column and applied to all row cells. The calculation formula returns "Current user" if the issue assignee is the current user; since "Current user" is a unique value, the exact value condition is used.

Report with applied rules:

Cell formatting for rows

To highlight a row with total or subtotal values, apply a condition on that particular row/column. 

In the Default tab, leave values empty, and select how to highlight the row/column.

Example

In the "All Projects" row, show all values on a pale blue background with bold font.

Report with applied rules:

Cell formatting for Gantt charts

Use all cell formatting options in the Gantt chart table. To apply them in the chart, go to the Gantt tab and select Cell formatting. All cell formatting options available in tables are also available in the Gantt chart table.

Conditional formatting for cells in the Gantt is set separately from that for cells in the report Table view!