A chart gets difficult to read if it has a lot of data plotted on it. While it is a good practice to plot only the relevant data, there are situations where you need to show a lot of data points on a single chart. If you are stuck in such a situation, it is a good idea to have a dynamic chart that highlights the selected series so that it is easier to read and compare. Something as shown below:
In the above chart, when you click on the button, the selected year’s series gets highlighted with red marker outline and the data labels. Follow Along.. Download the Chart
Highlight Data Points in Excel Line Chart
Here is how you can create this type of charts: If you have followed all the above steps, you would have something as shown below: Now to make the buttons functional, we will use a simple VBA code.
The VBA code
We will use VBA code to do 2 things:
Change the year value in cell F3 when the shape is clicked, and Change the color of the selected shape
Simply copy the following code in the VB Editor. To copy this code:
Press Alt + F11. It will open the VBE Editor. Go to Insert and click on Module. This will insert a module. Double click on module icon, and paste the code on the code area on the right.
Assign Macros to Buttons
Once you have the VBA code in place, you need to assign macros to the buttons/shapes. To do this:
Right-click on the shape and select Assign Macro. In the Assign Macro dialog box, select the macro and click OK.
Note: Since this workbook contains a macro, save it as a .xlsm or .xls format file. Now your dynamic chart is ready. With a single click, you can now highlight data points for the selected series. Download the file More on Dynamic Excel Charting Tutorials:
Dynamic Pareto Chart in Excel. Dynamic Target Line in Excel Bar Charts. Spot the Data Point in Excel Scatter Chart. Dynamic Chart with Series Selection Check Box. How to Create Dynamic Chart Titles in Excel.
Note: My data is like:- Brands in rows and various parameters in column headings. Above column headings I have year for particular set of headings. I need something like you showed here in this article. Please help.
