Office hacks: chart classification axis labels also change color

When expressing data in a bar chart, to highlight the series that do not satisfy the condition, its X-axis labels can be set to other colors to show the difference. For example, if a salesperson is required to fail to reach 30 sales tasks, he or she is considered to have failed and should be marked red on the name (Figure 1). So, how to set the label color automatically according to the data change? Actually, it is easy to do so by using composite charts.

1824A-BQBS-1

First, on the basis of the original data to add auxiliary columns, so as to build the data source needed for the chart. Such as the original data area A1:B9 for each salesperson corresponding to the number of sales, increase the auxiliary column “red label”, enter “=IF(B2<$B$11,0,NA ())” in cell C2, fill down to C9; increase the auxiliary column “black label”, enter “=IF(B2>=$B$11,0,NA())” in cell D2, fill down to D9. select A1:D9, insert a cluster bar chart, right-click the chart and select “Change Chart Type”, select “Combination” in the pop-up window, and set “Red Label”, “Black Label The chart type corresponding to the “red label” and “black label” are changed to “Line Chart with Data Label” (Figure 2).

1824A-BQBS-2

Next, select the “Red Label” series in the chart, right-click, select “Add Data Label”, select the added “Red Label” series of data labels, right-click, select “Set Data Label Format”, in the “Label Options” tab, check the “Category Name”, remove the checkboxes for “Value” and “Show Guide Line”, and select “Down” for “Label Position” (Figure 3).

1824A-BQBS-3

Next, right-click the horizontal coordinates, select “Set Axis Format”, in the “Axis Options” tab, “Label → Label Position” at select “None”; no lines, no fill; select the chart in the “red label” series (that is, the chart represents the “red label” series of charts), in the “Series Options → Fill & Line → Label” tab, “Data Label Options” at select “None”; select the data label representing the “red label” series, the color is set to “Red” (Figure 4).

1824A-BQBS-4

By the same token, the “Black Label” series is set up as above, except that the label color of the data should be set to “Black”.

Finally, beautify the chart, delete the legend and you are done.

Leave a Comment