+ Reply to Thread
Results 1 to 16 of 16

Continuous line graph using multiple rows of data?

  1. #1
    Registered User
    Join Date
    08-25-2004
    Location
    Kent, England
    Posts
    14

    Continuous line graph using multiple rows of data?

    Really hoping you can help with this! I just cannot find a way round this:

    Data for three customers is shown in multiple rows, but each customer in the graph needs to be shown as a continuous line, is there any way I can do this?
    (I can't put the data into one continuous row)

    Example data:
    Week 1 Week 2 Week 3 Week 4
    Customer 1 100 100 100 100
    Customer 2 90 90 90 90
    Customer 3 80 80 80 80

    Week 5 Week 6 etc
    Customer 1 100 100
    Customer 2 90 90
    Customer 3 80 80

    Any help you can give gratefully received!

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,443

    Re: Continuous line graph using multiple rows of data?

    Hi,

    Create a named range (CTRL & F3) for the range that you want to graph, and then use that named range as the data for the graph. Please see the attached example. Use CTRL and F3 to view the named range manager.
    Attached Files Attached Files
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    08-25-2004
    Location
    Kent, England
    Posts
    14

    Re: Continuous line graph using multiple rows of data?

    Thanks for this example, I'm sorry but could you provide a little more detail? Although I have used a named range it displays them as 6 different series, rather than 3 continuous lines? When I tried it on the example you kindly did the graph seems to do the same thing (there was no detail in the graph when I opened it), and displays two series rather than one continuous line? I fear I'm misunderstanding what to do?

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,443

    Re: Continuous line graph using multiple rows of data?

    Hmm, that's odd. What version of excel are you using?

  5. #5
    Registered User
    Join Date
    08-25-2004
    Location
    Kent, England
    Posts
    14

    Re: Continuous line graph using multiple rows of data?

    2007 version

  6. #6
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,443

    Re: Continuous line graph using multiple rows of data?

    Very strange indeed! Anyhoo, stepwise here's the way I did it.

    CTRL & F3 to bring up the name manager

    Click New

    Enter "Customer_1" in the "name" dialogue

    Enter the location of the data pertaining to Customer 1 in the "refers to" dialogue. For example, =Sheet1!$A$1:$E$1,Sheet1!$A$4:$E$4

    note that continuous ranges are separated with a comma.

    Click OK, then Close

    With no data selected, choose Insert > Line Chart

    a blank chart should appear

    choose Chart Tools > Design > Select Data

    Clear the "Chart Data Range" dialogue

    Click Add

    Enter =Sheet1!Customer_1 in the "Series values" dialogue.

    Click OK

    Click OK

    Repeat for subsequent customers.

  7. #7
    Registered User
    Join Date
    08-25-2004
    Location
    Kent, England
    Posts
    14

    Re: Continuous line graph using multiple rows of data?

    Thank you, I think skipping the commas was causing me the problem. I have managed to make the line continuous, but the resulting graph is very strange in terms of values - although when I add data labels the values shown are correct, they have 'stacked on top of each other' on the graph rather than overlapping (which they would with the true data) - so are shown at incorrect values.
    Would it be easier if I sent you what I have been able to produce?
    Your help with this is truly appreciated so thank you!

  8. #8
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,443

    Re: Continuous line graph using multiple rows of data?

    If you could attach it to the thread it would be useful - take out any personal or sensitive data 1st though.

  9. #9
    Registered User
    Join Date
    08-25-2004
    Location
    Kent, England
    Posts
    14

    Re: Continuous line graph using multiple rows of data?

    Thanks, example attached of what I have managed to produce, as you will see I can't get the lines of the graph to line up correctly with axis.
    I'm sure it's something simple!
    Attached Files Attached Files

  10. #10
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,443

    Re: Continuous line graph using multiple rows of data?

    Right click the chart

    Select Change Chart Type

    Select the 1st line chart option (you currently have stacked line chart selected)
    Last edited by sweep; 04-28-2010 at 11:33 AM.

  11. #11
    Registered User
    Join Date
    08-25-2004
    Location
    Kent, England
    Posts
    14

    Re: Continuous line graph using multiple rows of data?

    Unfortunately, it moved the starting point of each line onto the axis, but didn't locate the lines correctly?

  12. #12
    Registered User
    Join Date
    08-25-2004
    Location
    Kent, England
    Posts
    14

    Re: Continuous line graph using multiple rows of data?

    Thank you! How embarrassing, what a silly mistake! All your help has been appreciated.

  13. #13
    Registered User
    Join Date
    10-28-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Re: Continuous line graph using multiple rows of data?

    First: Thanks, this definitely helps with the basics.

    In my scenario, I have about 50 rows. These rows change every week. Is there an easy way to do this?

    I have started with: Sheet1!$A$5:$G$5,Sheet1!$A$10:$G$10,Sheet1!$A$15:$G$15,Sheet1!$A$20:$G$20

    As you can see, this would be a huge string. How can it be condensed?

    Thank You

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Continuous line graph using multiple rows of data?

    Nich, welcome

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  15. #15
    Registered User
    Join Date
    10-28-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Re: Continuous line graph using multiple rows of data?

    Thank You, FDibbins - You are correct. I apologize, and will start a new thread.

  16. #16
    Registered User
    Join Date
    10-21-2014
    Location
    Albuquerque, NM
    MS-Off Ver
    Office 2007
    Posts
    1

    Re: Continuous line graph using multiple rows of data?

    I am trying to accomplish this in a macro enable worksheet. Should it matter that is has .xlsm? I cannot get this work for me. I keep getting an formula error when I click "OK" on the edit series box . Any help is appreciated. Thank you
    Last edited by ernrodri2; 02-09-2015 at 02:38 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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