How To Create A Chart With Dynamic Markers In Excel

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”

41916-1

If I change the selection to ’03, the red markers automatically change, as does the reference in the title:

41916-2

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:

41916-3

The formula in cell C4 is:

=IF($A4< =$B$1,B4,NA())

And the formula in cell D4 is:

=IF($A4=$B$1,B4,NA())

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:

41916-4

I then right-click on the line and choose Format Data Series:

41916-5

I’ll choose No Line for the Line option:

41916-6

Then format the Markers however I want them to look:

41916-7

And this is the result:

41916-8

Now I’ll select the chart, go to the Design tab and click on Select Data:

41916-9

I’ll then choose the “Add” option and enter the title and data range accordingly:

41916-10

Notice how the markers for that data are displayed on the chart:

41916-11

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:

41916-12

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:

41916-13

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:

41916-14

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:

41916-15

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!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

2 Responses to How To Create A Chart With Dynamic Markers In Excel

  1. A.Maurizio April 21, 2016 at 12:31 pm #

    Hello my name is Maurice, excuse me for my further request, but believe me, without your help priprio not know how to solve this problem.
    So: always using a chart positioned on an excel sheet I wanted to match each square (series) to a single cell, to create a perpetual calendar.
    Now everything works fine; except that for a fact, and it is this: In the calendar as you well know some numbers may not be apparent until certain conditions, which I solved by writing this “= O code (AA5 = DATE ( $ H $ 1; MONTH ($ AD $ 12) +1; 1)) and the game and done.
    Now I would like to achieve the same thing using the Chart; How can I do to make this happen! let me also just a practical example so that I can understand all the rest then I’ll do; Thanks Greetings from A.Maurizio

    Link Program : Link: https://app.box.com/s/lhqva3eji0xcf2nmk8lxyki88tt1mi5t

  2. Michael Rempel April 24, 2016 at 11:30 am #

    It took a while but I think I finally figured out what you are trying to do – you want to conditionally format the markers in your calendar to basically be blank if those days are not in the month selected. I don’t know how to do this, and I did some research and could not find anything to help. Sorry. Please let me know if you figure it out, I’d love to understand it. Could you possibly use an IF statement and the result if false be an NA() function?

Leave a Reply