Trouble with Excel Stacking Bar Graphs

[Joules]

Hi,

I have some data from a research project that I am trying to graph in
Excel. I would like to place the scores ffir each subject in a
"stacked column". Additionally I would like to indicate each test
subject's contribution to the column by using a different color for
each subject. That is, when I list the scores for subjects A, B, and
C stacked one above each other it would appear as A is the color red
with a score of 4, B is green with a score of 5, and C is teal with a
score of 7. So, when someone looks at the graph they can see the
column height is 16 (4+5+7) and they can see that all the bars in the
color red are scores obtained by subject A, B is green, and C is
teal. Another column will be built from the scores of subjects D, E,
and F; the next column will be built from the test scores of subjects
G, H, and I, etc.

I listed the scores in a table in Excel and it built a bar graph that
looked pretty good, but the colors seemed to be assigned almost at
random; for example some times the score for subject A would be red,
and then some other color.

I thought I was making some progress by right clicking on the graph,
going to "Source Data" and entering the data location for the series
manually, but I could not figure out how to tell excel which column
was supposed to receive the data. If I hover the curser over a
collumn a label appears which says something like "Subject A" Point
"Column 2", and the data is in column 2 of the graph, but I want it to
be in column 3, or 4, or 5. How can I control which bar in the bar
graph gets the data from the series.

Thanks in advance,
Joules

Joules

 

[Earl Kiosterud]

Joules,

I've not seen the colors assigned at random. The first series always seems to be yellow,
the second maroon, the third blue, etc. You can change the colors. Click on a column of a
series (only once) -- a handle should appear in each column of the series. Use the color
fill button on the drawing toolbar, or Format - Selected Data series. Now to use this color
scheme in other charts, right-click the chart - Chart Type, Custom Types tab -
User-Defined - Add.

 

[Joules]

Joules,

I've not seen the colors assigned at random. The first series always seems to be yellow,
the second maroon, the third blue, etc. You can change the colors. Click on a column of a
series (only once) -- a handle should appear in each column of the series. Use the color
fill button on the drawing toolbar, or Format - Selected Data series. Now to use this color
scheme in other charts, right-click the chart - Chart Type, Custom Types tab -
User-Defined - Add.

Hi Earl,

Thank you for your reply. I am sorry I did not explain myself very
well. I have entered the data in the spread sheet and created a
graph. I am trying to color code the columns so that a reader can tell
which data column contains which test subject's score. That is, there
are scores for three different test subjects within each column. I
have a total of nine tests subjects, six columns, and three different
scores within each column.

It seemed like the easiest way to get what I wanted was to add new
series in the "Source Data" dialog box; but excel added the data to
the wrong vertical bars in the bar graph. If you are adding a series
in the Source Data dialog box is there any way to control which bar
the data is added to?

Sincerely,

 

[Jon Peltier]

When you add data to a column chart, the added data must have data for each
category. If the series has no column over a given category, it must have a
zero or blank cell in that position. You need to stagger your data as shown
here:

A B C D E F G H I
One 1 2 3
Two 4 5 6
Three 7 8 9


- Jon