+ Reply to Thread
Results 1 to 11 of 11

Line Graph With 2 Sets Of Data and Slicer

  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2010
    Posts
    207

    Line Graph With 2 Sets Of Data and Slicer

    I'm curious if someone can make a graph for me. It doesn't have to be anything fancy. I'm just trying to learn/see how to convert my data into the proper table formats in order to present it in the graph like I want it. I've attached my example spreadsheet that should be self explanatory. I won't even need any explanations... I just need to see it done so I'll know.

    Your help is much appreciated!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2010
    Posts
    207

    Re: Line Graph With 2 Sets Of Data and Slicer

    Come on ExcelForum gurus... I know you can solve this simple one I either come up with one graph split into 2 sections or one graph that looks like a nightmare

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,680

    Re: Line Graph With 2 Sets Of Data and Slicer

    May be try defined names Ctrl+F3
    and such definitions for period7vals
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and period8vals
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    which could be used as a source for your series (and consequtively - lines).

    O3 and O5 are cells connected to CheckBox and ScrollButton
    Attached Files Attached Files
    Best Regards,

    Kaper

  4. #4
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2010
    Posts
    207

    Re: Line Graph With 2 Sets Of Data and Slicer

    Quote Originally Posted by Kaper View Post
    May be try defined names....
    This is super close but without the slicer, it would be difficult for people to navigate it. The poor soul who is at the 291 location, for example, would have to click for quite a while to get to the desired data for their facility. I like what you've done, though. Is there any way to adjust it so that a slicer could be utilized. I tried adding one myself but it wipes out the graph.

    One small detail, I notice the graph doesn't actually display MON, TUE, WED, etc... it's an overlay. Is there a reason why those categories can't be shown?

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,680

    Re: Line Graph With 2 Sets Of Data and Slicer

    Use line graph (and format line as smooth) instead of scatterplot to get proper MON-FRI labels on horizontal axis

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019
    Posts
    384

    Re: Line Graph With 2 Sets Of Data and Slicer

    My proposition using Power Query and Pivot Table. Maybe it will interest you?
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2010
    Posts
    207

    Re: Line Graph With 2 Sets Of Data and Slicer

    Quote Originally Posted by stasinek View Post
    My proposition using Power Query and Pivot Table. Maybe it will interest you?
    Very awesome! I've never heard of power query but this was what I was needing. I know how to change over to a line graph if I want but I actually like the columns better now that I see them that way. Thanks so much for doing this. Now, I get to spend some time dissecting how you applied the power query and created pivot table.

    Thanks so much!

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019
    Posts
    384

    Re: Line Graph With 2 Sets Of Data and Slicer

    The EX 2010 add-on should be installed separately (preferably from the official Microsoft website). Your tables have been converted to excel tables (I have cleared the table formatting system). Such tables serve as the source for the PQ query. After merging tables, the query was only in memory and served as an external source for the PivotTable. I think I helped a little in understanding my method of solving your task.

  9. #9
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019
    Posts
    384

    Re: Line Graph With 2 Sets Of Data and Slicer

    The Power Query add-on to EX 2010 should be installed separately (preferably from the official Microsoft website). Your tables have been converted to excel tables (I have cleared the table formatting system). Such tables serve as the source for the PQ query. After merging tables, the query was only in memory and served as an external source for the PivotTable. I think I helped a little in understanding my method of solving your task.

  10. #10
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019
    Posts
    384

    Re: Line Graph With 2 Sets Of Data and Slicer

    The Power Query add-on to EX 2010 should be installed separately (preferably from the official Microsoft website).
    Your tables have been converted to excel tables (I have cleared the table formatting system).
    Such tables serve as the source for the PQ query. After merging tables, the query was only in memory and served as an external source for the PivotTable.
    I think I helped a little in understanding my method of solving your task.

  11. #11
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2010
    Posts
    207

    Re: Line Graph With 2 Sets Of Data and Slicer

    Quote Originally Posted by stasinek View Post
    I think I helped a little in understanding my method of solving your task.
    Yes, you did... thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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