Pivot Chart Help

Hi All,

I want to create a panel pivot chart. I don't want to convert to a regular line or XY scatter plot, because I want the chart to be easy to maintain if my data changes (for example, if my data now adds a new region or new type, the pivot chart would update automatically without having to manually add a new series the way I would with a regular chart.)

I have a couple of constraints:
- I want to use a pivot chart because of the easy maintenance if the source data feeding the table changes.
- I want the lines to be disconnected for different categories of "cost" and "use"
- For each region, I want the line color to be the same, regardless of whether it is "cost" or "use". I want the ability to use slicers and change the graph based on a user being able to view specific regions of their choice, so I don't want to manually change any colors.
- Eventually I'll have 50+ regions, and 6 different categories (in addition to the cost and use categories).
- I tried adding a stagger field, to disconnect the lines, as discussed in Peltier's article titled Excel Panel Chart Example - Chart with Vertical Panels but this causes the lines for each region to be different colors for cost vs use. In addition, in my chart, since I'm going to have 50+ regions, I get the error message about the maximum number of series is 255 since this method creates more series, and I want to avoid this error message.
- I have come up with a workaround in the attached example, given the constraints I listed. This method has a "blank" year in order to separate out cost and use in my graph, and still allows it to be the same series so the color is the same and I don't reach that 255 max. However, this causes there to be a "blank" option in the year slicer and an extra space in the graph.

I'm wondering if anyone has a better option? Or knows a way to do this without having to include the blank year? If not, is there a way to lock in the "blank" year selection in the slicer so that I don't have to tell the user to always keep that selected in the slicer?

Also if anyone knows of a way to keep the line in between cost and use but delete the lines between each year, that would be helpful.

I have attached my example workbook with my workaround method. Thanks in advance!

Hi, there

The idea of stagger field is a cute tweak It does the trick; as to the slicer, you can disable it by tick the option in the settings menu for slicer (right click slicer - Slicer Settings)
Slicer.png

Thanks for your response. The problem with "hiding items w/ no data" is that if the user just clicks on "2020" for example, then the data points between cost and use connect with a line. I want to keep the lines disconnected between cost and use. That would require for the "blank year" to always be selected in the slicer. Ideally, I'd have it so that the blank year is always selected, and then I'd hide items w/ no data so the user doesn't see that selection in the slicer or know that they have to keep it selected. Is there a way to lock in that selection in the slicer so it is always clicked?