Today I will show how you can create a cool excel chart for your dashboards that responds to mouse over – yes, no clicking required. The final chart is going to look like the one below.
This can be a very useful technique to show some important data points related to your main series. For example in the chart above I am showing the profit made for each year when the user points his/her mouse over the relevant series.
So, let’s see how this is done
Step 1: create a data series for your chart. In this case I am calling this data series ‘Product A’. Once you have done that, create a duplicate series with the same data – i.e. A prime. Now just select all the cells right above the category x-axis (marked in yellow in the image below) and give them a named range – let’s say ‘some_range’. We will be using this named range to store the information for the position of the mouse.
Step 2: create a clustered column chart using this data. You should get a chart like the image below – two series of exactly the same height. Format the series to whatever you like. A point to remember here is that the color of the duplicate Product A series is what the user will see when he moves his mouse over.
Step 3: now we need to make these series overlap each other completely. To do this, select the duplicate series -> right click -> format series->set overlap to 100%
At this point you will only be able to see one series in your chart – the duplicate series (yellow one in my case)
Step 4: Now we need to go back to our data. Replace all the data that you have for the duplicate series with formula that says – if the cell in the ‘some_range’ (yellow region) has a value of ’1′ then return the value of the original series, else return a blank string.
What this will allow us to do is: switch on and off the relevant data in the duplicate series. By now you would have got a sense of what we are trying to do. So when the mouse will move over a column I will change the value of the relevant yellow cell to 1 and when it moves off the chart, I will change it back to 0. This will create an effect as if the chart/series itself is changing color.
Step 5: test that our method works. Change one of the yellow cells to have a value of 1 and rest of them to 0. Your chart should now look like this:
Yup, the column with the value of 1 seems to be highlighted in yellow.
Step 6: Now we need to add something called data labels. Go to developers tab -> insert -> active x control -> label. And add as many labels as you have columns in the chart.
Step 7: Now pick a label and place it right on top of a data column. The label should completely hide the data column (but not the x-axis) and should run up to the very top of the chart.
Step 8: Repeat this step for all the columns. At this stage all your columns would be hidden behind the data labels. Now double click on the first data label. This will take you to the vba editor. In the editor select ‘ Label1’ and the ‘MouseMove’ event for that label (see red boxes in the picture below) and then add this piece of code:
Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ActiveSheet.Range(“some_range“) = 0
ActiveSheet.Range(“some_range“)(1) = 1
We need to do this for all the labels. While repeating it for all the labels the only change we need to make to the code is to change the piece highlighted in red above to 2, 3,4 …. Etc.
Basically we are asking the code that whenever the mouse moves on a label set all the cells in the range ‘some_range’ to zero and set the relevant column to 1, i.e. the column to which the mouse points.
Step 9: Now we need to make the labels on our chart transparent. To do this select all the labels, go to properties and change these two values: 1) delete the caption 2) set BackStyle to 0-fmBackStyleTransparent. And we are done !
Step 10: move mouse over your chart and see the colors changing. You can use the 1/0 flag that we have created to pull any relevant data for the charts, in the example that I have created I am using it to calculate profit.
One thing that you might notice is that when your mouse goes off the chart – the last highlighted columns remains highlighted. The fix to this is – add a large label that covers all your chart and the area around it, make it transparent and add a code that resets all the cells in ‘some_range’ to zero. Something like this:
Private Sub Label10_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ActiveSheet.Range(“some_range“) = 0
Hope you like this trick. You can download the sample workbook that I used for this chart here.