Macro And Graphs to stop Manual Data Label Intervention

[cartica]

Hi,

I am attaching my excel sheet. One tab is the graphs, and the other tab is the data. I am trying to figure out a Macro, or some other way so that I do not need to spend 30 minutes a day manually moving around the data labels to be presentable. The data changes daily, so it moves around in the graphs section. Besides that, in the graph labeled top 10 traded securities, how do I get rid of the zeros when I do not purchase or sell a stock. Any help would be greatly appreciated.

Thank you

[MrShorty]

This looks like basically the same question asked previously: http://www.excelforum.com/excel-char...-labeling.html

With this iteration, I think my suggestions from the previous discussion have not changed -- evaluate the need for these data labels in the chart. If they are necessary, I think I would use some variation of a column-XYscatter combination chart. Apply the data labels to the XY scatter data series instead of the column data series.

Then (the hard part): figuring out a reliable algorithm for the X and Y values of the XY series so that the data labels will place themselves reliably. I think I would set up a column XY combination chart with the desired data labels added. Manually pick X and Y values for each data label until you are satisfied. Then copy in another data set and see how you end up moving the data labels. Continue doing that manually until you have some sense of the patterns you use for positioning the data labels. Then we can figure out how to formalize those patterns into worksheet formulas that will roughly mimic what you do manually.

Since you gave no feedback on the previous thread, I do not know if you were able to create the column/XY combination chart. If you were not this tutorial talks about combining scatter and column charts: http://peltiertech.com/Excel/Charts/...olAndLine.html The menu commands suggest that it is an older one, written for 2003 and earlier, but the basic commands are still present in newer versions of Excel, so I would expect a similar sequence of steps.

[cartica]

unfortunately, I need the graphs in it's current format. I believe it is the best layout of the story I am trying to tell. I am not sure the bar and xy will do it justice.

[MrShorty]

Can you perhaps explain what you are trying to tell with these charts and why the XY will be detrimental to that narrative? I don't see why having an XY series associated with the data labels (formatted as "invisible" so that we don't see the actual XY series markers/lines, all we see from the XY series are the data labels) would detract from the charts that you have.

[cartica]

Would you be able to provide 1 example of what you propose next to my first graph, so I can see? Thank you

[MrShorty]

I left the markers visible, so you can see where they are, but it would be easy enough to Format Data Series -> Marker Options -> None to completely hide the markers for the XY series. I have the XY series calculated as random numbers now, so you can see them jump around the chart.

I don't see why this approach cannot be made to work, though it will take some development to get it right.

[cartica]

MrShorty- So if you look at the first chart here are already 3 series, can you add a 4th? For the second chart that you added a 3rd series, can you explain how this will work any better?

[MrShorty]

I'm not sure I am understanding these latest questions.

So if you look at the first chart here are already 3 series, can you add a 4th?

Yes? According to Excel's specifications and limits, 2010 can have up to 255 data series in a chart, so 4 should be no difficulty. Or is there more to your question than just, "can a 4th series be added"?

For the second chart that you added a 3rd series, can you explain how this will work any better?

If I understand correctly, the heart of your question is about positioning data labels. Excel's built in procedure (drag individual data labels around with the mouse) is tedious and not easily automated, I am trying to show that if you use an additional data series as an XY series, that you can easily position data labels anywhere in the chart by choosing appropriate values for the X and Y values of this additional data series. Obviously, random numbers for these coordinates are not useful, but it should show that you can easily position labels anywhere in the chart if you can figure out exactly how to calculate the desired location of a given label.