Multiple Stacked Bar Charts, Data Organitation

  • Thread starter Thread starter Bull Splat
  • Start date Start date
B

Bull Splat

I'm building a chart that uses multiple stacked bar charts to show an
employees work time broken down into days of the week.
I've read online, and downloaded example files (funchart4)
(stackedbar.zip) and a few others. I been through this and other news
groups.
I see that the data has to be organized in a staggered format, but I'm
not seeing the connection between the way the datas arrainged, and the
way the chart determines which data goes to which bar. Currently, I
have the chart displaying two stacked bar charts for each day, but I
need to add a third stacked bar chart for each day. I've tried to
just include a "third" row to the data format, but that just adds the
new info to one of the existing bars.
I've gone back in and tried to change the source data cells to get
this to bring up the "third" bar, but so far no luck.

One additional twist, the third bar chart needs to display another
time element, and then (finally) the fourth stacked bar chart needs to
display items sold that day. What I'm trying to do is compare the
time an employees claimed to be working, next to the time he actually
worked, and the number of items sold on each day (to demonstrate a
correlation beteen lack of work, and lack of sales).

Thanks already to Jon Peltier and Tushar Mehta for your excellent
suggestions.

Thanks.
 
Hi Bull -

This takes a while to work out in your mind, but it does make sense.
Let's say I have three clusters of bars, A, B, and C. Suppose each
cluster has 4 stacks of bars, which I'll refer to as A1, A2, A3, A4, B1,
B2, ..., C4. For the sake of neatness, let's put a bar-width blank in
before and after clusters, so the X axis has slots for these categories
(even if all the labels won't show):

0 A1 A2 A3 A4 0 B1 B2 B3 B4 0 C1 C2 C3 C4 0

Now suppose further that each position A1, A2, etc., has four columns
stacked on it, the columns on A1, B1, C1 are the same series, A2, B2, C2
are the same series, etc. So pictorially the stacks look like this:

d h l p d h l p d h l p
c g k o c g k o c g k o
b f j n b f j n b f j n
a e i m a e i m a e i m
0 A1 A2 A3 A4 0 B1 B2 B3 B4 0 C1 C2 C3 C4 0

where lower case letters represent the different series in the chart.
Of course the columns will be of varying height, but this illustrates
the pattern. Each series needs its own column in the worksheet, and the
categories (0, A1, A2, etc.) need a column, and any series stacked
together over a category label need a value in the same row as the label
is in. Elsewhere there are blanks. This pattern of values and blanks
produces the staggered arrangement:

a b c d e f g h i j k l m n o p
0
A1 X X X X
A2 X X X X
A3 X X X X
A4 X X X X
0
B1 X X X X
B2 X X X X
B3 X X X X
B4 X X X X
0
C1 X X X X
C2 X X X X
C3 X X X X
C4 X X X X
0

I've indicated the cells that require values with the letter X; other
cells can be left blank or may contain zeros. To ensure easy chart
creation, the cell in the top left of this table must be blank. To be
sure, select it and press the Delete key. Leave the zeros in the first
column until after the chart has been drawn.

Now select the range from the top left blank cell to the bottom right
blank cell under the last X in the column of p's values. Start the
chart wizard, create a stacked column chart, and make sure you select
the Series in Columns option in step two, Chart Source Data, on the Data
Range tab. Once the chart is made, double click on any one of these
series, and on the Options tab, set the Gap Width to 0, to make the
columns within a cluster touch themselves.

The reason the blank cell is so important is that it tells Excel to use
the top row and left column of the range for series names and category
labels. It's much easier to learn to leave the cell blank than to
define the names and categories of each series manually each time you
build a chart. You need the zeros in the first column, at least the
first one, to prevent Excel from using the first two rows for series
names. A multi-cell region in the top left of the data range tells
Excel to use rows and columns corresponding to the size of the blank
region for names and categories. You can create some nice category
label structures using this technique, if you keep your wits about you.
But that's a lesson for another day.

- Jon
 
Back
Top