SOLVED

Chart color shading not correctly distributing colors

Brass Contributor

In the attached screenshot you can see where I've tried to shade a chart and rather than continuous shades, it applies them haphazardly.

 

This seems to be an effect over time... where I delete and add new chart elements, and want it to reassign the colors from scratch, but it seems to have some memory of previous assignments.

 

As I constantly add and delete from this chart, I don't want to make the chart from scratch, and would rather have it forget whatever previous states, and assign colors continuously across the chart.

 

Ideas?

 

... using MacOS Excel version 16.61.1

Untitled 17.png

11 Replies
... it appears Excel keeps an internal/representation that is not the same as in the "Legend Entries" list in the "Select Source Data" dialog. Even when "walking" through the table entries, t "tabs" between entries in an order other than listed. Occasionally, it displays entries in an order not listed (pushing a central data point to the end).

... not sure why Microsoft isn't fixing bugs like this.

To explain this problem in a different way:

 

  1. In a chart, select the "change color" drop-down (specifically I choose one of the continuous shading options, but any is fine).  Many color options are listed, each shows a list of colors to be assigned to the chart.
  2. You would expect the colors in the list of colors selected in "change color" to be distributed in the same order as the "Select Data" pop-up, "Legend entries (Series)" list.  For example, if 10 colors are in the selected list, you would expect the first color assigned to the first charted element, the second color to the second charted element, etc... (looping at the end, if there are more charted elements than colors).

As time go on, as the "Legend..." list is added to and deleted from, this is no longer the case.  Especially when a continuous shading is selected, and you get a hap-hazard assignment of colors when compared to the listed series.  Not quite hap-hazard: it looks to assign the first element to some nth color (not the first listed color), and cycle through the colors from there.

 

I see this in other ways too: I'd also expect "option+up/down" arrow to traverse elements in the series in the same order as listed in the "Legend..." list.  That order is lost too.

 

I also see an occasional error that is very similar: while the chart elements are shown in the chart properly (in the order listed in the "Legend..." list), a data table at the bottom will sometimes place an item in the middle of the chart at the end of the data table.  This is fairly rare (once a month).

Is there any way to get support from Microsoft on this?
Or maybe there's a better forum for Excel charting questions?
Could someone please suggest a better forum for Excel charting issues?

I finally got through to Microsoft support, I showed them how it should work and what happens to the chart over time after it quits working properly.  The conversation went like this:

 

Microsoft: We are escalating the issue and we are waiting for the level 3 engineers to reach out to us, kindly bear with us to resolve this with you. Do have a great day.

 

Microsoft: …after my collaboration with my team, I was informed to tell you that this is a limitation with excel on MAC, and there is nothing we can do from our own end.

 

me: Can you explain the limitation?

 

Microsoft:  Limitation means that there is nothing we can do from our end here.

 

Me: I didn’t ask for the definition of the word “limitation”. I do know english.  What is the imitation that I am breaking to cause this bug?  If you tell me what's happening, I can find a work-around.

 

Microsoft: I am sorry for the inconvenience this must have caused you, but there is nothing we can do about it from our own end.

 

So, back to the question: is there any place to bet Excel help with a bug?

@Blackbiuried 

If Microsoft can't or won't help you, the only workaround is probably to switch to Excel for Windows...

best response confirmed by Blackbiuried (Brass Contributor)
Solution
... Or switch to Numbers on the Mac and be done with Microsoft altogether.

@Blackbiuried 

If I understand your question correctly, the result you're seeing is due to the order in which colors are applied by the current chart theme. You will find a thorough discussion on chart theme colors and how they work here:
https://www.brandwares.com/bestpractices/

No, it has to do with an error that appears as a chart ages (adding and deleting points) then trying to re-color (select a color scheme) from "scratch".... it acts like it is still filling in colors for those deleted elements leaving the borking the colors displayed.

Note also from your link: "save as template" is no longer an option in Excel for Mac OS X.

@Blackbiuried 

Thanks for replying.

I'm still having trouble visualizing the steps your taking. Any chance you could upload an example workbook (without personal or confidential information contained within) to a share site such as OneDrive or DropBox with a step-by-step example of what happens and what you would like to have happen instead? If you can do that, please reply with a link to the example workbook.

 

You can right-click (control-click) on the border of a chart and choose Save As Template from the menu to save a chart template file (.crtx).

 

Screenshot 2022-07-13 at 12.54.38 PM.png

 

 

 

1 best response

Accepted Solutions
best response confirmed by Blackbiuried (Brass Contributor)
Solution
... Or switch to Numbers on the Mac and be done with Microsoft altogether.

View solution in original post