+ Reply to Thread
Results 1 to 8 of 8

Macro And Graphs to stop Manual Data Label Intervention

  1. #1
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Macro And Graphs to stop Manual Data Label Intervention

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,759

    Re: Macro And Graphs to stop Manual Data Label Intervention

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Macro And Graphs to stop Manual Data Label Intervention

    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.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,759

    Re: Macro And Graphs to stop Manual Data Label Intervention

    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.

  5. #5
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Macro And Graphs to stop Manual Data Label Intervention

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

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,759

    Re: Macro And Graphs to stop Manual Data Label Intervention

    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.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Macro And Graphs to stop Manual Data Label Intervention

    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?

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,759

    Re: Macro And Graphs to stop Manual Data Label Intervention

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] TAB keys instead of manual intervention to move between VBA controls
    By Mangai Venkata in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2016, 01:21 PM
  2. Looping Macro to Re-label X Axis on Graphs
    By kanect in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2015, 06:15 PM
  3. Replies: 2
    Last Post: 11-03-2014, 12:06 PM
  4. How to automate the quarterly manual updating of over 100 graphs?
    By Mirisage in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-17-2014, 11:15 AM
  5. vb code to select all files from a folder without manual intervention
    By ravivaliya in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-08-2009, 04:12 AM
  6. Answer Excel 'yes/no' question in Macro without user intervention?
    By dingman4 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2007, 01:35 PM
  7. Automatic updation of workbook without any manual intervention
    By engrzubair in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-05-2007, 10:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1