Auto color scheme is in an Excel chart

[jfk683]

I am looking to find what the auto color scheme is in an Excel chart.
Basically, when you select a range of data for a chart it
automatically selects a color for each line in a line graph. I, for
the sake of formatting and conditional formatting, need to know what
that order is for the first 40 colors.

I am currently using Excel 2003.


Thank you.

 

[Jon Peltier]

You could make a chart with 40 series, and see what you've got.

I guess what you want, though, is the color index values for the colors. If
you go to Tools menu > Options > Color, you see the palette, with a couple
rows at the bottom for charts. The 'Chart Fills' colors are color index
values 17 through 24, and the 'Chart Lines' colors are color index values 25
through 32. So if you make a line chart, the first eight series use color
index values 25 to 32. Then the index values increase to 33, 34, etc. When
they get to 56, the number of colors in the palette, they start counting
again at 1, 2, 3. It's not particularly easy to figure out which color index
values correspond to which tiles in the palette, because they are mostly not
in order.

You could select A1:A56, press Alt-F11 to open the VB Editor, press Ctrl+G
to open the Immediate window, copy the following line, paste it into this
window, put the cursor in the line, and press Enter. This will color A1 with
color index 1, A2 with color index 2, etc.

I blogged about colors in Excel charts:
http://peltiertech.com/WordPress/2008/02/20/using-colors-in-excel-charts/

- Jon

 

[jfk683]

To clarify:

I am creating a chart with up to 40 entries, when there are 40
entries. I say "up to", because depending on the number of stock
tickers I enter, the graph will only show that many lines. As a way
of getting around the legend showing a colored line with the name "#N/
A" or even just blank, I deleted the legend, and have created my own
dynamic legend that will only show the names of the tickers entered.
I have written a formula and conditional formatting that will fill an
entire cell with the color of the line in the chart. That is why I
need to know what the color scheme is.

Your answer is helpful and I have seen that before, but my problem is
that when I have 40 entries, there are more than 8 colors as you
mentioned, but not 40 (it begins to repeat).

Any suggestions... I am at my wits end. Or suggestions on how to
accomplish a chart that will show only the entries I have listed in
both the chart and the legend?

John

 

[jfk683]

I am sorry I entered the last response for a different post that
addressed this same topic.

 

[Jon Peltier]

It does not repeat colorindex values, but it may repeat colors, because some
in the charting rows are the same as some in the rest of the palette. You
could change any duplicate colors to custom colors.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


To clarify:

I am creating a chart with up to 40 entries, when there are 40
entries. I say "up to", because depending on the number of stock
tickers I enter, the graph will only show that many lines. As a way
of getting around the legend showing a colored line with the name "#N/
A" or even just blank, I deleted the legend, and have created my own
dynamic legend that will only show the names of the tickers entered.
I have written a formula and conditional formatting that will fill an
entire cell with the color of the line in the chart. That is why I
need to know what the color scheme is.

Your answer is helpful and I have seen that before, but my problem is
that when I have 40 entries, there are more than 8 colors as you
mentioned, but not 40 (it begins to repeat).

Any suggestions... I am at my wits end. Or suggestions on how to
accomplish a chart that will show only the entries I have listed in
both the chart and the legend?

John

 

[terryc]

Jon, Enjoyed your blog. What can I do to change the color scheme in 2007?

 

[Jon Peltier]

For all the buzz about Excel 2007's millions of colors, I think it's harder
to deal with colors in 2007. You can select from among a number of built in
themes, or design your own. But instead of having control over 56 unique
colors which are linked to a color index, Excel 2007 only lets you change I
think 8 theme colors, and these are lightened and darkened to give the other
colors in the standard scheme. Then you can add all the custom colors you
want, but if you decide you wanted green instead of blue, you can't change
the custom color, you add the other one, and change each object's color
independently.

- Jon

 

[terryc]

How do I change the theme or design my own? I have a line chart that shows
the scores of my six team members in 4 different areas. So I only need 7
colors. But the colors are so similar it is hard to remember who is what
color.

 

[Jon Peltier]

On one of the tabs, I think it's called Page Layout, there are a bunch of
controls to the left of the tab which have Theme-related captions. If you
can't figure out what to do, you could try Googling on Excel 2007 Theme
Colors.

- Jon

 

[terryc]

Thanks Jon.

On one of the tabs, I think it's called Page Layout, there are a bunch of
controls to the left of the tab which have Theme-related captions. If you
can't figure out what to do, you could try Googling on Excel 2007 Theme
Colors.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______