• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

Add markers to chart from data series

Hello,

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.

Thanks,
CC
Chart-Example.xlsx
0
CC10
Asked:
CC10
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
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?
kfalandays
0
 
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.

Thanks,
CC
0
 
Karen FalandaysTraining SpecialistCommented:
Take a look at the attached file, with conditional formatting:>icon sets. Would this solve the issue?
kfalandays
Copy-of-Chart-Example.xlsx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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.
0
 
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
0
 
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.

Regards,
-Glenn
EE-Chart-Example2.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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
ArrowsBehindLine.xlsx
0
 
CC10Author Commented:
Both solutions worked but Glenn's was better for my needs.  Thanks.
0
 
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).
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now