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.
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.
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.
Select the orange point and hit Ctrl+1, it will open up the format window, then set the series overlap to 100%.
Once you set the overlap 100%, chart will apear like this.
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
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)
Now, come back to the chart and move your mouse on the series. you will see the magic like this.