Add markers to chart from data series


I have a table with a basic line chart which I have included as an attachment. Is it possible to mark certain data points in the table that will then automatically show up in the chart as markers?
e.g. If I colour the cell B10 green, then a marker in the form of a green Up Arrow should appear at the corresponding data point in the chart. If I colour the cell B20 red, then a marker in the form of a red Down Arrow should appear at the corresponding data point in the chart.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karen FalandaysTraining SpecialistCommented:
Hi CC10,
There is not an easy way to do that without programming, but why not represent the data using your list and conditional formatting? That way you can show the arrows as needed OR just draw the markers manually. Is there a condition that the numbers must meet or is it random?
CC10Author Commented:
Hi there,

I do not quite understand your first comment but I will answer the second first. I have tried to draw the markers manually but it is quite tedious as I have to double click the data point on the chart and then insert the marker. There are no up and down arrows in the built-in marker options and I would have to create a picture and then insert it. This is not the optimal solution.

The condition in the list is actually a buy or sell rate. I thought it would be easier to mark the cell in the data table with a colour, green for buy and red for sell. This would then show up on the chart as a up or down arrow at the corresponding data point,
I appreciate that this would need programming. I have a macro that colours a bar in a column chart on a particular condition but here I need to have actual up and down arrows to show the buy and sell rates visually in the chart.

Karen FalandaysTraining SpecialistCommented:
Take a look at the attached file, with conditional formatting:>icon sets. Would this solve the issue?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CC10Author Commented:
Hi there,

unfortunately not as the icons only appear on the data table. i.e. columnB

Maybe I can explain it better.

Just take one cell, B 20 = 1.3591

If I colour this cell Green, then the datapoint on the chart that corresponds with cell B20 will have a marker with an Up arrow coloured Green.

If I take the cell B 40 = 1.3578   and colour it red, then the corresponding data point in the chart would show a marker with a red coloured down arrow.

If all the other cells in column B have no colour, then there would be no markers in the chart.
CC10Author Commented:
This macro colours various bars in a bar chart to match the corresponding colour of a cell in the data table. This does not seem to work for a line chart.

I need to insert a marker at the data point with matching colour.

Sub FormatChartColoursEURUSD()
    Dim lngIndex As Long, rngData As Range, pt As Point, ser As Series, rngCell As Range
    Set rngData = Worksheets("EURUSD").Range("S2:S978")
    Set ser = Worksheets("EURUSD").ChartObjects("chart 11").Chart.SeriesCollection(1)
    With ser
        .Interior.ColorIndex = 47
        For lngIndex = 1 To .Points.Count
            Set pt = .Points(lngIndex)
            Set rngCell = rngData.Cells(lngIndex)
            If rngCell.Interior.ColorIndex <> xlColorIndexNone Then
                pt.Interior.ColorIndex = rngCell.Interior.ColorIndex
            End If
        Next lngIndex
    End With
End Sub
Glenn RayExcel VBA DeveloperCommented:
It may be that a programmatic approach will be needed, but take a look at this modified example of your workbook and see if it might be the basis of a solution.

I've added three columns:
1) "Indicator" - enter either "Up" or "Down" here to indicate a flagged point. (using data validation to control)
2) "UpPoints" - a new data series for "Up" flagged values
3) "DownPoints" - a new data series for "Down" flagged values

There is conditional formatting applied to the values in column B that will turn them green (Up) or red (Down) as appropriate.

I added the two new data series to your chart - points only, no lines - and colored them green and red accordingly.

This set up will add colored markers on the flagged points.  Anything more elaborate such as up/down arrows above or below the line will require significant VBA coding.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I've done something like this a while back using a stacked column chart behind the line with the top series of the column stack using a picture fill. The trigger for the arrows is not cell formatting but a formula that calculates if an upwards or downwards trend presents. You can change the formula to present your own logic or enter the data manually.

You could use conditional formatting to color the cells based on the value in the two trend columns.

In this particular example the user wanted the arrows to be behind the line. If you change the formula in column C  to return a higher number you can get the arrows to show above the line.

cheers, teylyn
CC10Author Commented:
Both solutions worked but Glenn's was better for my needs.  Thanks.
Glenn RayExcel VBA DeveloperCommented:
I like teylyn's solution, too (very creative use of adjustment columns to set the arrows).  But this data set has so many points that the arrows would be tiny, if viewable at all (due to number of columns needed).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.