+ Reply to Thread
Results 1 to 5 of 5

Excel Chart: Display Multiple Series Over One X Axis?

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Excel Chart: Display Multiple Series Over One X Axis?

    Hello Excel Jedi Masters,

    Suppose I have the following data (Iím showing it twice, sorted in two different ways):

    Data.jpg

    I ultimately need a graph that looks like this:

    Chart.jpg

    Obviously, this is a scatter graph with lines over, I guess, multiple series? Iíve been Googling different Excel tutorials and canít stumble on the correct terminology. For the purposes of this post, Iím not concerned with the format of the axis or the legend, only how to get each student represented by their own uniquely-colored line. Any suggestions? Or can someone identify the buzzwords I need to look up how to create this chart?

    FYI, I am working in Excel 2019 for Windows. Thank you!

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

    Re: Excel Chart: Display Multiple Series Over One X Axis?

    A lot of charting is arranging the data in the spreadsheet. The arrangement you show (a flat list) is good for database work, but does not work very well for charting. The goal is to rearrange the data so that you get something like:
    Please Login or Register  to view this content.
    The first two methods for rearranging the data that come to mind:

    1) A pivot table (https://www.excel-easy.com/data-****...ot-tables.html ) where you have day as row label, student as column label, and sum of score as the pivot values. A line chart like what your picture shows would be straightforward from the pivot table data, but a scatter chart is not an allowed pivot chart type. If you decide you need a scatter chart instead of a line chart, then you can follow instructions here: https://peltiertech.com/regular-char...-pivot-tables/

    2) A "conditional formatting" technique like this: https://peltiertech.com/conditional-...-excel-charts/ where you add helper columns to the right of the data with each name at the top, then use an IF() formula to make a copy of the data under the appropriate student name. Use these helper columns in your line/scatter chart.

    Which approach do you like? Where do you get stuck in implementation?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Excel Chart: Display Multiple Series Over One X Axis?

    Wow, thanks MrShorty,

    I've played around with conditional formatting in the past, and I don't think that will be feasible. In this post, I've presented a toy data set; my actual data set is much larger and going the cond. format route could be balky.

    I'd like to try my hand at pivot tables; this seems a good opportunity to learn. That said, I'm clearly not getting the gist of this concept. Please check out the below image, which is my clunky attempt to implement your advice. Can you see where I'm going wrong?

    Thank you!

    Attachment 667707

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

    Re: Excel Chart: Display Multiple Series Over One X Axis?

    I'm not quite sure how one would measure "balkiness", so I will have to take your word for it that having a column for each name is "balky".

    Pivot tables are useful tools, and it is valuable to be able to use them. Your picture failed to attach, so I cannot see what you are trying to do. The process I use in my copy of Excel (this help file https://support.office.com/en-us/art...9-F99134456576 suggests that your newer version is substantially similar):

    1) Select the source data.
    2) Insert -> Pivot Table.
    3) Check that Excel is using the correct source data and choose an output location. Ok to move to the next step.
    4) In the Pivot Fields pane, drag the "day" field to the row labels box, drag the "student" field to the column labels box, and drag "score" to the values field.
    5) Select the pivot table and insert a line chart.

    Then look it over and see how close we are to what you want.

  5. #5
    Registered User
    Join Date
    01-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Excel Chart: Display Multiple Series Over One X Axis?

    Yes! YES!!! MrShorty, you've saved my presentation. Your instructions were very clear and easy to follow and now I'm looking at a beautiful line chart that is exactly what I need. I initially failed to understand how my original chart was transformed into a pivot chart, and that was throwing me off.

    This is perfect, thank you. If you're ever in NJ, look me up, I owe you a beer...

    Many thanks!

+ 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. Replies: 5
    Last Post: 01-07-2017, 03:17 PM
  2. Replies: 1
    Last Post: 06-18-2015, 02:01 PM
  3. Excel chart Y axis v's series
    By Rob K in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2015, 01:01 PM
  4. Replies: 3
    Last Post: 04-23-2015, 11:21 AM
  5. Chart front-to-back series display if series are different chart types
    By Exconomist in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-06-2012, 06:49 AM
  6. [SOLVED] Can't link category (X) axis labels to multiple series in column chart, arrgh
    By danob in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-05-2012, 10:03 AM
  7. Replies: 1
    Last Post: 05-07-2012, 11:53 AM

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