Pivot chart joining multiple series

Exceloway

New Member
Joined
May 9, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

i think I already know the answer to this but just want to check I'm not missing something obvious. I have a set of data that has had a chart somewhat "manually created" from. I need to recreate the graph but with the ability to cut the same data in multiple ways, and a graph displayed for each (so sectors/ sites/ customers). Ideally in my mind this wold involve the same pivot table produced multiple times then a couple of slicers linked to all the tables so you look at the same date ranges etc across all the graphs.

The graph is a line graph and each year the colour has been set (I'm guessing manually) to make it easier on the eye. In Excel terms, we would define these as series, but to do that in a pivot table I've discovered that where one year ends and the new starts it cannot produce a connecting line between the two series. I have tried all combinations I could think possible in the "hidden cells" window. Am I right in thinking there is no setting that will magically solve this?

If not, is there anything straight forward I might be able to do to force the joining line? I did read a suggestion elsewhere about 1 minus the other but I can't see how that will work in this context.

My current method is that I have just created a table instead and then replicated the table multiple times, pointed the graphs to each and built the filtering option with VBA. But it takes nearly 3 minutes to run which I'm not happy with. This is the core of the code. You will see I tried in the final stage to create the range it picks up the dates first with a view to holding them in the code to use rather than keep going back to the cells each time it needed to do the filtering. I'm not sure if it actually does that - it shaved off another 10 seconds that was all.
At any rate, the question is, if I can't fix the pivot chart issue and continue down the current path, is there anything else I can do to make this code slicker (pref 60 seconds or less). For extra info, it's looping through 35 sheets each with one table on at the moment.

1677178139939.png

Thanks for advice as always.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,212,927
Messages
6,110,708
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top