In this tutorial we will discuss how to dynamically and automatically change the markers in a chart to highlight the values based on the series selected in a Data Validation drop down list.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here we have a list of sales for years from ’01 to ’10. In cell B1 I’ve selected from the Data Validation drop down list YR06, and notice that in the chart on the left, the markers for the years through ’06 are highlighted in red, and in the right chart, the value for year ’06 is highlighted in red. Also note that the Chart Title for both indicate the year ’06”
If I change the selection to ’03, the red markers automatically change, as does the reference in the title:
The key to this process is the data in the hidden columns C & D. These contain formulas that determine the data to be displayed in the charts:
The formula in cell C4 is:
And the formula in cell D4 is:
The only difference is when comparing the sales value to the year selected in B1, column C uses “less than or equal to” while column D is just “equal to”.
To create the left chart, I start with selecting the data in A3:B13, then choosing a line chart with markers:
I then right-click on the line and choose Format Data Series:
I’ll choose No Line for the Line option:
Then format the Markers however I want them to look:
And this is the result:
Now I’ll select the chart, go to the Design tab and click on Select Data:
I’ll then choose the “Add” option and enter the title and data range accordingly:
Notice how the markers for that data are displayed on the chart:
Now, all I need to do is format those markers to be large enough to cover the other markers, and in a color that I want them to be:
The only difference between this chart and the other is to add the Value3 data in column D rather than the Value2 data in column C.
Tho only other aspect is to add the chart title and make it dynamic. First we need to create a formula somewhere on the document with the following formula:
=”Sales Through “&B1
Make sure you put it in a location that will not be disturbed.
Then add a chart title to your chart:
Next, click into the chart title on your chart, then go to the Formula Bar and type in an equal sign and the location where you put the previous formula:
When you hit Enter, the chart title will display what ever is in the cell with your formula, and will dynamically change as you change the selected year:
Take a few minutes to watch the video for more details on this process!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!