Conditional formatting in Excel chart

 Conditional formatting in the chart
(text / ExcelPro chart blog)
advanced charts in the PTS training courses, there is a Conditional formatting in charts of the content, that is, how to apply conditional formatting in the chart. We know , excel can set the conditional formatting of cells, but the chart is not the function. PTS techniques, in fact, nothing more is to imitate the conditions of the format. today's log and sort out the techniques in this regard.
1, right column charts apply conditional formatting to the columns.
This is the most common format requirements and methods of chart terms. For example, we compare with the column index of the completion of a particular branch, we want to higher than average (or plan progress and other threshold) the columns in green, lower than the average post in red.
its production idea is,
a, under the conditional, the original target sequence separated into above-average and low the average of the two sequences.
b, the two sequences do with stacked column, the two sequences were filled in different colors.
In fact, many people in order to make a negative effect of different fill color is the use of this method.
the application of this method as well:
* marked on the chart is always maximum and minimum, the principle is isolated from the original sequence of maximum and minimum values as auxiliary sequences to join the chart.
* identification chart, such as specific dates such as weekends, the principle is isolated from the original sequence of data points over the weekend to join the chart.
above method also applies to bar charts, graphs.
2, the value of Y axis labels apply conditional formatting.
value of this technique using a custom format, the Y axis value labels, above a certain value (such as budget completion rate, 50%) is green , below a certain value displayed in red. In the Format Axis 50 ]#,## 0; G / common format
50 threshold is set according to need to specify.
This technique can also be used in the data series chart data labels so that each data point Data tab displays the corresponding color.
3, the classification of the X-axis labels apply conditional formatting.
X-axis category labels alone can not set conditional formatting, but this technique is to use auxiliary sequence of simulation Bale. under In the figure, higher than the average (or other threshold) of the X-axis labels appear in green, below the display is red.
1 with the above ideas,
a, conditional based on the original target sequences isolated from two auxiliary sequences, but the corresponding values were 0.
b, to do with the original sequence column, set its x-axis without category labels.
c, these two auxiliary sequences add chart type graphs, hidden is not visible.
d, supporting the sequence data set is displayed as the class name symbol, aligned to the bottom. Two sets of data simulated x-axis classification symbol labels were set to their different data format, implement conditional formatting.
I know the use of conditional formatting in the chart is that these practices, and whether there are other forms of conditional formatting the chart method and application, we welcome supplement.
now, to the topic of this blog, an interactive 3: The figure in the column on the red and green arrow is how to do? welcome in the comments leave your production ideas, or the production of documents sent to you. look forward to participating!