• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2016 charts - all chart elements disappear when I amend the secondary axis - help!

Johnny C

New Member
We got upgraded from Xl2013 32 bit to 2016 64bit and I have a big problem with charts created in the old version

I have a model with 30 charts or so. They are all formatted to a corporate standard which is messy, non-standard colours, marker meanings etc. I can rebuild the charts from scratch but it takes ½-1hr each and I have a number of models with about 30 in each.

They aren't complex technically, a number of series as Stacked Area, a number of lines to highlight subtotals at each new area, and a blue metric line against which they are to be visually compared. The metric line needs to be on a secondary axis as it has a completely different scale and above the top of the stacked areas so it's clearly visible.

The charts were created last year using Excel 2013. Now we have 2016, and I need to update them with a new year of data.

I try and amend the upper bound of the secondary axis, all chart elements disappear.
When I try and change a series to primary that works, but when I set it back to secondary they all disappear.
When I try and delete the series to add it, if I press or right click delete, that series stays but all others disappear. That happens irrespective of primary or secondary.
So I managed to delete the series using the select chart data dialog box (I forgot that even existed), and add it back. But as soon as I try and make it secondary axis, everything disappears again.

Before: The blue line has to be on a secondary axis to appear above the rest. The blue line was originally on a secondary axis with an upper bound of 75 which needs setting to 85 so the line and labels fitted the chart.

63156

Changing secondary axis upper bound: New number entered in Maximum as below

63155



After: When I hit return
63157



What I can do to fix it?
I can change other aspects, for example amending gridlines is fine.

One other curious feature, when I tried to delete the series by selecting and hitting the Del key, it deleted all the series except that. That applied when I tried to delete any series on primary or secondary axis, the only way I can delete a series is by using the Select Data dialog box which is a pain.

it's not just one rogue chart, it's any with a secondary axis.
 

Attachments

  • Cht1.png
    Cht1.png
    23.5 KB · Views: 2
I'd recommend uploading sample where this occurs (using single chart as example is enough). I.E. the chart that was created in older version.

Modifying secondary axis can be tricky and there are number of ways it can go wrong.

Without looking at underlying data structure etc. Bit hard to pinpoint how to help.
 
I can't post the whole shebang as it has corporate data in. I tried deleting all other sheets and anonymising the data but weirdly the chart worked then.
I tried resetting the 'Properties follow chart data point' option in Advanced options (Jon Peltier suggestion) but that didn't work.

I did some VBA to reset the bounds and weirdly that worked

So I wrote a small userform to set the Axis bounds on both axes and stuck it on my QAT, it's quicker doing it that way anyway. Must be a bug as it happens on all old charts. New charts are fine, even if they're an identical chart to an old one that doesn't work properly.

I do wonder if it's related to the issue where you have a stacked area chart with custom colours which reset themselves if you manually change the =SERIES() formula. if you amend the series using VBA the series keeps the custom colours.
 
Solved. The chart had a picture-linked-paste grouped with it. When you amend the bounds it creates a spurious blank chart which is at the front of the other items in the group. I sent Jon Peltier a version and it did the same for him.
You can just select the blank chart and delete it. or send to back.
it's an excel 2016 bug, it happens on grouped charts created with 2016.
Bizarrely, the blank chart it creates has the same name as the grouped chart. If you delete the whole group and press ctrl-Z to undo, the chart appears correctly.
 
Hmm, odd. Never had that issue before.

At any rate, thanks for the update and sharing your solution.
 
Back
Top