Hey all you chart lovers,
I have searched around for a solution but haven't found anything applicable. Here is my situation.
I am using Excel to make a timeline of country leaders for several countries. I therefore have a big table with 50+ countries in columns and 500+ leaders in lines. Each leader has his duration in power in the column of his country and the rest is left blank.
That's all nice and well but that means that Excel considers all 500+ leaders as separate sets and therefore strives to find me 500+ different colours. Eventually, it gets to extremely pale shades and you can't see much.
I would be happy resetting the colours for every country, but Excel takes them all together, so that seems impossible. Or limiting the overall choice of colours.
Another interesting option, however, would be to have each set (short or long, depending on the leader's time in power) automatically assigned a colour based on the duration (so the cell value). Ideally, the maximum duration would be bright red, the minimum duration bright green, and the others plotted somewhere in between.
Conditional formatting would do wonders for this inside the cells but isn't applicable for graphs. I have seen solutions that separate one column into several and assign a colour to each new column, but that won't work for me because 1) I already plenty columns for the countries, and 2) I want an actual gradient range of colours, not just a few.
So, I'm open to all thoughts and ideas (even to VBA, though that is not my forte at all). I am including screen caps of my table and graph so that you guys can have a idea of what I mean.
Thanks in advance!
Screen cap of the large table with leaders' time in power for each country
Screen cap of the current graph, with individual colours for each and every single leader
