Knowledge base KB0186

Can I use conditional formatting to change colors depending on data values?

Enter your data in a separate Excel workbook and link it via think-cell Excel data links (see 21. Excel data links in our user manual).

Once the conditions are finalized, the Excel link can be severed if desired, by breaking the link or deleting the Excel file. The data and conditions will still be available in the internal think-cell datasheet, though the conditional formatting cannot be changed after breaking the Excel link.

Set up conditional formatting

To use the conditional formatting for your think-cell chart, choose the Use Datasheet Fill on Top option as described in 3.8.2 Color scheme in our user manual.

To set up conditional formatting, select the respective data cells in the Excel file. For line charts, the fill color of data cells controls the color of markers (see 3.8.8 Line scheme). The line color itself is controlled by the cell containing the line series label.

These cells may contain any number format, formulas, and references to other cells.

References in cells are supported: references: cells.

In Office 2010 and later, you can access the conditional formatting dialog from HomeStylesConditional FormattingNew Rule…. Choose Format only cells that contain in the dialog.

Rules for conditional formatting in think-cell

Excel: Conditional Formatting. Only Cell Value conditional rules are supported by think-cell.

For the definition of the value range the following operators may be used:

  • +, -, *, /, ^, &, and %
  • =, <, >, ≤, and ≥
  • Please note that conditional formatting does not work for rules containing references to other cells.
    Rule definition supported by think-cell: references: rules supported.
    References in rules are not supported by think-cell: references: rules not supported.

think-cell does not support using conditional formatting rules to change the number format of cells. This can still be set via Excel's Format Cells... dialog (see 6.5.3 Number format in our user manual).

Troubleshooting

Due to some restrictions in Microsoft Excel, the update of the think-cell chart might not occur immediately after changing the rules for conditional formatting. You can trigger an update by changing any data value of the respective chart. Two known cases are documented in the knowledge base articles:

Share