CaptNapalm
New member
- Joined
- Apr 22, 2021
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel 365
Hi,
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.
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.