How to highlight chart points in excel on mouse hover

Do you know you have mouse hover event for excel charts? Yes, There are few events associated with chart objects. you can use them to to make some interactive visualisation. In this article we will see how we can highlight a point in chart series when hovering mouse cursor on it.

Preparing Data

let’s take a very simple example here, we have a table with country name and their GDP ($ Billion). it looks like the table below.

Countries GDP

Prepare Calculated Column

We will also need a calculated column in order to show the point highlighted. this is pretty simple formula, We will have a cell which we will populate by hover event, lets call that cell “SelectedCountry”. in this calclated column, we will say if country in first column is equal to SelectedCountry, Populate price else show NA(). see the screenshot below.

Inserting a chart

Now, Lets select this data and hit F11, it will insert a chart sheet, like showing below.

Mouse Hover

Select the orange point and hit Ctrl+1, it will open up the format window, then set the series overlap to 100%.

Mouse Hover

Once you set the overlap 100%, chart will apear like this.

Mouse Hover

At this point you can see that if you change the country name in “SelectedCountry” cell, that country’s point in the chart will become orange, technically, the second series’s point will overlap the first series’s point. Now, we need to insert event that will populate “SelectedCounty” cell when we hover mouse.

Insert a chart event

Right click on chart sheet tab and click “View Code”, insert this code there

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

      Dim lngElementType As Long
      Dim lngSeries As Long
      Dim lngPoint As Long
      Dim srSeries As Series
      Dim varXValues

      Me.GetChartElement x, y, lngElementType, lngSeries, lngPoint
      If lngElementType = 3 Then
            If Me.SeriesCollection(lngSeries).Name = “GDP” Then
                  Set srSeries = Me.SeriesCollection(lngSeries)
                  varXValues = srSeries.XValues
                  Sheet1.Range(“rngSelected”).Value = varXValues(lngPoint)
            End If
      End If
End Sub

Now, come back to the chart and move your mouse on the series. you will see the magic like this.

Mouse Hover

Download  a working file from here, Post your feedback in commentbox

Highlight-Chart-series-point-on-mouse-hover.xlsm (107 downloads)

See More

Mouse Hover event on worksheet

XY Scatter Charts 1

XY Scatter Charts 2

XY Scatter Charts 3

1 reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *