v2017.1 allows you to use formulas to conditionally alter the colors of data on the chart. This is similar to conditionally formatting cells. The color of a data element depends on its value.
For example, you could specify that if any bar on a bar chart exceeds a certain value, then it will be colored red.
To conditionally format elements in a chart:
-
Double-click a chart to open the chart wizard. Click the Appearance tab.
-
Click Conditional Colors.
-
Click
Add to add a condition. Choose a color by entering a hex value, or using the color picker. -
Click the formula
icon to set the conditional formula. -
Enter a formula which evaluates to True or False.
In order to get the data element value, there are several special parameters that you can use in the formula. These parameters correspond with the fields on the Data page, which you use to select data for the chart.
The exact parameters depend on the type of chart and the data layout:
@data_label@: Corresponds with the Data Label field, or X-Axis field for scatter and bubble charts. Corresponds with the Point Label field for charts using the Cell Based data layout.
@data_value@: Corresponds with the Data Value field, or Y-Axis field for scatter and bubble charts. Corresponds with the Point Value field for charts using the Cell Based data layout.
@series_label@: Corresponds with the Series Label field. Available for charts using the Row Based data layout.
@bubble_label@: Corresponds with the Bubble Label field for bubble charts.
@bubble_size@: Corresponds with the Bubble Size field for bubble charts.
Note. If you change the type of chart, some parameters may become unavailable. You may have to go back and edit the conditional formulas.
-
Click OK. To add more conditions, repeat steps 3-6.
If there are multiple conditions, they evaluate in order from the lowest row to the highest. To change the order, reorder the rows using the up and down arrows.
Note. You cannot use cell references, data field references, or parameter references in a chart conditional formula.