Excel - Automatic Shading in Graphs 1

[AGlazer]

Hi, everyone.

Quick question for you. I'm creating a bar graph in excel with approximately 200 items on it. These items are actually in two groups, and I need to color half of them in white and half of them in black. However, I don't want to run the chart with two series, because I want to be able to sort them all together.

(i.e., with two series, I end up with 100 "items" split with two bars. with one series, I end up with 200 "items" with one bar each).

Any suggestions, other than coloring 100 bars by hand?

Thanks,

Aaron

 

[SkipVought]

Hi,

I do not understand -- they are 2 groups but NOT 2 bars. Well, how do you differentiate between them? They are EITHER 1 group or 2.

CONFUSED!!!???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[AGlazer]

Skip,

Sorry for the confusion.

Data looks like this:

Item W/E Performance
---------- --- -----
TV E 4.3%
Radio E 2.8%
Magazine W 3.8%
Book W 8.7%

Basically, I'm graphing the "Performance" value, a percentage change. Based on the "flag" W/E (in this example, written or electronic media), I want to color the bars differently -- they both effect performance, so I want to sort them based on greatest -> least impact on performance, but I want to have all E's colored black and all W's colored blue.

That help?

Thanks for the help!

-A

 

[SkipVought]

Hi,

1. Make 2 more columns -- E & W
2. enter this formula in the E column in row 2 (assuming that everything starts in A1...

=IF($B2=D$1,$C2,"")

3. Copy this formula over and down.
4. in your chart data source, specify 2 series heading in row 1 and data in rows 2 and following, x categories in col 1

VOLA!
2-color bars, one color for E and the other for W!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

Interesting question, I know you can do it for each individual object in the x-axis, but that just gives each bar an individual color. I think the only way you can do it is by hand. Seems like there would be an easier way.

 

[SkipVought]

Another technique to create the correct data summary is to use a Pivot Table with item names in the row area and W/E values in the column area and Percentage in the Data area.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[AGlazer]

Skip,

You are the man. I can't thank you enough -- you just saved me many hours of tedious color shading!

THANKS!!!!

-A