Dynamically Highlight data points in Excel charts using Form Controls

0
5456

Charts and graphs and other visual means make information easier to understand and analyze. Excel gives plenty of options in this department as different types of graphs are possible.

But in some cases wrong chart types in the situation impair chart’s visual effectiveness. For example in case of line chart with many data points it may become difficult to trace which data point is related to which series. One way to overcome this problem is to dynamically highlight the data point as follows:

highlight data points 1

The original idea for this chart came from the post by Sumit on his Excel blog. I really liked it but he used VBA method to get the job done which is also good. But I will be using non VBA technique using Excel form controls and it will save you lots of coding know how and not to depend on codes every time. So I hope my technique will help even those Excel users that are not versed in VBA coding.

Download Tutorial Workbook
To follow the steps discussed in this tutorial download this sample Excel workbook to follow the instructions and learn the technique better.

Highlight Data points – Step by Step

Step 1: Download the tutorial workbook and open it. And immediately copy the data by selecting the range and then pressing and holding CTRL key down and moving the selection box to the right.

highlight data point 2

Step 2: Select the initial range and insert line chart with markers by going to Insert tab > Charts group > Line chart button > click line chart button with markers

highlight data point 3

Step 3: Now here is the ingenious part. Copy the second range of data that you previously replicated and paste it on the chart. Yes on the chart! not anywhere in the worksheet.

highlight data point 4

Did you see what just happened? Excel has inserted another data series right over the previous one.

Step 4: Time to insert buttons. Go to developer tab > Controls group > click Insert drop down button > click option button. The cursor will change to plus sign. Click anywhere in the worksheet to insert the button. Right click on the button and click form control. In cell link box mention the cell you want to connect to this button. I chose K2.

Step 5: Now insert three more buttons following the same process as mentioned in Step 4. In my case all the next three buttons were by default connected to cell K2.

highlight data point 5

We need four buttons because we have four years i.e. 2012, 2013, 2014 and 2015. Change the text of each button accordingly.

To check if buttons are working appropriately you can do so by clicking each button and seeing if the value in cell K2 changes on click.

highlight data point 6

Step 6: In column J from cell J2 to J5 put a series of number from 1 to 4. And in cell L2 put this formula and drag the fill handle down to cell L5.

=$K$2=J2

highlight data point 7

Step 7: Now go back to cell F2 and after deleting existing content from the cell paste this formula:

=IF($L2=TRUE,B2,NA())

Drag the fill handle down to cell F5 and then dragging it again across to column I to right.

highlight data point 8

Step 8: Click 2012 button and right click on the relevant data series in the chart. Click Format data series. And make the changes to line and marker as shown in the animation below:

highlight data point 9

Do exactly the same with other three data series. Once done you will have the chart like this:

highlight data point 10

Step 9: Clear out the unnecessary elements in chart by left clicking them and hitting delete button on the keyboard like chart title etc.

highlight data point 11

Step 10: In legends we have the legend marks of second series we inserted too. To get rid of them simply click each legend twice with left mouse button and hit delete button.

highlight data point 12

Check out more tutorials on Excel charts:

  1. Highlight instances in Excel charts in different colors with shaded bars in background
  2. Conditionally Format Chart’s Background in Excel – How To
  3. Making Sales Dashboard using Excel Slicers – How To
  4. ABC Inventory Analysis using Excel Charts

LEAVE A REPLY