In Excel charts you can only set the X-axis label color uniformly, not individually. But is it possible to have an X-axis label automatically change color when the data reaches a certain level?
Now we will demonstrate using the following example:
In Excel charts you can only set the X-axis label color uniformly, not individually.
Add multiple new line series and use their data labels to replace the original X-axis labels.
- Add a helper column in Column C
- The formula is =IF(B2<=100,0,NA()), meaning if B2 is less than or equal to 100 the result is 0, otherwise N/A.
- Add a helper column in Column D
- The formula is =IF(B2>100,0,NA()), meaning if B2 is greater than 100 the result is 0, otherwise N/A.
- Click the chart, Design ⇒ Select Data ⇒ Add
- Series name: $C$1；Series values: $C$2:$C$13 ⇒ OK
- Add once more: Add ⇒ Series name: $D$1; Series values: $D$2:$D$13 ⇒ OK
- OK
- Design ⇒ Change Chart Type ⇒ Combo
Existing series: Clustered Column
Two added series: Line
- The chart should now look like this:
- Click the X-axis labels ⇒ Ctrl + 1 ⇒ In Text Options, under Labels set Label Position to None
- Click one of the lines ⇒ Design ⇒ Add Chart Element ⇒ Data Labels ⇒ Below
- Repeat step 2 for the other line
- Click the Data Labels you just added ⇒ Ctrl + 1 ⇒ In Label Options, under Label Contains select Category Name and deselect Value
- Repeat step 4 for the other line's Data Labels
- Click one of the lines ⇒ Ctrl + 1 ⇒ In Series Options, under Line select No Line to hide the line
- Repeat step 6 for the other line
- The chart should now look like this:
- Click one set of Data Labels (i.e., months >100) to change the color
- Click the other set of Data Labels (i.e.months <=100) to change the color
- Completed
