How to: 3 side by side columns with overlayed (?) data

[Guest]

Hi there,

Hope someone can help me create some column charts.

I have 2 economic sectors, S1 and S2. Each sector consumes 3 types of
energy - E1, E2 and E3 - both directly and indirectly. For each energy
type, the indirect amount is larger than the direct amount.

How can I graph this information so that:

1. There is one column for each of the "E's" that shows both direct and
indirect amounts. The 3 E columns are side by side for each of the sectors.
So from left to right the columns read: S1E1, S1E2, S1E3 S2E1, S2E2,
S2E3

2. Each of the E columns show both direct and indirect amounts (but not
stacked. ie if direct is 5 and indirect 8, then the indirect 'colour'
appears from 5 to 8 only, rather than 5 to 13)


Note, the actual numbers in my spreadsheet are 32 sectors and 5 energy types
(each with two series of data - direct and indirect)...that are linked to
equations in other worksheets. Therefore any solution that would minimise
needing to manipulate the data would be greatly appreciated!

Many thanks in advance.

Regards,
Debborah

 

[Jon Peltier]

You will need to rearrange your data. But it will take much less time than
agonizing over how to get nonconforming data to conform to your chart.

If direct is greater than indirect, according to your scheme, then direct
will completely obscure indirect. Not a good charting approach.

One way to arrange the data is as follows. Don't hard code it, put links in
the table to your data wherever it resides.

Indirect Direct
S1E1 8 5
S1E2 6 3
S1E3 8 4

S2E1 5 2
S2E2 5 1
S2E3 6 3

The blank row will separate the columns from S1Ex and S2Ex. Make a clustered
column chart, then double click on one of the series, and on the Options
tab, change Overlap to 100. You may also want to change the gap width.

- Jon

 

[Guest]

Awesome! Many thanks, Jon. I appreciate it.

Regards,
Debborah