Conditional formatting in Excel charts

In PTS's advanced charting training course, there is a Conditional formatting in charts, which is how to apply conditional formatting to a chart. We know that you can set conditional formatting on cells in Excel, but you don't have this feature on a chart. PTS skills, in fact, is a form of conditional imitation. Today, this log is a way to tidy up this skill.

1. Apply conditional formatting to column chart columns.

This is the most common chart conditional format requirement and method. For example, we use a column chart to compare the completion of an index of each branch, we would like to the average (or planned progress and other thresholds) of the pillars in green, lower than the average column in red.

The idea of its making is that

A, according to the condition judgment, the original index sequence is separated into above average and below average two sequences.

b, using these two sequences to do stacked column chart, two sequences are filled in different colors.

In fact, a lot of people in order to make "how to set complementary color" positive negative effect of different fill color, is the use of this method.

The application of this method is also:

* Always mark the maximum minimum in the chart by separating the maximum and minimum values from the original sequence to join the chart as a secondary sequence.

* Identify specific dates in the chart, such as weekends, by separating the weekend data points from the original sequence to join the chart.

The above methods also apply to bar graphs and graphs.

2. Apply conditional formatting to the y-axis numerical label.

This technique takes advantage of the custom format of the value, which displays the value label of the y-axis above a value (such as the budgeted completion rate, 50%) as green, and below a certain value in red. In axis format number customization type, enter:

Red [<=50]#,# #0; Blue [>50]#,# #0; g/General format

50 of which are the thresholds we set, specified as required.

This technique can also be used for data markers in a data series in a chart, so that the data labels for each data point are displayed in the corresponding color.

3. Apply conditional formatting to the X-axis category label.

The x-axis category label itself cannot set conditional formatting, but this technique is only simulated using auxiliary sequences. In the following illustration, the X-axis label above the average (or other threshold) is shown as green, and below is shown in red.

The ideas in the previous 1,

A, according to the condition judgment, the original index sequence separates two auxiliary sequences, but the corresponding numerical value is 0.

b, with the original sequence to do column chart, set its x-axis without category labels.

C, add these two auxiliary sequences to the chart, the type is a graph, hidden invisible.

D, the data labels that set the auxiliary sequence are displayed as category names, aligned below. The x-axis classification labels of two sets of data markers are respectively set up different data formats to realize the conditional formatting.

The way I know to use conditional formatting in charts is these, and there are other forms of chart conditional formatting and applications that are welcome to add.

Now, give this blog an interactive topic 3: How does the red-green up and DOWN arrow on the column chart in the following figure work? You are welcome to leave your production ideas in the comments, or send your production documents. Look forward to participating!