Knowledge base KB0186

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

To keep the internal datasheet simple, the conditional formatting dialog is not available in the internal datasheet. To use conditional formatting in think-cell, simply compile your data in a separate Excel workbook and link it via think-cell Excel data links (see the chapter Excel data links in our user manual).

If you do not need to change the conditions or colors anymore, you do not have to keep the Excel link after setting up everything. The data and conditions will be available in the internal think-cell datasheet after breaking the link (or deleting the Excel file). However, the conditional formatting cannot be changed after breaking the Excel link.

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

Set up conditional formatting in Microsoft Excel

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. 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 2007 and later click on HomeStylesConditional FormattingNew Rule…. Choose Format only cells that contain in the conditional formatting dialog.

Rules for conditional formatting in think-cell

Excel: Conditional Formatting

think-cell supports up to three rule types for conditional formatting. The conditional rule for the Cell Value may use one of the following options:

  • between / not between
  • equal to / not equal to
  • less than / greater than
  • less than or equal to / greater than or equal to

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

Changing the number format by conditional formatting is not supported by think-cell. However, think-cell can use a number format from Excel: First choose the desired number format in Excel using its Format Cells... dialog, then select Use Excel Format from the bottom of think-cell’s number format control. The number format cannot change the font color of think-cell labels.


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