+ Reply to Thread
Results 1 to 11 of 11

Line Graph With 2 Sets Of Data and Slicer

  1. #1
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    284

    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 Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    284

    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
    7,247

    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 Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    284

    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
    7,247

    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
    389

    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 Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    284

    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
    389

    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
    389

    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
    389

    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 Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    284

    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)

Similar Threads

  1. Combining a bar graph and line graph with 2 sets of data
    By lynzlou in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-11-2014, 12:49 AM
  2. Column/line graph combination with multiple data sets
    By LukeD in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-24-2013, 05:44 AM
  3. Excel 2007 : How do I put 4 data sets onto one line style graph?
    By portisheadradio in forum Excel General
    Replies: 2
    Last Post: 12-27-2011, 12:12 PM
  4. Line Graph: multiple lines from multiple data sets?
    By Guerodemonio in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-28-2011, 06:15 AM
  5. One-line graph, multiple data sets
    By wani0402 in forum Excel General
    Replies: 3
    Last Post: 11-30-2010, 04:43 PM
  6. Replies: 1
    Last Post: 12-16-2008, 08:44 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