Conditional Format
This page covers field- or row-level formats which vary according to the data in individual cells.
Bar Charts and Heat Maps
On any numeric field, click the Horizontal Bars button (
) in the toolbar to get a bar chart:
By default, the bar chart axis adjusts to accommodate whichever largest value is currently being displayed. You can adjust the axis manually with the Numeric Minimum/Maximum properties in the Format popup:
If a value exceeds the specified axis range, its bar is shown with a “tear” indication (
).
An alternative visualization style for numbers is the heat map. To activate this, press the dropdown arrow next to the bar chart icon in the toolbar, and click Heat Map (
).
The bar chart and heat map visualizations can be used side-by-side in different fields. Each field defines its own axis range.
The bar chart or heat map options can be used in combination with a crosstab layout to compare data side-by-side.
Color If Negative/Positive
For numeric fields, the Color If Negative/Positive option is provided in the Format popup. It allows numbers to be displayed in red or green depending on its sign. These styles are common in financial reports.
Color or Strike If True
Fields with checkbox values (
) are known as logical (or “boolean”) fields. For logical fields, the Format popup contains several options that apply when the condition is true.
The checkbox value can be stored in a table, or come from an arbitrary logical formula.
The relevant format options on logical fields are:
- Color If True: Background color to use when the value is true. See the screenshot above.
- Apply Color To: The scope of the conditional background color. The color can apply to the cell containing the checkbox, the next cell that is not a checkbox, or the entire row.
- Strikethrough Row If True: De-emphasize and strike through the row when the value is true. This style could be used to indicate e.g. completed tasks or expired items.
The background color and strikeout styles can be combined if desired.
Sidebar Legend
When the options above are set, a small color icon (
), or a color/strikethrough icon (
), is shown next to the logical field in the Fields sidebar as a legend.
You can have multiple color-coded fields. If Apply Color To is configured to affect the same row or the same following cell, the earlier activated color takes precedence. The checkbox field itself can be hidden if desired.
Toolbar Shortcut
In the toolbar, the Conditional Format (
) button can be used to quickly set the aforementioned options, based on the value in the currently selected cell:
When necessary, a new logical formula (e.g. [Country] = "USA") will be inserted to hold the requested formatting rule. The formula will be hidden by default. If desired, you can unhide the formula from the field selector and modify it to set arbitrary conditions.
If the currently selected cell has a color or strikethrough style active from an existing format rule, then the toolbar button's actions will relate to the existing rule. In the latter case, the toolbar button's icon will indicate the applicable color and/or strikethrough setting (
).
Dialog Box Shortcut
Similar functionality is available via Format→Conditional Format (Command+5/⌘5). The latter action is provided for discoverability and keyboard accessibility purposes.
Color Scheme
The available colors have been selected to provide contrast against each other and against other indications, on both Light and Dark Mode.
