Results 1 to 2 of 2

Thread: Multi-series line chart w/ slicer

  1. #1
    Neophyte CaptNapalm's Avatar
    Join Date
    Apr 2021
    Excel Version
    Excel 365

    Multi-series line chart w/ slicer

    Register for a FREE account, and/
    or Log in to avoid these ads!


    Long-time lurker, first time poster. I'm having a heck of a time graphing some data. The end result I want is a sliceable multi-series line or XY scatter plot chart of a table of time-based data with dates on the X-axis.

    The challenges are:
    - The table is dynamic, rows and columns grow and shrink, as the table(s) are power query output
    - The data is date-based, but not all dates have values, and the dates are not evenly spaced
    - I wish there to be a slicer (instead of just using the chart filter to filter series)

    Attempt 1 (example 'table1'):
    - Three column tabular data layout. Works great using XY scatter plot, except you can't differentiate between series visually, only via slicer. So not that useful for series comparison, which is kind of the whole point.

    Attempt 2 (example 'table2_pivot1'):
    - Pivot of tabular data with dates as the first column, series as columns across the top. Works ok, but not sliceable (to my knowledge). And also, line chart has a hard time picking up series name from table headers. But date axis is properly picking up dates and lines are proportional across time.

    Attempt 3 (example 'table3_pivot2'):
    - Pivot of tabular data with dates as table headers and series as rows. Best graph so far, and sliceable. BUT Excel won't recognize date data (as dates) in table headers, so the line graphs are not proportional to the dates. I've tried demoting headers to a series row with no good result. And also, you do have to trick excel into accepting more than 255 columns of data for the graph.

    So what to do? Conceptually, this graph seems super intuitive, so I think there should be a way to do this. But I'm running out of ideas and feel like I'm missing something.

    Any help appreciated. Would like to avoid code if possible. Example data attached.
    Attached Files Attached Files

  2. #2
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Excel Version

    Not sure if this is even close to what you had in mind. Let me know! And happy trading!
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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