Stacked Line graph line colors do not match legend

Question

I made an interactive chart that shows different data, depending on which ActiveX checkboxes are checked. I created a few tables that would show different data by using a few nested IF statements like:

=IF('Sheet1!$O$25;SUM(AV$2:AV8)/B8;IF('Sheet1'!$O$22;SUM(AK8;AL8;AT8;AU8);IF('Sheet1'!$P$22;AM8;IF('Sheet1'!$P$25;AQ8;NA()))))

Column O in Sheet1 gets the true or false values from the checkboxes. I made it possible to show three different values in the graph.

Now if I try to change the chart type from a normal line to a stacked line and only check the first and third checkboxes, the colors of the actual graphline and the legend are not equal. I have the linecolor setting to automatic. It looks like the first line in the graph is always blue and the second is always green and the third always red.

So what happens is, if I say "show only line 1 and 3", the legend leaves a gap for the second data, saying data3 (PM) is red, but the actual line in the graph is green. I double checked and the green line is indeed the "PM" datapoints.

Am I doing something wrong, is it a bug in Excel or might this actually be how the stacked line graph was designed?

EDIT:

You can simply duplicate this issue:

• in column A paste random values, in my example below I used 3
• in column B paste random values, in my example below I used 2
• in column C paste random values, in my example below I used 1

Now make a stacked graph. Select column B and clear the values.

As you can see, the legend shows green and blue as containing data, yet the lines are green and red!

Answer 1

Ok, so I figured out what is going on here. As this is a stacked line graph, any line that is not the last line, but has all values at zero or NA() will be ON TOP OF line 3. By actively suppressing the no-value line visibility in VBA this can be fixed. I haven't found any other solution to get around this behavior.

Answer 2

In case somebody gets ever more interested - since I did not find any better place nor did I find an answer throughout the internet:

The same happend to me with manual graph settings (no VBA code). It turned out that every element of the legend can be formated independently of the actual series (*). I formated the corresponding legend element symbol (line color) to the (custom) line color I had chosen for my series before (and which did not show up correctly in the legend).

When I changed the color of the series later, the change magically propagated to the legend properly since then.

My hypothesis states that sometimes the legend elemnt keeps, e.g., its "line style" "automatic" (do not have proper English localization here) although the series formating got customized. Once the element is made "not-automatic" manually, it starts follows the (future) changes in the series formatting properly (?)

(*) My another hypothesis is that MS try their best to allow cheating your (their own??) CEO with mingled data graphs (or I cannot find any better reason for allowing independent legend formatting... :))

Answer 3

File > Options > Advanced > Chart >

• deselect 'Properties follow chart data point for current workbook'

• deselect 'Properties follow chart data point for all new workbooks'