How to highlight data points in an Excel chart using Form Controls

It is sometimes not always easy to trace which data point relates to which series in a chart, especially when it comes to a line chart.  To overcome this problem, we can dynamically highlight an individual data point. Let’s get started.

Download the workbook to practice.

  • Copy the data and paste it in a different area in your workbook. In our example, we are copying the range Sheet1!A1:G5 to Sheet 2!A1:G5

  • Select the data on Sheet1 and insert a Line Chart With Markers

  • Copy the data pasted on Sheet 2 and paste it directly on top of the chart. Excel inserts another data series directly over the existing one.

 

  •  Select the Developer Tab > Insert > and select the Option Button (Form Control). Click anywhere in the worksheet to insert the button.

 

 

  • Right click on the button and select Format Control.

 

  • In the Cell Link box enter the cell you want to connect to this button. In our example I have entered M2. Select OK.

 

 

  • Repeat the above process until you have the desired number of buttons and link them all to the same cell. In our example we will need 4 buttons for each year.  Edit the button label to the correct annotation.

  • Ensure the buttons are working correctly by selecting each one and checking that the value in M2 changes with each selection.

 

 

  • Enter a 1 in N2,
  • 2 in N3, etc until you have the desired number of entries.

 

  • In cell O2 enter the formula =$M$2=N2 and drag the fill handle down to the last cell

 

 

  • On Sheet 2, enter the following formula in cell B2
    =IF(Sheet1!$O2=TRUE,Sheet1!B2,NA())

 

  • Drag the fill handle down to cell B5 and then again across all the columns to column G.

 

  • Click the 2016 button and right click on the relevant data series in the chart. Click Format data series.

 

 

  • Make the following changes to the line and marker:
    • Fill : No line

     

    • Marker : Size 15

    • Fill : No Fill
    • Border : Colour = Red
    • Border : Width = 1.5pt
    • Dash Type : Dash

  • Repeat the above process on the remaining data series.

  • Edit the chart elements by clicking them and either deleting them or editing them e.g. chart title, gridlines etc.

    • As the years are represented by the Option buttons, the legend can be deleted.

      Note: if you do not see the Developer Tab, follow the steps below:

      To display the DEVELOPER tab, click on the File menu and then select Options from the drop-down menu.

      When the Excel Options window appears, click on the Customize Ribbon option on the left. Click on the Developer checkbox under the list of Main Tabs on the right. Then click on the OK button.

      Now you should see the DEVELOPER tab in the toolbar at the top of the screen.

      Anonymous