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 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 Excel Fill on Top option as described in 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 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

Cells can fulfill the conditions of up to three rules; additional rules will then be ignored by think-cell, even if the cell value would meet their condition(s).

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 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

think-cell uses cookies to improve the functionality, performance and security of this site. Your consent is necessary if you want to use the full functionality of this site. More information on the use of cookies by think-cell, your consent and your privacy rights can be found in our privacy policy.