1. Modeling
  2. Working with Data
  3. Conditional Formatting

Conditional formatting enables you to color-code a cell dependent on the value in that cell or another cell.

You can:

  • apply conditional formatting to a grid in a module or on a dashboard
  • create one set of rules for a module grid and a different set of rules for a dashboard grid
  • apply different rules to different line items

The Conditional Formatting dialog allows you to enable conditional formatting, create new rules, edit existing rules, or delete rules. If the Enable Conditional Formatting option is not selected, any existing rules will be deactivated.

The check box next to the conditional formatting icon on the toolbar, enables you to turn all conditional formatting rules on or off with a single click.

Revenue By State module shown with Conditional Formatting Icon selected

End users can’t save the changes they make to conditional formatting (but they can export them to .pdf). Administrators can save any changes they’ve made.

Conditional Formatting on Dashboards

Conditional formatting on a dashboard can be independent of conditional formatting on a module grid. It can be applied to a grid in a dashboard without affecting the original grid. A module grid with no conditional formatting applied can have conditional formatting applied to its dashboard grids.

If there are multiple grids on a dashboard that are based on the same module, you can apply different conditional formatting to each without affecting the base module’s settings.

Whether in run-time or dashboard design mode, Conditional Formatting can be accessed from the Format option on the dashboard element menu.

Conditional formatting is enabled for all users by default. Workspace Administrators can remove access to this option via Menu Options in the Dashboard Designer.

Apply conditional formatting

Conditional formatting can be applied when in a grid:

  • in a module
  • on a dashboard in dashboard design mode
  • on a dashboard in run-time mode
  1. On the Format menu, click Conditional Formatting and then click New Rule.

    The New Rule dialog appears:

  1. Select the line item to format. Usually the color is based on values from the same item, but you can override this and pick the values from another item.
  2. Do one of the following:
  • On the Values and colors list, select 2-color scale and set the Minimum and Maximum values.

  • Or, pick a 3-color scale and set the Minimum, Mid-point and Maximum values.

  1. Click OK to save the rule.
    The colors will be shaded gradually between these values. Values that exceed the maximum will be given the maximum color. Values less than the minimum will be given the minimum color. You can change the colors used by selecting from the list.

  1. To save the conditional formatting, on the View menu click Save.

Validate data-entry forms

Conditional formatting lets you color-code a cell dependent on the value in that cell or another cell. It can be used to indicate warning or error conditions when completing forms or grids.

Suppose you have an HR form that tracks your monthly pension costs for staff by pay grade:

Now, you want to ensure that any data-entry errors or omissions are clearly flagged. You can identify data issues on your form using conditional formatting:

Here's how to do it:

  1. Add a new line item to your module that will act as an error measure for another data-entry line item. Here, Error Grade performs this role. We can then use a formula for this number-formatted line item to return an error measure value, conditional on the value entered in the text formatted Grade line item:

    The values returned into Error Grade represent missing entries—equals 1—or invalid entries—equals 2—in the Grade column.
  1. Highlight the Grade line item and then choose Format > Conditional Formatting or click on the toolbar. The Manage Conditional Formatting dialog appears.
  2. Click New Rule and then configure the conditional formatting you want to show for Grade, based on the values returned into Error Grade:

  1. Click OK. You'll see a preview of the conditional formatting rule:

  1. Click OK. The conditional formatting is applied to the Grade line item:

  1. If you want to switch off the color-coding, clear the check box next to the conditional formatting icon on the tool bar.
  2. Optionally, you can add additional conditional formatting rules based on a line item you've set up. In this example, color-coding will be applied to Basic Pay, conditional on the value in Error Grade: