Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Plotting 4 dimensions in a chart
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! |
#2
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) | |||
Prevent non-numerical data (e.g. text) from plotting on a chart | Charts and Charting in Excel | |||
Plotting moving average line on a chart | Charts and Charting in Excel |