Hi there.
I have a two-dimensional table that I'm trying to turn into a line chart. The issue I am running into is that I have more than 255 Data Series, which is an Excel limitation.
In column A, there are 600+ Billcode values. These are my data series.
Across the top of my table, in Row 1, are Month1 through Month93. These should be the X axis of my chart.
Cells B2:CP600 contain monthly invoice values.
Additionally, I have two columns with additional data meant to help break this data up. Column CQ ("A to Z") contains values that break up my billcodes into three chunks of less than 255 rows (A-G; H-O; P-Z). I also have column CR ("Project Length") that contains the relative duration of the Billcodes in each row (0 to 1 years; 1 to 2 years; etc.). This is another way to break up my data into chunks of less than 255 rows.
With that, I've tried several approaches. I've found that PivotCharts are subject to the same limitations, so that's not an option. I've converted my data to a Table and tried applying filters, but not surprisingly, it appears Excel is recognizing the entire contents and not just the filtered selection.
I've lately been trying to create a dynamic named range and use that in conjunction with the autofilter as a basis for the chart, but have been struggling with this as well.
Any suggestions on an elegant, interactive way to break this data set up into bite-sized chunks that Excel can handle? Ideally, I'll be able to have a polished product that will allow the user to use an AutoFilter, Slicers, or even a ComboBox form control to see this data broken up into parts.
Thanks!
I have a two-dimensional table that I'm trying to turn into a line chart. The issue I am running into is that I have more than 255 Data Series, which is an Excel limitation.
In column A, there are 600+ Billcode values. These are my data series.
Across the top of my table, in Row 1, are Month1 through Month93. These should be the X axis of my chart.
Cells B2:CP600 contain monthly invoice values.
Additionally, I have two columns with additional data meant to help break this data up. Column CQ ("A to Z") contains values that break up my billcodes into three chunks of less than 255 rows (A-G; H-O; P-Z). I also have column CR ("Project Length") that contains the relative duration of the Billcodes in each row (0 to 1 years; 1 to 2 years; etc.). This is another way to break up my data into chunks of less than 255 rows.
With that, I've tried several approaches. I've found that PivotCharts are subject to the same limitations, so that's not an option. I've converted my data to a Table and tried applying filters, but not surprisingly, it appears Excel is recognizing the entire contents and not just the filtered selection.
I've lately been trying to create a dynamic named range and use that in conjunction with the autofilter as a basis for the chart, but have been struggling with this as well.
Any suggestions on an elegant, interactive way to break this data set up into bite-sized chunks that Excel can handle? Ideally, I'll be able to have a polished product that will allow the user to use an AutoFilter, Slicers, or even a ComboBox form control to see this data broken up into parts.
Thanks!
