How to do analysis for a set of data in Excel with graphs?

Posted on 2011-04-22
Last Modified: 2012-05-11
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?


Question by:Tolgar
    LVL 22

    Accepted Solution

    Something like this?
    In G2:L5 I used the search function to check if column C contains a certain text. Then on B9:G11 I used the SumIf you create a total for each tool for each area. Then the graph is easy.
    The same technique is used for the Platform frequency. The range D2:F5 contained text and not numbers in the example. I converted those to numbers.

    Author Comment

    Thank you for your reply. It is exactly what I wanted.

    However, when I type the following command in a cell it does not evaluate the value. I just see the function itself.


    Why do you think so?


    Author Comment

    ok It worked. I don't know what I have changed actually.

    LVL 22

    Expert Comment

    by:Nico Bontenbal
    <<Thank you for your reply. It is exactly what I wanted.>>
    In that case, would you please mark my answer as the solution.

    Author Closing Comment

    Sorry, I was so busy during the day. Just have chance to close this question.

    Thanks again,

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now