When I Hide Columns, My Bar Column colors change automatically...

Hi Guys

Here is my first post so hope to get replies

i have a matrix with the row being revenue, and columns being multiple scenarios for different month

i.e.

Jan Bud Jan Frcst Jan Act Feb Bud Feb Frcst Feb Act.......
Revenue

i wish to create a chart showing different color for each scenario i.e. red for Jan Bud, Feb Bud, Mar Bud.... Blue for Jan Frcst, Feb Frcst....
i don't want to select multiple series as this will take a lot of time (12month*4scenarios) + i have around 30 charts to build

I have found a way online to select info as one series of data; i then manually colored the bars as needed.
Until here everything is working fine (unless you have a fast and easy method to use)

Now here is the challenge. when i hide one of the scenario, the corresponding chart gets hidden as expected HOWEVER the bar color change again... so Bud becomes both Red & Blue.....

Can anyone suggest a solution (that is if i was clear)!
untitled.JPG


Thanks
MJS

Hi npamcpp

Thank you that was a really quick reply!
here you go my workbook. yes the setup is different as this is system generated

Thanks
Mansour

as a quick note the series color was standard blue and was manually colored for all bars.
i believe this is why when i hide one of the scenario (see control room) the colors get crazy......

is there an easy and fast way to setup the chart from scratch but still get the same results?

the master sheet include all the relevant data: revenue per month-scenario. to filter I hide the columns i do not wish to see; the chart then automatically hides the corresponding bars.
when i hide the columns "budget" for example, all the budget bars (12) gets hidden which is fine; however the colors change i.e. if budget is setup as red, i expect not see any red bar on the graph.
apologies if i am not being very clear... hope you get the chance to look at the uploaded file (forecast model) - maybe there is an easier way of formatting it?

Thanks again for your time
MJS

Thanks Andy... but
How do i do that !?

Thanks Andy ... however won;t be able to use this as this is a system generated report can't toggle all data...
hope you have an other method.
again one of the solution is to setup all as one series. and then manually color the bars. but the question remains why are the colors changing when i hide the columns

Hi Andy

Thanks again... to be honest i don't know how to do that...