ExcelBanter - Plotting 4 dimensions in a chart

1. That "inefficient" manner of arranging the data is actually very
flexible, enabling you to filter and pivot the data, and it's pretty
close to a way to display your data.

2. Pivot charts are an inadequate method to display data, not living up
to their initial promise. Pivot tables are pretty cool, though.

3. Contrary to popular belief, 3D charts do not necessarily make data
easier to visualize. Being limited to the 2 dimensions on a printed page
or a monitor screen makes them less useful and introduces sources of
inaccuracy and misinterpretation.

Set up your data in the inefficient manner. Create a pivot table, with A
and F in the row area, E in the column area, and X in the data area. It
will look something line this:

E1 E2
A F1 X X
F2 X X
F3 X X
F4 X X
B F1 X X
F2 X X
F3 X X
F4 X X
C F1 X X
F2 X X
F3 X X
F4 X X
D F1 X X
F2 X X
F3 X X
F4 X X

From this arrangement, you can make a stacked column chart with E2 on
E1, and the category axis having two levels of factors. It's not a
clustered-stacked arrangement. For that you'd have to do some tricks,
shown in the links on this page:

http://peltiertech.com/Excel/ChartsH...sterStack.html

I would recommend not making a pivot chart, but making a regular chart.
The easiest way is to copy the pivot table, and use paste-special,
values option, to put the data elsewhere in the sheet.

The nice thing about setting up the pivot table is that you can easily
pivot it around, to group differently:

E1 E2
F1 A X X
B X X
C X X
D X X
F2 A X X
B X X
C X X
D X X
[etc.]

or even

A B C D
E1 E2 E1 E2 E1 E2 E1 E2
F1 X X X X X X X X
F2 X X X X X X X X
F3 X X X X X X X X
F4 X X X X X X X X

Look familiar?

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

Garam Chitti wrote:

I have data that is arranged as follows:

A B C D
E1 E2 E1 E2 E1 E2 E1 E2
F1 X X X X X X X X
F2 X X X X X X X X
F3 X X X X X X X X

I need to create a 3Dview chart with the above data such that I can see the
relationships for E1 and E2 for each A/B/C/D and also by F1/F2/F3. In
addition, I want to get a total of all E1's and E2 (possibly as a stacked
column). I tried to use the 3D column chart, but could only create on2 chart
for each F1/F2/F3 using just the A/B/C/D and E1/E2 as the X & Y axes, with
the Z axes showing the values (X's).

I even tried a pivotchart, but the data needs to be entered in a very
inefficient manner:

F1 A E1 X
F1 A E2 X
F1 B E1 X
F1 B E2 X
F1 C E1 X
F1 C E2 X
F1 D E1 X
F1 D E2 X
F2 A E1 X
F2 A E2 X
F2 B E1 X
and so on.

I'd like to keep the data as shown at the beginning and still create the 4D
chart.

Thanks!