I have a set of data and I wan to do some analysis on this data. You can find my example excel sheet (book1.xlsx) in the attachment.
The graphs that I want to draw are:
1- Which design tools each team uses. X axis will be Design tools. Y axis is how many people responded what tool. It will be a stack bar graph that each bar will represent one design tool. In each bar we will see each area by color. So there will be 6 bars (Visio, Magicdraw, Word, Gimp, Powerpoint, Umbrello) and in each bar there will be 3 colors for each area (Area 1, Area 2, Area 3)
Note: for example person2-3-4 selected multiple design tools. In this case I want to see each tool separately, because word and gimp are repeated tools.
2- In the last 3 columns, 1 means rarely, 5 means frequently. Again I want to see how frequently they are used on a weighted graph by area on a stack bar graph. Visually we can say that Area1 uses Ruby at a rate of 1, Area2 at a rate of 3, Area3 at a rate of 5+2 and so on for PHP and .NET
Note: Each bar will represent Ruby, PHP and .NET . So there will be 3 bars. In each bar, we will see each area by color. so there will be 3 colors on each bar.
This data is just an example. I have a huge set of data. How can I make this using Excel 2007. I tried using pivot table but I couldn't manage to do it.
Can you please send me a working example for these two cases?