I was asked recently whether it was possible to change the font color of a data label in an Excel chart to highlight the maximum value.
Well, sure, anything is possible. And there are at least two ways to accomplish this task. Because I’ve been doing a lot of coding lately, my first thought was an approach using VBA. Then of course I came to my senses, and worked out a non-programmatic approach. If possible, it’s usually advantageous not to rely on VBA for such tasks.
The VBA Approach
Because I thought of it first, I’ll show the VBA method of formatting the label for the maximum value first.
Here is the simple data and chart, with all labels formatted with white text.
For this simple example, I want the tallest bar to have a black label, like this:
Here is the simple VBA routine I worked out to accomplish my task.
Sub HighlightMaxDataLabel() Dim srs As Series Dim vY As Variant Dim iPt As Long, nPts As Long Dim dMax As Double Dim iHighlightColor As Long ' do nothing if user hasn't selected a chart If Not ActiveChart Is Nothing Then Set srs = ActiveChart.SeriesCollection(1) ' highlight color: change to suit iHighlightColor = RGB(0, 0, 0) ' reset all labels to original font color With srs.DataLabels.Font .Color = .Color End With vY = srs.Values nPts = srs.Points.Count ' find maximum value dMax = vY(1) For iPt = 2 To nPts If dMax < vY(iPt) Then dMax = vY(iPt) End If Next For iPt = 1 To nPts ' highlight all labels at maximum value If vY(iPt) = dMax Then srs.Points(iPt).DataLabel.Font.Color = iHighlightColor End If Next End If End Sub
When the data changes, the labels don’t immediately change.
Run the code again, and the labels are now properly highlighted.
Of course you could modify the code and stick it into a Worksheet_Change event procedure to make the labels change when the data changes.
I made sure that the code checked all values, without stopping at the first maximum. This way, both labels are highlighted if there’s a tie for first.
The Non-Programmatic Approach
Here is the same data with a couple extra columns, and the column chart without data labels. The added columns provide data for hidden line chart series which will contain the differently formatted data labels.
Assuming the data is in A1:D6, the formulas are:
These formulas result in only one of the two line chart series having a marker for each column of the column chart.
Start by making the chart using all of the data (left), or if you’ve already got the chart, add the extra series. Then change the chart type of the additional series* to line chart (right).
* right click on the series, choose Change Series Chart Type from the pop up menu, and select the desired chart type.
Add data labels to each line chart* (left), then format them as desired (right).
* right click on the series, choose Add Data Labels from the pop up menu.
Finally format the two line chart series so they use no line and no marker.
When the data change, the chart labels change just as quickly as Excel can calculate the new values in columns C and D. No need to hassle with VBA event procedures.
If more than one value matches the maximum, each will be highlighted as the maximum.