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.
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.
In Office 2010 and later, you can access the conditional formatting dialog from Home > Styles > Conditional Formatting > New Rule…. Choose Format only cells that contain in the dialog.
Rules for conditional formatting in 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 in rules are not supported by think-cell:
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: